Nov 12 2019 09:25 AM
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
Nov 12 2019 11:39 AM
Nov 12 2019 12:03 PM
Solution
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.
Nov 14 2019 06:00 AM
@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?
Nov 14 2019 06:24 AM
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.
Nov 12 2019 12:03 PM
Solution
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.