MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.