How? Update all formulas when A1 changes

[srburk]

Lets say I have hundreds of formulas throughout the worksheet that contain the number 2311. 2311 may refer to a different worksheet name or a different file name. It could refer to anything.

I may have 10 identical sheets in the same workbook that refers to similar information but the 2311 needs to be changed for each.

It is not feasible for me to go through hundreds of formulas to change 2311 to 3405 on this sheet and 2311 to 4595 on that sheet.

I'd like to place the number in A1 and have each formula grab the number there. This will allow me to update it.In A1 I have the number 2311.

Is there any way?

 

[Datsmart]

Use a cell reference in your formula such as... =9*A1
If you have many formulae like: =9*2311, then you can use Find and Replace to change all the 2311's to A1's.

 

[Greg Truby]

Yes. That's what the INDIRECT() function is all about. For example let's say 2311 is a sheet name. So you put 2311 in cell A1.
Your old formula in B1 was <ul>[*]=2311!C29*18+2311!J12[/list]Your new formula would be<ul>[*]=indirect(a1 & "!C29")*18+indirect(a1 & "!J12")[/list]HTH

 

[srburk]

Yes. That's what the INDIRECT() function is all about. For example let's say 2311 is a sheet name. So you put 2311 in cell A1.
Your old formula in B1 was <ul>[*]=2311!C29*18+2311!J12[/list]Your new formula would be<ul>[*]=indirect(a1 & "!C29")*18+indirect(a1 & "!J12")[/list]HTH

How would I combine that with a reference to a pivot table?
=GETPIVOTDATA(PV1,"6110 (A1) 2004")

A1 = 2311

I was hoping that it would result in the following formula:
=GETPIVOTDATA(PV1,"6110 2311 2004")
but it gives me a #n/a

Is it possible to combine an indirect with the getpivotdata function?

 

[Troyville]

Write a simple macro. here's the code. In my macro I needed to replace last month in all formulas with this month. You can adjust to do what you need.

Dim lastmonth
Dim thismonth
lastmonth = InputBox("Replace What - Enter Last Month", "Find and Replace")
thismonth = InputBox("Replace With What - Enter Current Month", "Find and Replace")
Dim Sht As Worksheet
For Each Sht In Worksheets
Sht.Cells.Replace What:=lastmonth, Replacement:=thismonth, LookAt:=xlPart, MatchCase:=False
Next

 

[Greg Truby]

In some limited testing I was able to concatenate text inside the GetPivotData() with no problems, so for your example I think the following syntax should work<ul>[*]=GetPivotData(PV1,"6110 " & A1 & " 2004")[/list]

 

[srburk]

Write a simple macro. here's the code. In my macro I needed to replace last month in all formulas with this month. You can adjust to do what you need.

Dim lastmonth
Dim thismonth
lastmonth = InputBox("Replace What - Enter Last Month", "Find and Replace")
thismonth = InputBox("Replace With What - Enter Current Month", "Find and Replace")
Dim Sht As Worksheet
For Each Sht In Worksheets
Sht.Cells.Replace What:=lastmonth, Replacement:=thismonth, LookAt:=xlPart, MatchCase:=False
Next

Problem is, I don't want to replace it in every sheet. I want each sheet to have its formulas point back to its own A1.

 

[Greg Truby]

Troy, maybe I'm missing something? What's the advantage to the macro? If I wanted to do what you're suggesting (which is not what the OP is requesting to do BTW) then why not just right-click any worksheet tab and pick Select All Sheets from the popup and then hit Ctrl+H?

 

[srburk]

In some limited testing I was able to concatenate text inside the GetPivotData() with no problems, so for your example I think the following syntax should work<ul>[*]=GetPivotData(PV1,"6110 " & A1 & " 2004")[/list]

THAT WORKED! THANKS!