 SOLVED

# Change cell reference formula row based on input

Hello guys,

I'm working on a spreadsheet and try to figure out how to do the following:

TABLE1 is where I introduce the price in column A, and in the next 5 columns I have different IF functions that get values from TABLE2. I use TABLE2 to introduce different values on different rows (these values change over time). Every time when I change a value (let's say variable 2) I insert a new row in TABLE2 and input all values. --- I do this in order to preserve the old values calculated by formulas from row (1)---

Here is how this should work:

In TABLE1 column G, I need to select the row number from TABLE2 (with data validation). When I select row number (2) for example in cell G6, all the formulas from TABLE1 that are bellow G6 row (including G6 row) will change the cell references from row (1) -orange color- to row (2) -green color-.

So, first 3 prices in column A (500, 780 and 100) will get values from row (1) -orange color-

And all the prices that I will input bellow will get values from row (2) -green color- Until I will change to a new row number (3) -blue color-

***all colors that I've used are for explainatory purposes only. I do not need to color rows

I hope that I've explained it clearly. Can you please help me with this? I attach excel file bellow.

Best regards,

Alex

4 Replies
best response confirmed by Alecs (Contributor)
Solution

# Re: Change cell reference formula row based on input

``=IF(A3>50,A3/\$J\$3,IF(A3>100,A3/\$L\$3,0))``

What is the intended result in column B if the value in column A is greater 100? The above formula returns the result of A3/\$J\$3 for all values in column A which are greater 50. I suppose you want to return the result of A3/\$L\$3 if A3 is e.g. 250 and changed the formula:

``=IF(A3>100,A3/\$L\$3,IF(A3>50,A3/\$J\$3,0))``

In order to select the row from Table2 i used INDEX and MATCH with your formulas for example the formula in column B is:

``=IF(A3>100,A3/INDEX(\$L\$3:\$L\$31,MATCH(G3,\$I\$3:\$I\$31,0)),IF(A3>50,A3/INDEX(\$J\$3:\$J\$31,MATCH(G3,\$I\$3:\$I\$31,0)),0))``

# Re: Change cell reference formula row based on input

@Alecs This is perfect use for a LOOKUP function.  There are actually many ways to accomplish this.  In the attached I use 2 versions both using VLOOKUP().  I would recommend XLOOKUP() if you have Excel 365 as it has improved performance but in case you don't have 365 I used Vlookup.  In sheet 1 i use cell references and chose a range to row 99 for table 2.  In sheet 2 I converted both tables into actual Tables and used the table structured reference, which will grow or shrink with the size of the table and probably a better way to do it (IMHO).

# Re: Change cell reference formula row based on input

Thanks a lot! this is exactly what I needed. I will give you the best response since your message was first hope that mtarler is ok with this. Both solutions are valid here

# Re: Change cell reference formula row based on input

Thank you Matt. This is exactly what I needed here. I much appreciate your time and efforts
wish you all the best!