[Solved] Filling Other Cells With A Formula

Discuss the spreadsheet application

[Solved] Filling Other Cells With A Formula

Postby freedhardwoods » Mon Nov 25, 2019 12:34 pm

I will begin by saying that although I have used Calc for several years, I am still a novice when trying to do more than just basic things.I tried searching here for a solution, but didn't find it. I am trying use the fill option so that the formula will read; =IF(C2=D1;A2;0) =IF(C2=E1;A2;0) =IF(C2=F1;A2;0) =IF(C2=G1;A2;0) etc.

Everything I have tried always comes out with; =IF(C2=D1;A2;0) =IF(D2=E1;B2;0) =IF(E2=F1;C2;0) =IF(F2=G1;D2;0) etc

Is there a solution to make this work?
Last edited by Hagar Delest on Tue Nov 26, 2019 5:44 pm, edited 2 times in total.
Reason: tagged solved
OpenOffice 4.0.1 on Windows 7
freedhardwoods
 
Posts: 8
Joined: Sat Feb 20, 2016 12:45 pm

Re: Filling Other Cells With A Formula

Postby DiGro » Mon Nov 25, 2019 2:09 pm

To prevent values from changing whilst filling you have to "lock" them.

You do that by preceding the column and/or row with a dollarsign ($)

So if:
Code: Select all   Expand viewCollapse view
=IF(C2=D1;A2;0)

is changed to:
Code: Select all   Expand viewCollapse view
=IF($C$2=D1;$A$2;0)
it will retain values C2 and A2, but will change D1 to the value for the next column

Filling from
Code: Select all   Expand viewCollapse view
=IF($C$2=D1;$A$2;0)
1 column to the right will give you then
Code: Select all   Expand viewCollapse view
=IF($C$2=E1;$A$2;0)
in that next column
____________
DiGro

AOO 4.1.7 on Windows 10. Scanned with ZIGGO Safe Online (F-Secure)
User avatar
DiGro
 
Posts: 100
Joined: Mon Oct 08, 2007 1:31 am
Location: Hoorn NH, The Netherlands

Re: Filling Other Cells With A Formula

Postby freedhardwoods » Mon Nov 25, 2019 3:08 pm

That's what I needed. Thanks a lot. :super:
OpenOffice 4.0.1 on Windows 7
freedhardwoods
 
Posts: 8
Joined: Sat Feb 20, 2016 12:45 pm


Return to Calc

Who is online

Users browsing this forum: Majestic-12 [Bot] and 7 guests