Change sheet reference in formula?

[David Turner]

XL2000

I have two workbooks, FOODCOST.XLS and INVENTORY TRACKING.XLS that are
individually updated monthly via macros

In INVENTORY TRACKING.XLS:
Cell B42 currently contains the formula
=SUMPRODUCT(('C:\DATA\EXCEL\[FOODCOST.XLS]Nov'!$M$5:$M$34)*('C:\DATA\EXCEL
\[FOODCOST.XLS]Nov'!$K$5:$K$34="Sysco"))
Cell G42 currently contains the formula
='C:\DATA\EXCEL\[FOODCOST.XLS]Nov'!$E$36

FOODCOST.XLS is updated monthly to add a sheet with the abbreviated name
for the next month. Is there some code I can add to an existing routine in
INVENTORY TRACKING.XLS that would automatically adjust these formulas to
refer to the latest month's sheet in FOODCOST.XLS?

Or could I change something in the formulas themselves that will tell them
to look at the range(s) in the latest sheet in FOODCOST.XLS?

 

[Dave Peterson]

I think I'd do this:

Create a new sheet in Foodcost.xls and name it "myblankworksheet" (any valid
unique string of characters).

Then you could just edit|replace "myblankworksheet" with the name of the latest
month--but you'll have to know that.

XL2000

I have two workbooks, FOODCOST.XLS and INVENTORY TRACKING.XLS that are
individually updated monthly via macros

In INVENTORY TRACKING.XLS:
Cell B42 currently contains the formula
=SUMPRODUCT(('C:\DATA\EXCEL\[FOODCOST.XLS]Nov'!$M$5:$M$34)*('C:\DATA\EXCEL
\[FOODCOST.XLS]Nov'!$K$5:$K$34="Sysco"))
Cell G42 currently contains the formula
='C:\DATA\EXCEL\[FOODCOST.XLS]Nov'!$E$36

FOODCOST.XLS is updated monthly to add a sheet with the abbreviated name
for the next month. Is there some code I can add to an existing routine in
INVENTORY TRACKING.XLS that would automatically adjust these formulas to
refer to the latest month's sheet in FOODCOST.XLS?

Or could I change something in the formulas themselves that will tell them
to look at the range(s) in the latest sheet in FOODCOST.XLS?

 

[David Turner]

Dave Peterson wrote

I think I'd do this:

Create a new sheet in Foodcost.xls and name it "myblankworksheet" (any
valid unique string of characters).

Then you could just edit|replace "myblankworksheet" with the name of
the latest month--but you'll have to know that.

I don't follow, and forgive me if I'm missing the point.

Foodcost.xls already has a Sub NewMonth() routine that adds a sheet named
for the following month. Ex: when run from a TextBox button on Nov adds a
sheet named Dec. Section of that macro that does it:

srcName = ActiveSheet.Name
tgtName = Format(CDate(srcName & "-2002") + 32, "mmm")
Sheets(srcName).Copy After:=Sheets(srcName)
Sheets(srcName).TextBoxes.Delete
ActiveSheet.Name = tgtName

That said--after running that routine, I can switch to Food Inventory.xls
and edit|replace Nov with Dec to update the formulas there. Not the
solution I was looking for, but beats trying to edit the formulas from the
formula bar and risk missing one.

 

[Dave Peterson]

How about having the NewMonth sub open the other worksheet and do the change
there, too. Have your macro change the old month ([FOODCOST.XLS]Nov) to the new
month.

(I mistakenly thought that there was a new worksheet (with formulas) being
created every month, too.)

 

[David Turner]

Dave Peterson wrote

How about having the NewMonth sub open the other worksheet and do the
change there, too. Have your macro change the old month
([FOODCOST.XLS]Nov) to the new month.

I'm failing to visualize. Are you saying NewMonth() in Foodcost.xls could
update Inventory.xls formulas? If so, why can't that be done via Inventory
sub? Keep in mind Inventory.xls is not tabbed by month, and its current sub
only clears and transfers some named ranges to update it.

 

[Dave Peterson]

Yep. I'm saying that the macro in FoodCost.xls could update the formulas in
inventory.xls.

dim InvWks as workbook
dim oldMonth as string
dim nextMonth as string
set invwks = workbooks.open("c:\yourpath\inventory.xls").worksheets(1)

oldmonth = "[FOODCOST.XLS]Nov!"
nextmonth = "[FOODCOST.XLS]" & format(date,"mmm") & "!"

with invwks
.Cells.Replace What:=oldmonth, Replacement:=nextmonth, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.parent.save
.parent.close savechanges:=false
end with

But I'm not sure how you know the name of the old month. Can it be retreived
from the system day (minus a week or two???) or could you save it somewhere else
so you could retreive it when you need it (a hidden worksheet???).

And yeah, if you know the months then you could put that macro in either spot.
But I'm still not sure how you know the old one and how you determine the new
one.

But if all else fails, you could just ask with a couple of inputboxes.

Dave Peterson wrote

How about having the NewMonth sub open the other worksheet and do the
change there, too. Have your macro change the old month
([FOODCOST.XLS]Nov) to the new month.

I'm failing to visualize. Are you saying NewMonth() in Foodcost.xls could
update Inventory.xls formulas? If so, why can't that be done via Inventory
sub? Keep in mind Inventory.xls is not tabbed by month, and its current sub
only clears and transfers some named ranges to update it.

 

[David Turner]

Dave Peterson wrote

But if all else fails, you could just ask with a couple of inputboxes.

Since I want total control over when this book gets updated, and I don't
want to rely on the other book being opened, that's what I settled on:

'===stolen from a Google Groups post:
Dim wString As String
Dim rString As String
wString = InputBox("Enter 'What String'", "What String")
rString = InputBox("Enter 'Replacement String'", "Replacement String")
Cells.Replace What:=wString, _
Replacement:=rString, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

I did have to change the wording in a couple of descriptive cells to avoid
them being changed.

Thanks for hanging in there with me.

 

[David Turner]

Dave Peterson wrote

But I'm not sure how you know the name of the old month.

That was the reason for my original post, asking if maybe I could reference
a sheet index of Foodcost.xls or something like that.

 

[David Turner]

Dave Peterson wrote

But I'm not sure how you know the name of the old month.

Hmm...

This worked as long as Foodcost.xls was open:

wString = Workbooks("foodcost.xls").Sheets(5).Name
rString = Workbooks("foodcost.xls").Sheets(6).Name
Cells.Replace What:=wString, _
Replacement:=rString, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

 

[David Turner]

Dave Peterson wrote

And yeah, if you know the months then you could put that macro in
either spot. But I'm still not sure how you know the old one and how
you determine the new one.

"Cake and eat it, too" final code:

Sub Renew()
Dim OldMonth As String
Dim NewMonth As String
Dim wkbk As Workbook
Set wkbk = ActiveWorkbook
Application.ScreenUpdating = False
Workbooks.Open "Foodcost.xls"
wkbk.Activate
Range("Initial_Qty").Value = Range("On_Hand").Value
Range("Added_Used").ClearContents
OldMonth = Workbooks("foodcost.xls").Sheets(5).Name
NewMonth = Workbooks("foodcost.xls").Sheets(6).Name
Cells.Replace What:=OldMonth, _
Replacement:=NewMonth, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Workbooks("Foodcost.xls").Close
Application.ScreenUpdating = True
End Sub

 

[thierry le queau]

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!