Formulas using fields that change
Posted by Jeff on October 23, 2001 11:34 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 Mark W. on October 23, 2001 11:44 AM
Use =10*INDEX(2:2,MATCH(9.99999999999999E+307,2:2))
as your formula in cell A2.
Posted by Jeff on October 23, 2001 11:47 AM
I get #N/A... can you breakdown what the info in that formula is, perhaps it's because I have different column than A2?
Posted by Mark W. on October 23, 2001 11:51 AM
Enter the formula shown below in cell A2. Enter
2 into cell B2; 4 into cell C2; and 1 into cell
D2... and it should work.
