1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find & Replace within a formula that has an absolute reference

Discussion in 'Ask an Excel Question' started by DavidB1175, Sep 12, 2018.

  1. DavidB1175

    DavidB1175 New Member

    Messages:
    4
    Hi, I have a rather large worksheet for Chart Data that is for different charts within the workbook but the cell references are pointing to another workbook. I want to replicate the charts and chart data from fiscal year (FY18) to fiscal year (FY19) and also change the months date to 2018 and 2019 using Find And Replace. I have about 200 rows with 12 columns, one for each month. My question is, how can I keep Excel from trying to find the old file and the corresponding worksheet to just change the numbers within the cell references until they are all updated and then ask Excel to update the workbook. Thanks, David

    =SUM('U:\4 Statistics\FY18 Monthly Statistics Systemwide and Branch\[Library-System-FY-2018-Stats.xlsx]Oct2017'!$B$58)

    I realize the file name is rather long as the main file is on a Network shared drive and its named as such for others to find it. If I can just change 18 to 19 and 2017 to 2018 it will save a lot of work.
  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    Look in the connections -> Ribbon: Data -> Connections
    upload_2018-9-12_20-9-50.png
    First look if the file is not used in the workbook (Locations where...)
    If not, delete the connection.
    Also look in the name manager to see if you don't have a range reference to that external file.
    Check (macros are available on the net) if you have trailing data series referring to the external file. Update/delete these data series.
  3. DavidB1175

    DavidB1175 New Member

    Messages:
    4
    Hi. Thank you for the tips. I found no Locations where connections are. I did go through the name manager and deleted locations other than in the existing workbook. Though, in the Connections>Edit Links I was able to change the source with most of the rows but somehow I lost the cell references and the formulas switched to number values when I tried to change the source to the main external file. The problem I seem to have is changing the link to the FY18 folder to the new FY19 folder and changing the source link to each of the FY-2019 files. Thanks.
  4. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    Without seeing the file it is difficult to help out. Are you sure you updated all elements of your formula?

    Alternative via a macro like this one from
    https://www.ozgrid.com/forum/forum/help-forums/excel-general/122546-vba-replace-part-of-the-formula
    There seems to be 3 parts of your formula that needs to change.
    Code (vb):

    Cell.Formula = Replace(Cell.Formula, "2017'!", "2018'!")
    Cell.Formula = Replace(Cell.Formula, "FY18 ", "FY19 ")
    Cell.Formula = Replace(Cell.Formula, "FY-2018-", "FY-2019-")
     
    Alternative via CTRL + H
    1. replace "=SUM" by "SUM" (-> converts formula to text)
    2. replace "2017'!" by "2018'!"
    3. replace "FY18 " by "FY19 "
    4. replace "FY-2018-" by "FY-2019-"
    5. replace "SUM" by "=SUM" (-> convert to formula again)
    Chirag R Raval likes this.
  5. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    446
    I was wondering how my working practice of never using a cell reference (except to define a Name in the absence of a suitable structured reference) would work out. I think marginally better in that there would be order of magnitude fewer changes to be made at the Name level as opposed to the cell level. I don't see it as solving the problem though.

    Another strategy might be to use Power Query import (and possibly aggregate) the data you wish to chart.
    Chirag R Raval likes this.
  6. DavidB1175

    DavidB1175 New Member

    Messages:
    4
    Thank You Guido and Peter.
    I tried the VBA code and was not successful. I kept running into the debugger. I'm not versed with VBA and it should be something I should learn next. However I used your Replace suggestions to change the cells to text with taking the = sign out and changing the cell format to text. I should have thought of that before but I haven't had problems changes text or numbers in cells before.
    Peter, I curious about how you never use a cell reference when retrieving numbers from another file?
    Thanks for the help, David
  7. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    David, you should not have changed the cell format. What I meant was the simple fact of removing the equal sign "=" converts the formula to text, regardless of the cell format.

    Since you've asked for it :), Peter started this epic thread about the A1-ref style on this forum.
    And he is suggesting to import external data via Power Query and do the aggregation in already in PQ. After this transformation loading the aggregated table into Excel and make your charts on that or those tables, if you'd need more then data table.

    Before I knew about PQ (and before it existed) I rarely (like never) referred to external files. I simply used "external data" in a simple pivot.
    Chirag R Raval likes this.
  8. DavidB1175

    DavidB1175 New Member

    Messages:
    4
    Guido, It appears in my version of 2013 Excel our network IT is not giving me the ability to add-on PQ. I looked through all the options but I did add PowerPivot. After all of this I will work on learning more about pivot tables and using external data as suggested. I'll look for the A1-ref style thread too. Last year I was following the lead of using a Chart Data table but needed to pull numbers from more than one file in order to have them in one place. Thank you for the help. David
    Chirag R Raval likes this.

Share This Page