How to update Indirect formula so that cell reference is based on a hardcoded cell value?

[k3yn0t3]

Hi all. Quick question:

Does anyone know how to update the formula in I22 to make it be fed by the cell reference i hardcoded into A19? I tried replacing the H60 in the Indirect formula with "text(A19))" but that doesn't work.

The reason I would like to do this is so that I can copy and paste this formula into cells I29:L29 and still have it go to same cell in Factory_2's sheet.

=+INDIRECT($C$20&"!"&CELL("ADDRESS",H60))

Thank you in advance.

Best regards,
Sam

Factories_v20.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
19	H60		Average Growth Rate
20		1	Factory_1
21					Live: Case 1				--%	--%	--%	--%
22					Case 1				--%	--%	--%	--%
23					Case 2				--%	--%	--%	--%
24					Case 3				--%	--%	--%	--%
25					Case 4				--%	--%	--%	--%
26
27	H60	2	Factory_2
28					Live: Case 1				--%	--%	--%	--%
29					Case 1				--%	--%	--%	--%
30					Case 2				--%	--%	--%	--%
31					Case 3				--%	--%	--%	--%
32					Case 4				--%	--%	--%	--%
Assume_xl2bb

Cell Formulas
Range	Formula
E21,E28	E21	=+TEXT("Live: "&OFFSET(E21,$E$5,),)
I21:L21,I28:L28	I21	=+OFFSET(I21,$E$5,)
I22:L22,I29:L29	I22	=+INDIRECT($C$20&"!"&CELL("ADDRESS",H60))
I23:L25,I30:L32	I23	=+I22
A27	A27	=+A19
B27	B27	=+B20+$E$15
C27	C27	=+$C$15&$B27

 

[Trebor76]

Hi k3yn0t3,

Isn't it just this:

=+INDIRECT(C20&"!"&A19)

Note - when I copied your example into Excel all the OFFSET formulas were causing circular references which need to be fixed.

Regards,

Robert

 

[k3yn0t3]

Thanks, Robert. Just typing in A19 doesnt work because then it pulls from A19 in the other sheet. The cell that I need to go to when the formula redirects to the other sheet is H60.

The "cell(address())" avoids the #REF in my current formula. I have 200 factories so I need this to be dynamic, or else I'd manually copy and paste

 

[Trebor76]

If H60 is in cell A19 it will reference cell H60

 

[k3yn0t3]

It's working perfectly in my current workbook, apologies if something is lost in translation. Of course, indirect makes auditing a pain

 

[Trebor76]

So all good now? Did you fix the circular references?

 

[k3yn0t3]

Sorry no. I meant that the original formula isn't REFing out for me. I want to copy and paste the I22 formula into I29 and have the H60 reference not change. Right now, it changes to H67. I understand why it changes, but I'm trying to find a workaround so that I can duplicate these driver rows and have the formula still work to avoid copy and pasting this formula with H60 for each factory 200 times

 

[k3yn0t3]

Figured this out! I anchored the reference so that the row doesn't move as I copy it down...so obvious. Thanks for the help, Robert

Before:

=+INDIRECT($C20&"!"&CELL("ADDRESS",H60))

After

=+INDIRECT($C20&"!"&CELL("ADDRESS",H$60))

 

[Trebor76]

Figured this out!

Great Just remember to fix the circular references as these can cause the calculation engine to freeze and then no formulas will calculate.