Replacing Text in formula that matches the text in another cell

C

clemons171

New Member
Joined
Aug 10, 2016
Messages
25
Example: On Sheet1, Cell A1 has the text "sheet2" in it. Some of the cells below A1 on Sheet1 have a formula that returns a value from a cell on Sheet2. I then put the text "Sheet3" in cell B1. Then I make a copy of Sheet2 and label it Sheet3. However, all the formulas in Sheet1 under B1 have the text "Sheet2" in them. I need a macro that replaces all the text in the formulas under each respective column to match the text in A1, B1, C1, D1, etc. So each columns formulas return values from the sheets that match the text in row 1.
 

Attachments

  • Capture.PNG
    Capture.PNG
    47.3 KB · Views: 22

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
why dont you use the cell with the sheet name as a reference in your formula?
--------------------
Book1
AB
1SiteworkParking Deck
2s1pd1
3s2pd2
4s3pd3
5s4pd4
6s5pd5
7s6pd6
8s7pd7
9s8pd8
10s9pd9
11s10pd10
Cost Model
Cell Formulas
RangeFormula
A2:B2A2=INDIRECT("'"&A$1&"'"&"!A2")
A3:B3A3=INDIRECT("'"&A$1&"'"&"!A3")
A4:B4A4=INDIRECT("'"&A$1&"'"&"!A4")
A5:B5A5=INDIRECT("'"&A$1&"'"&"!A5")
A6:B6A6=INDIRECT("'"&A$1&"'"&"!A6")
A7:B7A7=INDIRECT("'"&A$1&"'"&"!A7")
A8:B8A8=INDIRECT("'"&A$1&"'"&"!A8")
A9:B9A9=INDIRECT("'"&A$1&"'"&"!A9")
A10:B10A10=INDIRECT("'"&A$1&"'"&"!A10")
A11:B11A11=INDIRECT("'"&A$1&"'"&"!A11")

--------------------
Book1
A
1name
2s1
3s2
4s3
5s4
6s5
7s6
8s7
9s8
10s9
11s10
Sitework

--------------------
Book1
A
1name
2pd1
3pd2
4pd3
5pd4
6pd5
7pd6
8pd7
9pd8
10pd9
11pd10
Parking Deck
 
Upvote 0
clemons171 said:
Example: On Sheet1, Cell A1 has the text "sheet2" in it. Some of the cells below A1 on Sheet1 have a formula that returns a value from a cell on Sheet2. I then put the text "Sheet3" in cell B1. Then I make a copy of Sheet2 and label it Sheet3. However, all the formulas in Sheet1 under B1 have the text "Sheet2" in them. I need a macro that replaces all the text in the formulas under each respective column to match the text in A1, B1, C1, D1, etc. So each columns formulas return values from the sheets that match the text in row 1.
Click to expand...
Garco House - DD Budget 081722.xlsx
EFGHIJK
102SiteworkParking DeckTownhomesApartments
103
10467,105156,579178,947111,842
105
106
107
108029,34429,34429,344
109
1100000
111
112
113
1140143,094143,094143,094
115
11603,7553,7553,755
117
118
119
1200137,072137,072137,072
121
122
123
1240144,000144,000144,000
125
1260110,775110,775110,775
127
128
129
1300718,964718,964718,964
131
132087,00087,00087,000
133
134061,70061,70061,700
135
136
137
1380181,270181,270181,270
139
1400200,625200,625200,625
141
142052,66552,66552,665
143
144016,47916,47916,479
145
146011,26511,26511,265
Cost Model - Inn
Cell Formulas
RangeFormula
E102,K102,I102,G102E102=+E46
E104E104=Sitework!M24
G104G104='Parking Deck'!$M$24
I104I104=Townhomes!$M$24
K104K104=Apartments!$M$24
G108G108='Parking Deck'!$M$62
I108I108=Townhomes!$M$62
K108K108=Apartments!$M$62
G110G110='Parking Deck'!$M$70
I110I110=Townhomes!$M$70
K110K110=Apartments!$M$70
G114G114='Parking Deck'!$M$74
I114I114=Townhomes!$M$74
K114K114=Apartments!$M$74
G116G116='Parking Deck'!$M$86
I116I116=Townhomes!$M$86
K116K116=Apartments!$M$86
G120G120='Parking Deck'!$M$92
I120I120=Townhomes!$M$92
K120K120=Apartments!$M$92
G124G124='Parking Deck'!$M$102
I124I124=Townhomes!$M$102
K124K124=Apartments!$M$102
G126G126='Parking Deck'!$M$106
I126I126=Townhomes!$M$106
K126K126=Apartments!$M$106
G130G130='Parking Deck'!$M$122
I130I130=Townhomes!$M$122
K130K130=Apartments!$M$122
G132G132='Parking Deck'!$M$142
I132I132=Townhomes!$M$142
K132K132=Apartments!$M$142
G134G134='Parking Deck'!$M$148
I134I134=Townhomes!$M$148
K134K134=Apartments!$M$148
G138G138='Parking Deck'!$M$164
I138I138=Townhomes!$M$164
K138K138=Apartments!$M$164
G140G140='Parking Deck'!$M$176
I140I140=Townhomes!$M$176
K140K140=Apartments!$M$176
G142G142='Parking Deck'!$M$182
I142I142=Townhomes!$M$182
K142K142=Apartments!$M$182
G144G144='Parking Deck'!$M$192
I144I144=Townhomes!$M$192
K144K144=Apartments!$M$192
G146G146='Parking Deck'!$M$200
I146I146=Townhomes!$M$200
K146K146=Apartments!$M$200
 
