Formulas using fields that change
Posted by Jeff on October 23, 2001 11:33 AM
I have a spreadsheet that I need to be able to do the following to:
The first column will be the formula.
In each column next to the formula will be different numbers depending on the date the number is entered. what I need the formula to do is always use the next column as new information is entered in the spreadsheet.
For example. Lets say the formula multiplies "x" by 10. I need the formula to recognize "x" as a new field every week:
Formula Day1 Day2 Day3 Day4
10 2 4 1
Since Day3=1 and the formula is x*10, the formula column displays 10. But if I enter a number into Day4, I want the Formula column to change. Like if I enter 5 into Day4, I want the formula to no calculate 5*10 and display 50. How do I get the formula to apply the rightmost information as "x"?
Any ideas?
Posted by Aladin Akyurek on October 23, 2001 11:44 AM
Use
=10*INDIRECT(ADDRESS(ROW(),MATCH(9.99999999999999E+307,2:2)))
10 is the example constant you mentioned. The 2:2 bit in the INDIRECT part refers to the row where this formula resides.
Aladin
=========
Posted by Juan Pablo on October 23, 2001 11:46 AM
You could use something like this...
If your data is in B2:D2, put this formula in A2
=10*(DESREF($B2,0,MATCH(9.9999E+307,2:2)-2))
If you need, you can copy this down.
Juan Pablo
Posted by Juan Pablo on October 23, 2001 11:48 AM
Should be
=10*(OFFSET($B2,0,MATCH(9.9999E+307,2:2)-2))
Juan Pablo
Posted by Jeff on October 23, 2001 11:51 AM
So far all the suggestions have left me with a "#N/A"... what info should I change based on where the formula resides?
Posted by Mark W. on October 23, 2001 11:52 AM
See 2620.html (nt)
Posted by Aladin Akyurek on October 23, 2001 11:57 AM
Jeff,
All 3 formulas are equivalent.
Assuming that you have your formula is in A2 and your formula needs the last value in the row of A2, that is, 2:2, just put one of the proposals in your formula.
Aladin
Posted by Jeff on October 23, 2001 12:00 PM
Great... sort of worked, but what happens if my formula is not in A2, but it is in C7... I am getting a circular reference now.
Posted by Aladin Akyurek on October 23, 2001 12:12 PM
Jeff,
That shouldn't matter if you use:
=10*INDIRECT(ADDRESS(ROW(),MATCH(9.99999999999999E+307,7:7)))
where 7:7 is the row of C7 that houses this formula.
Aladin
Posted by Jeff on October 23, 2001 12:13 PM
It is saying that I have created a circular reference.
Posted by Aladin Akyurek on October 23, 2001 12:23 PM
But Dave, 2 is 200%. Why wouldn't you enter 2% as
.02?
Posted by Mark W. on October 23, 2001 12:24 PM
Enter your Day 1 value. It is saying that I have created a circular reference. : where 7:7 is the row of C7 that houses this formula. : Aladin
Posted by Aladin Akyurek on October 23, 2001 12:29 PM
Or...
just bring up the circ ref dialog via View|Toolbars|Circular Reference and click on "Remove All Arrows". Excel will now accept the formula. If no values next to C7, you'll see a 0 in C7. Enter some values in row 7 next to C7, the formula will start using the latest of them.
Aladin
PS. I posted a similar reply, it seems to be vanished in thin air.
========= Enter your Day 1 value. : It is saying that I have created a circular reference.
