Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
SMullins
 
Posts: n/a
Default 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.

  #2   Report Post  
ΊΌΦέΘΛ
 
Posts: n/a
Default

you can use VBA


  #3   Report Post  
SMullins
 
Posts: n/a
Default

VBA? Most likely not

"ΒΊΒΌΓ–ΓΓˆΓ‹" wrote:

you can use VBA



  #4   Report Post  
Duke Carey
 
Posts: n/a
Default

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


"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.

  #5   Report Post  
SMullins
 
Posts: n/a
Default

Changes formulas that I do not want changed

"Duke Carey" wrote:

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


"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.



  #6   Report Post  
Tom Hewitt
 
Posts: n/a
Default

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.

"SMullins" wrote:

Changes formulas that I do not want changed

"Duke Carey" wrote:

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


"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.

  #7   Report Post  
Duke Carey
 
Posts: n/a
Default

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





"SMullins" wrote:

Changes formulas that I do not want changed

"Duke Carey" wrote:

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


"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.

  #8   Report Post  
SMullins
 
Posts: n/a
Default

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

"Duke Carey" wrote:

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





"SMullins" wrote:

Changes formulas that I do not want changed

"Duke Carey" wrote:

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


"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.

  #9   Report Post  
Chip Pearson
 
Posts: n/a
Default

Yes, INDIRECT requires that the file be open.


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


"SMullins" wrote in message
...
This formula gives me an invalid cell reference. Does the
other workbook
have to be open?

"Duke Carey" wrote:

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





"SMullins" wrote:

Changes formulas that I do not want changed

"Duke Carey" wrote:

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


"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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
How do I set up a formula on a time sheet to calculate time in 1/. gschmid Excel Discussion (Misc queries) 2 January 18th 05 02:48 PM
Need formula for sheet & cell reference MPH Excel Worksheet Functions 0 January 16th 05 04:39 PM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 02:27 AM
Formula to refer to other worksheet... Liz-In-USA Excel Worksheet Functions 4 November 9th 04 11:51 PM


All times are GMT +1. The time now is 03:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"