How to Reference the Text in a cell of another spreadsheet

[tobias123]

Quite a complicated question (well it is to me anyway). I have a workbook, with 2 sheets in. On Sheet 1, (Summary) I have a column of week numbers which correspond to Sheet 2 (Plan). I have to fill these in manually, but I have been asked to create a formula or find one, to automatically update the Summary, when I update the Plan.

Please see screenshots attached to further understand what I mean.

Summary.jpgPlan.jpg

For security reasons, I've had to blank out most of the data, but the principle remains the same.
What I must do, is on Summary, the "Wk 7" that you can see, relates to the "Go-Live" in Plan, but I need a way to automate this, so that when the Plan changes, and the "Go-Live" data moves position, it will automatically update on Summary.

I'm guessing I somehow need to get Excel to search for the words "Go-Live" and then get it to reference the column number that its in. But I have no idea how to do this.

Somebody pleaseeeeeeeeeee help! I've been trying to do this for hours now and have had no luck.

[TMS]

Welcome to the forum.

Unfortunately, pictures of workbooks and worksheets are next to useless when it comes to working up an answer to a question. Please upload a sample workbook.

There are lots of things you can do from a basic "=" with a fully qualified sheet/cell reference to a VLOOKUP or an INDEX/MATCH ... that would be the 'search for the words "Go Live"'.


Regards, TMS

[tobias123]

Okay thank you, I've uploaded an example workbook now.

I am easily able to do this with a simple '=' formula, but then if the date in Plan changes, it won't update in Summary because it is no longer in the same cell.

Test.xlsm

[tobias123]

bumpppppppppppppppp