SOLVED

Change cell reference formula row based on input

%3CLINGO-SUB%20id%3D%22lingo-sub-3438343%22%20slang%3D%22en-US%22%3EChange%20cell%20reference%20formula%20row%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3438343%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20guys%2C%3C%2FP%3E%3CP%3EI'm%20working%20on%20a%20spreadsheet%20and%20try%20to%20figure%20out%20how%20to%20do%20the%20following%3A%3C%2FP%3E%3CP%3ETABLE1%20is%20where%20I%20introduce%20the%20price%20in%20column%20A%2C%20and%20in%20the%20next%205%20columns%20I%20have%20different%20IF%20functions%20that%20get%20values%20from%20TABLE2.%20I%20use%20TABLE2%20to%20introduce%20different%20values%20on%20different%20rows%20(these%20values%20change%20over%20time).%20Every%20time%20when%20I%20change%20a%20value%20(let's%20say%20variable%202)%20I%20insert%20a%20new%20row%20in%20TABLE2%20and%20input%20all%20values.%20---%20I%20do%20this%20in%20order%20to%20preserve%20the%20old%20values%20calculated%20by%20formulas%20from%20row%20(1)---%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20how%20this%20should%20work%3A%3C%2FP%3E%3CP%3EIn%20TABLE1%20column%20G%2C%20I%20need%20to%20select%20the%20row%20number%20from%20TABLE2%20(with%20data%20validation).%20When%20I%20select%20row%20number%20(2)%20for%20example%20in%20cell%20G6%2C%20all%20the%20formulas%20from%20TABLE1%20that%20are%20bellow%20G6%20row%20(including%20G6%20row)%20will%20change%20the%20cell%20references%20from%20row%20(1)%20-orange%20color-%20to%20row%20(2)%20-green%20color-.%3C%2FP%3E%3CP%3ESo%2C%20first%203%20prices%20in%20column%20A%20(500%2C%20780%20and%20100)%20will%20get%20values%20from%20row%20(1)%20-orange%20color-%3C%2FP%3E%3CP%3EAnd%20all%20the%20prices%20that%20I%20will%20input%20bellow%20will%20get%20values%20from%20row%20(2)%20-green%20color-%20Until%20I%20will%20change%20to%20a%20new%20row%20number%20(3)%20-blue%20color-%3C%2FP%3E%3CP%3E***all%20colors%20that%20I've%20used%20are%20for%20explainatory%20purposes%20only.%20I%20do%20not%20need%20to%20color%20rows%3C%2FP%3E%3CP%3EI%20hope%20that%20I've%20explained%20it%20clearly.%20Can%20you%20please%20help%20me%20with%20this%3F%20I%20attach%20excel%20file%20bellow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3EAlex%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3438343%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3438670%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20cell%20reference%20formula%20row%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3438670%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1351289%22%20target%3D%22_blank%22%3E%40Alecs2405%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(A3%26gt%3B50%2CA3%2F%24J%243%2CIF(A3%26gt%3B100%2CA3%2F%24L%243%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EWhat%20is%20the%20intended%20result%20in%20column%20B%20if%20the%20value%20in%20column%20A%20is%20greater%20100%3F%20The%20above%20formula%20returns%20the%20result%20of%26nbsp%3BA3%2F%24J%243%20for%20all%20values%20in%20column%20A%20which%20are%20greater%2050.%20I%20suppose%20you%20want%20to%20return%20the%20result%20of%20A3%2F%24L%243%20if%20A3%20is%20e.g.%20250%20and%20changed%20the%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(A3%26gt%3B100%2CA3%2F%24L%243%2CIF(A3%26gt%3B50%2CA3%2F%24J%243%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20order%20to%20select%20the%20row%20from%20Table2%20i%20used%20INDEX%20and%20MATCH%20with%20your%20formulas%20for%20example%20the%20formula%20in%20column%20B%20is%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(A3%26gt%3B100%2CA3%2FINDEX(%24L%243%3A%24L%2431%2CMATCH(G3%2C%24I%243%3A%24I%2431%2C0))%2CIF(A3%26gt%3B50%2CA3%2FINDEX(%24J%243%3A%24J%2431%2CMATCH(G3%2C%24I%243%3A%24I%2431%2C0))%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3438701%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20cell%20reference%20formula%20row%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3438701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1351289%22%20target%3D%22_blank%22%3E%40Alecs2405%3C%2FA%3E%26nbsp%3BThis%20is%20perfect%20use%20for%20a%20LOOKUP%20function.%26nbsp%3B%20There%20are%20actually%20many%20ways%20to%20accomplish%20this.%26nbsp%3B%20In%20the%20attached%20I%20use%202%20versions%20both%20using%20VLOOKUP().%26nbsp%3B%20I%20would%20recommend%20XLOOKUP()%20if%20you%20have%20Excel%20365%20as%20it%20has%20improved%20performance%20but%20in%20case%20you%20don't%20have%20365%20I%20used%20Vlookup.%26nbsp%3B%20In%20sheet%201%20i%20use%20cell%20references%20and%20chose%20a%20range%20to%20row%2099%20for%20table%202.%26nbsp%3B%20In%20sheet%202%20I%20converted%20both%20tables%20into%20actual%20Tables%20and%20used%20the%20table%20structured%20reference%2C%20which%20will%20grow%20or%20shrink%20with%20the%20size%20of%20the%20table%20and%20probably%20a%20better%20way%20to%20do%20it%20(IMHO).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3438941%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20cell%20reference%20formula%20row%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3438941%22%20slang%3D%22en-US%22%3EThanks%20a%20lot!%20this%20is%20exactly%20what%20I%20needed.%20I%20will%20give%20you%20the%20best%20response%20since%20your%20message%20was%20first%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%20hope%20that%20mtarler%20is%20ok%20with%20this.%20Both%20solutions%20are%20valid%20here%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3438944%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20cell%20reference%20formula%20row%20based%20on%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3438944%22%20slang%3D%22en-US%22%3EThank%20you%20Matt.%20This%20is%20exactly%20what%20I%20needed%20here.%20I%20much%20appreciate%20your%20time%20and%20efforts%3CBR%20%2F%3Ewish%20you%20all%20the%20best!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
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 (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 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!