Sheet name in formula

[Guest]

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.

 

[º¼ÖÝÈË]

you can use VBA

 

[Guest]

VBA? Most likely not

 

[Guest]

Have you tried using Search & Replace? Ctrl-H is the shortcut key

 

[Guest]

Changes formulas that I do not want changed

Have you tried using Search & Replace? Ctrl-H is the shortcut key

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.

 

[Guest]

How about, recording a macro.

Have say cell A1 as what the old file is
and cell A2 the new one.

Then record a macro that would pick up A1 and replace with A2.

then you'd just have to change A1 and A2 ecah day and run the macro.

Might work.

Changes formulas that I do not want changed

Have you tried using Search & Replace? Ctrl-H is the shortcut key

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.

 

[Guest]

The course you'll have to follow then is this:

change your formula
='V:\Production Reporting Summaries\Tammy\April 05\[Production
Summary.xls]4_04'!$B$4

to
=SUMPRODUCT(INDIRECT("'V:\Production Reporting Summaries\Tammy\April
05\[Production Summary.xls]"&A1&"'!$B$4"))

and then put your 4_04 value in cell A1

Changes formulas that I do not want changed

Have you tried using Search & Replace? Ctrl-H is the shortcut key

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.

 

[Guest]

This formula gives me an invalid cell reference. Does the other workbook
have to be open?

The course you'll have to follow then is this:

change your formula
='V:\Production Reporting Summaries\Tammy\April 05\[Production
Summary.xls]4_04'!$B$4

to
=SUMPRODUCT(INDIRECT("'V:\Production Reporting Summaries\Tammy\April
05\[Production Summary.xls]"&A1&"'!$B$4"))

and then put your 4_04 value in cell A1

Changes formulas that I do not want changed

Have you tried using Search & Replace? Ctrl-H is the shortcut key


:

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.

 

[Chip Pearson]

Yes, INDIRECT requires that the file be open.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

This formula gives me an invalid cell reference. Does the
other workbook
have to be open?

The course you'll have to follow then is this:

change your formula
='V:\Production Reporting Summaries\Tammy\April 05\[Production
Summary.xls]4_04'!$B$4

to
=SUMPRODUCT(INDIRECT("'V:\Production Reporting
Summaries\Tammy\April
05\[Production Summary.xls]"&A1&"'!$B$4"))

and then put your 4_04 value in cell A1

Changes formulas that I do not want changed

:

Have you tried using Search & Replace? Ctrl-H is the
shortcut key


:

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.