Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: link formula / update formula automatically

  1. #1
    Join Date
    28th July 2006
    Posts
    75

    link formula / update formula automatically

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

  3. #3
    Join Date
    28th July 2006
    Posts
    75

    Re: link formula / update formula automatically

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th July 2006
    Posts
    146

    Re: link formula / update formula automatically

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th July 2006
    Posts
    75

    Re: link formula / update formula automatically

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    5,892

    Re: link formula / update formula automatically

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

    Wigi

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    28th July 2006
    Posts
    75

    Re: link formula / update formula automatically

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    5,892

    Re: link formula / update formula automatically

    Quote Originally Posted by MartinKoch
    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,682

    Re: link formula / update formula automatically

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    28th July 2006
    Posts
    75

    Re: link formula / update formula automatically

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: February 8th, 2008, 10:26
  2. Link Formulas Don't Update Automatically
    By itchock in forum EXCEL HELP
    Replies: 7
    Last Post: September 11th, 2006, 14:51
  3. Automatically update link when file opened?
    By J88L in forum EXCEL HELP
    Replies: 5
    Last Post: March 24th, 2006, 01:44
  4. Link Chart to automatically update
    By hbgray in forum Excel and/or Powerpoint Help
    Replies: 1
    Last Post: February 16th, 2006, 22:03
  5. Update link automatically
    By subash in forum EXCEL HELP
    Replies: 3
    Last Post: September 11th, 2005, 08:51

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno