ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Sheet name in formula (https://www.excelbanter.com/excel-worksheet-functions/21196-sheet-name-formula.html)

SMullins

Sheet name in formula
 
This is a formula I us daily

(example)
='V:\Production Reporting Summaries\Tammy\April 05\[Production
Summary.xls]4_04'!$B$4

each day I have to make a copy the previous days worksheet and then go into
multiply cells and change 4_04 to the next day, which would be 4_05.

I want to be able to enter 4_05 into one cell and have it update 4_04
automatically so I am not manually changing the formula daily.


Ron Coderre

Is there a reason you can't use Excel's Search/Replace?

EditReplace
Find what: 4_04
Replace with: 4_05

That should replace every instance of 4_04 in your worksheet with 4_05.

Does that help?

Regards,
Ron

SMullins

This would change soom parts of a formula in the sheet that I do not want
changed.

"Ron Coderre" wrote:

Is there a reason you can't use Excel's Search/Replace?

EditReplace
Find what: 4_04
Replace with: 4_05

That should replace every instance of 4_04 in your worksheet with 4_05.

Does that help?

Regards,
Ron


LanceB

Look at indirect
example

in a1 the text
'Sheet2!
=INDIRECT(A1&"c2")
will refererence c2 on sheet 2
=INDIRECT(A1&"d9")
will refererence d9 on sheet 2

Then all that has to change is the one reference in a1




"SMullins" wrote:

This is a formula I us daily

(example)
='V:\Production Reporting Summaries\Tammy\April 05\[Production
Summary.xls]4_04'!$B$4

each day I have to make a copy the previous days worksheet and then go into
multiply cells and change 4_04 to the next day, which would be 4_05.

I want to be able to enter 4_05 into one cell and have it update 4_04
automatically so I am not manually changing the formula daily.



All times are GMT +1. The time now is 07:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com