How? Update all formulas when A1 changes

S

srburk

Board Regular
Joined
Mar 25, 2002
Messages
154
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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
Click to expand...

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?
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0
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
Click to expand...

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.
 
Upvote 0
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?
 
Upvote 0
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]
Click to expand...


THAT WORKED! THANKS!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Consolidate or Similar, Only with Formula Reference?
Replies
0
Views
263
Scotster
S
S
  • Question Question
Use formula to name table columns
Replies
4
Views
377
sjrpdx
S
T
  • Question Question
Automatically Copying Data To A New Workbook When Closing A Workbook
Replies
4
Views
430
Tmini
T
A
  • Question Question
Changing row number dynamically in link to another file
Replies
1
Views
202
AlphaFrog
AlphaFrog
S
  • Question Question
Sorting through Data with Multiple Criteria (Index, Match, CountIF)?
Replies
1
Views
285
STATluver
S

Forum statistics

Threads
1,223,962
Messages
6,175,654
Members
452,664
Latest member
alpserbetli

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top