Change linked worksheet based on cell value

[Excellent432]

Hello,

I am trying to point excel workbook 1 to a worksheet in a linked workbook 2. basically the worksheet name changes and I need to update all the formulas that point to that worksheet on a weekly basis. Is there a way to automate this?

i.e. in workbook 1, I have the formula =C:\Users\[USER]\Desktop\[Copy of 000000 planning.xlsm]&L1&"'!"&N43 in an attempt to point workbook 1 to cell N43 in workbook 2, based on the value of "L1" in workbook 1. I can get it to return the path i.e. but not to evaluate. Can anyone help with this?

note: I have L1 auto updating with the current workbook's name, so it can run automatically.

 

[offthelip]

you need to use the INDIRECT function