Change sheet reference in formula?

D

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?
 
D

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.)
 
D

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.
Click to expand...

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

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.


David said:
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.
Click to expand...

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.
Click to expand...
 
D

David Turner

Dave Peterson wrote
But if all else fails, you could just ask with a couple of inputboxes.
Click to expand...

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

David Turner

Dave Peterson wrote
But I'm not sure how you know the name of the old month.
Click to expand...

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

David Turner

Dave Peterson wrote
But I'm not sure how you know the name of the old month.
Click to expand...

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
 
D

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.
Click to expand...

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