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

  • 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

DavidB1175

New Member
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.
 
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.
 
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.
 
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:
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)
 
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.
 
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
 
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.
 
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
 
Back
Top