SOLVED

Reference another cell to complete a formula

Copper Contributor

Microsoft Office 2016

 

I would like a formula to be updated automatically when a new recipe is entered into another cell. For example..

 

='[STATISTICS.xlsm]WK-5-18EF'!$B$4

 

the WK-5-18ef would be a reference from another cell. Then if I was to change it to k-8-6 the formula would automatically become  ='[STATISTICS.xlsm]k-8-6'!$B$4

 

I have multiple sheets to make and this would be a massive time saver, plus I can forward it out onto other computers. I have tried INDIRECT and CELL functions but I haven't had any luck.

 

I have uploaded a sample file. The cell A5 would be what changes, and K6,k8,o6,o8,T1,t2,t3,w1 would all change accordingly. The formulas grab info from multiple sources. I appreciate any help you can give, thank you

5 Replies

@David Spears 

Perhaps this works in your cell K6.

 

=INDIRECT("[STATISTICS-xlsm]"&A5&"!B4")

 

That comes up as a error... 

best response confirmed by David Spears (Copper Contributor)
Solution

@David Spears 

 

Made a typo. Obviously, it should be "STATISTICS.xlsm" between the brackets,

not "STATISTICS-xlsm". Recreated something similar on my own system and it works for me.

@Riny_van_Eekelen  It also needed an extra apostrophe but it works! thank you.

 

That was the formula I had worked out. But I now realize that the workbook it references has to be open. If I close that workbook the formula does not work. Is there a way to change that?

@David Spears 

True, INDIRECT doesn't work with closed workbooks. INDEX can do the trick, but at the moment I'm not able to look into this further.

1 best response

Accepted Solutions
best response confirmed by David Spears (Copper Contributor)
Solution

@David Spears 

 

Made a typo. Obviously, it should be "STATISTICS.xlsm" between the brackets,

not "STATISTICS-xlsm". Recreated something similar on my own system and it works for me.

View solution in original post