SOLVED

Change cell reference formula row based on input

Brass Contributor

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 (Brass Contributor)
Solution

@Alecs 

=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))

 

@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).

Thanks a lot! this is exactly what I needed. I will give you the best response since your message was first :D hope that mtarler is ok with this. Both solutions are valid here
Thank you Matt. This is exactly what I needed here. I much appreciate your time and efforts
wish you all the best! :)
1 best response

Accepted Solutions
best response confirmed by Alecs (Brass Contributor)
Solution

@Alecs 

=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))

 

View solution in original post