Announcement

Collapse
No announcement yet.

link formula / update formula automatically

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: link formula / update formula automatically

    Use Edit>Replace a look in Formulas.

    Comment


    • #3
      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?

      Comment


      • #4
        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.

        Comment


        • #5
          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

          Comment


          • #6
            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
            Regards,

            Wigi

            Excel MVP 2011-2014

            For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

            -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

            Comment


            • #7
              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

              Comment


              • #8
                Re: link formula / update formula automatically

                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
                Regards,

                Wigi

                Excel MVP 2011-2014

                For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: link formula / update formula automatically

                      Originally posted by 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))
                      Is this long? C'mon

                      What I meant was something like this:

                      Code:
                      Sub replac()
                      Dim ws As Worksheet
                      For Each ws In Worksheets
                          ws.Cells.Replace What:="A", Replacement:="B", _
                              SearchOrder:=xlByRows, MatchCase:=False
                      Next
                      End Sub
                      to replace A by B. Adjust to what you need.

                      Wigi
                      Regards,

                      Wigi

                      Excel MVP 2011-2014

                      For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                      -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                      Comment


                      • #12
                        Re: link formula / update formula automatically

                        emelendez80, please don't hijack other members posts. If you have a problem start a New Thread and, if needed, link to this thread.


                        MartinKoch, please use code tags when posting any code in future. As per the rules you agreed to.

                        Comment


                        • #13
                          Re: link formula / update formula automatically

                          Sorry MartinKoch, my bad, it wasn't code. That's one loooooooooooong formula!

                          Comment

                          Working...
                          X