MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.