link formula / update formula automatically

[MartinKoch]

Hi,
I’m working on quite huge spreadsheets. The same formula will be used on more than 30 sheets.
So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming).
I’m wondering if it’s possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas).
I can copy the formula with the “Past Special” function but the function is not updating if I change the formula on the main sheet.
So is it possible to link it and that it’ll be updated automatically when I’m changing the formula on the main sheet?

Thx for your help!
Martin

[Dave Hawley]

Use Edit>Replace a look in Formulas.

[MartinKoch]

but by using the replace function I still need to go to each sheet and run the replace function. Is there any other way to run it through the whole excel sheet at the same time?

[nightkil]

Dave,

I'm actually trying to do the same thing, but I dont know much about excel can you help me finding and using the EDIT>REPLACE which you are talking about?

Thanks.

[MartinKoch]

Hi emelendez,

if you just want to replace numbers / formulas / etc on one sheet is quite easy. Just go to the menue "Edit" (it's next to File) and than go to Replace. If you just need it for one sheet its quite self explaining.

Hope it'll help you!
Martin

[Wigi]

In recent Excel versions, Edit > Replace allows replacing in the whole workbook. Check out the options in the dialog sheet.

Wigi

[MartinKoch]

Ah ok thx. Just realised that I've got a 2000 version on my computer... have to ask my manager to get a new one

[Wigi]

have to ask my manager to get a new one

another - perhaps less appealing - possibility: search the forum for a VBA solution. I'm quite sure it must have been done already. (And isn't so hard in the end)

If not here at Ozgrid, try googling.

Wigi

[Jack in the UK]

Find & Replace
From the top of Excel Your see words like File, Edit, View
Mouse click on the work Edit
From the drop down click Replace
Complete the Find & Replace option in the dialog box
The options should be default Formulae so Your should be Ok

or
From the keyboard press CTRL key + H together
The same dialog appear
Follow on from the above

jiuk

[MartinKoch]

Well I tried it in VBA but the problem is that the funkion I'm using is quite long.

=IF(ISERROR(VLOOKUP(CONCATENATE("GR2_PRIM_GBP_01_"&$B$5&"_"&G$8&"_"&$B18&""),Bal!1:65536,8,FALSE)),0,VLOOKUP(CONCATENATE("GR2_PRIM_GBP_01_"&$B$5&"_"&G$8&"_"&$B18&""),Bal!1:65536,8,FALSE))+IF(ISERROR(VLOOKUP(CONCATENATE("GR2_PRIM_GBP_01_"&$B$5&"_"&G$8&"_"&$C18&""),Bal!1:65536,8,FALSE)),0,VLOOKUP(CONCATENATE("GR2_PRIM_GBP_01_"&$B$5&"_"&G$8&"_"&$C18&""),Bal!1:65536,8,FALSE))+IF(ISERROR(VLOOKUP(CONCATENATE("GR2_PRIM_GBP_01_"&$B$5&"_"&G$8&"_"&$D18&""),Bal!1:65536,8,FALSE)),0,VLOOKUP(CONCATENATE("GR2_PRIM_GBP_01_"&$B$5&"_"&G$8&"_"&$D18&""),Bal!1:65536,8,FALSE))+IF(ISERROR(VLOOKUP(CONCATENATE("GR2_PRIM_GBP_01_"&$B$5&"_"&G$8&"_"&$E18&""),Bal!1:65536,8,FALSE)),0,VLOOKUP(CONCATENATE("GR2_PRIM_GBP_01_"&$B$5&"_"&G$8&"_"&$E18&""),Bal!1:65536,8,FALSE))+IF(ISERROR(VLOOKUP(CONCATENATE("GR2_PRIM_GBP_01_"&$B$5&"_"&G$8&"_"&$F18&""),Bal!1:65536,8,FALSE)),0,VLOOKUP(CONCATENATE("GR2_PRIM_GBP_01_"&$B$5&"_"&G$8&"_"&$F18&""),Bal!1:65536,8,FALSE))

VBA was just able (for what ever reason) to copy part of it and it insered more "" in the formula.
So I think the Replace function will be the best solution.
Thx for your help