Upvote 0
setup one column with the formulas below, then can use a similar macro as below to copy the formulas over
(or you can simply select e104:e146, copy it, then paste it to row 104 of each column you need to)

Book1
EFG
102SiteworkParking Deck
103
104testing0
105
106
107
1080test2
109
11000
111
112
113
11400
115
11600
117
118
119
12000
121
122
123
12400
125
12600
127
128
129
13000
131
13200
133
13400
135
136
137
13800
139
14000
141
14200
143
14400
145
14600
Sheet8
Cell Formulas
RangeFormula
E102,G102E102=+E46
E104,G104E104=INDIRECT("'"&E$102&"'"&"!M24")
E108,G108E108=INDIRECT("'"&E$102&"'"&"!M62")
E110,G110E110=INDIRECT("'"&E$102&"'"&"!M70")
E114,G114E114=INDIRECT("'"&E$102&"'"&"!M74")
E116,G116E116=INDIRECT("'"&E$102&"'"&"!M86")
E120,G120E120=INDIRECT("'"&E$102&"'"&"!M92")
E124,G124E124=INDIRECT("'"&E$102&"'"&"!M102")
E126,G126E126=INDIRECT("'"&E$102&"'"&"!M106")
E130,G130E130=INDIRECT("'"&E$102&"'"&"!M122")
E132,G132E132=INDIRECT("'"&E$102&"'"&"!M142")
E134,G134E134=INDIRECT("'"&E$102&"'"&"!M148")
E138,G138E138=INDIRECT("'"&E$102&"'"&"!M164")
E140,G140E140=INDIRECT("'"&E$102&"'"&"!M176")
E142,G142E142=INDIRECT("'"&E$102&"'"&"!M182")
E144,G144E144=INDIRECT("'"&E$102&"'"&"!M192")
E146,G146E146=INDIRECT("'"&E$102&"'"&"!M200")

-------
you can add more columns as needed. i wasnt sure how many you might have.

VBA Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Range("E104").Select
    ActiveWindow.SmallScroll Down:=27
    Range("E104:E146").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-24
    Range("G104").Select
    ActiveSheet.Paste
    Range("I104").Select
    ActiveSheet.Paste
    Range("K104").Select
    ActiveSheet.Paste
    Range("M104").Select
    ActiveSheet.Paste
    Range("O104").Select
    ActiveSheet.Paste
    Range("Q104").Select
    ActiveSheet.Paste
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Copy and paste based on sheet name and cell value
Replies
4
Views
390
skillilea
S
S
  • Solved
IF & Vlookup formula query
Replies
3
Views
149
alansidman
A
G
  • Solved
Searching a column for any partial matches from another cell text
Replies
4
Views
283
Joe4
Joe4
N
  • Solved
Amend VLOOKUP Formula to point to a cell with 'Sheet name' as opposed to using the 'Sheet name' itself
Replies
3
Views
258
Fluff
Fluff
T
  • Solved
IF(ISERROR Array formula
Replies
4
Views
220
Tablecloth98
T

Forum statistics

Threads
1,214,992
Messages
6,122,631
Members
449,095
Latest member
bsb1122

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