May 27 2022 06:49 AM
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
May 27 2022 07:46 AM
Solution=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))
May 27 2022 07:48 AM
@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).
May 27 2022 08:28 AM
May 27 2022 08:29 AM
May 27 2022 07:46 AM
Solution=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))