CODEWITHSUNDEEP
excel
Doug Fir
Doug Fir

Reputation: 21242

Using Find Replace To Update Worksheet References

I have a report that I must update each week.

Throughout the report are references to another daily report of the format:

20140610_DailyReport

So each week when it comes to updating I am supposed to CTRL+H and replace 20140603_DailyReport with 20140610_DailyReport

The references are used within forumula such as: SUMPRODUCT( ('Daily\05 May\20140610_Daily_Dashboard.xlsx'!PayDate>=C3)* ('Daily\05 May\20140610_Daily_Dashboard.xlsx'!PayDate<=C4))

But when I try find/replace now excel is opening windows explorer and for each cell where the change is made, I have to select the file in question. It's really annoying.

How can I CTRL+H to update the path of the report being referenced without having to manually select each path for each cell?

Upvotes: 1

Views: 38

Answers (1)

pnuts
pnuts

Reputation: 59475

Structure your formulae to refer to a variable and use Ctrl+h to change that variable.

Upvotes: 1

Related Questions