Formulas using fields that change

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.

(12 responses) HI! I am using a vlookup/match to populate fields from column headers. For the most part it's doing exactly what I need except instead of pu

Let's Connect

MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on this
web site are provided "as is" and we do not guarantee that they can be used in all
situations.
This site contains affiliate links. Any affiliate commissions that we
earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com
running. You can earn a commission for sales leads that you send to us by joining our
affiliate program.
View our Privacy Policy, Cookies Policy, and Terms of Use.