Maco to automatically replace/update links in formulas

0

All,

      I have developed a system at my place of work that we use to track information from our machinery lines(how much we ran of certain models, scrap, downtime, etc). This inforamtion is then pulled from various other files in sub folders(Quality information, passdown information, etc.) These are all within a master folder. As you all know with the limits of excel, anything in a sub directory of the directory you are copy-pasting, does not update. Is there any sort of macro that can be made that will go through and update certain sections of multiple formulas in cells across a whole workbook to re-establish the link with the newly copied and renamed folders? For example part of a formula contain this "C:/Example/Master/DirectRun/1st Shift.xlsx" and since this is within a sub folder of the master folder itself, this link does not update automatically, I need it to be able to update to things like "C:/Example/{Month}/DirectRun/1st Shift.xlsx"

I will be honest I have no experience with macros at all, I have been going over the forum and I have found topics about how to START this type of macro but have not found a detailed enough article on how to fully enact something of my EXACT needs. I am learning as much about macros as I can in the time that I can do it in so please do no thing that I am just trying to get a one time pass so I can be on my merry way part of my job relies on Excel experience so I am on this forum every single day working on learning this part of excel.

Thank you all,

Jeremey

Answer
Discuss

Answers

0

Hi and welcome to the forum!

It sounds to me like you might just have Calculations set to Manual instead of Automatic. Go to where the formulas are and go to the Formulas tab and click Calculate Sheet and see if that helps. Otherwise, I would need a little more clarification on exactly what you need to do - Excel macros can of course go through all of your formulas and replace certain strings (text) with other strings (text).

However, what you might do if you just want to replace the values once is to use the Find/Replace feature that is already in Excel.

  1. Go to the formula that has the value that you want to replace and copy the text to replace, along with the slash before and after it. 
  2. Hit Ctrl + H to go to the Find/Replace window and tab.
  3. In Find What, paste in the text to replace.
  4. In Replace With, put the text to replace it with.
  5. Click Options to get more advanced features visible.
  6. Within should be set to Sheet
  7. Search can be left alone.
  8. Look in should be set to Formulas
  9. Click Replace once or twice or Find Next once or twice to make sure that it is matching the correct values and replacing them with the correct values.
  10. Once you are sure that it is finding and replace the correct values, you can click Replace All.
  11. Also, If you change Within from Sheet to Workbook, then it will do this for the entire workbook.

Be careful! Make a backup of your spreadsheet before doing this because you might end up replacing values that you did not mean to replace.

Let me know how this works for you!

Discuss
0

"As you all know with the limits of excel, anything in a sub directory of the directory you are copy-pasting, does not update." Please count me out. I don't know that. But the likely reason for this lack of knowledge might be my lack of experience in working with links. In my experience they are nothing but trouble. Your question proves my point.

Where I have need to access data from another workbook I would rather use VBA than formulas. Your idea to use VBA to write or amend formulas is another no-no for me: no like and no do. It's inefficient. If the worksheet formula can't do the job then let VBA do it.

If you need references like "C:/Example/{Month}/DirectRun/1st Shift.xlsx" in your worksheet formulas I suggest that you look at the INDIRECT and ADDRESS functions. Write the full path in a cell and let the formulas take correct path from there. Change the path in the cell and your formulas refer to the changed path. As a variant of this you could just write the month in a cell (or even extract it automatically from a date in your data) and create the full path, using that name, elsewhere.

This solution would require you to change all your formulas but it would do away with the kind of difficulties you face now and would do it without VBA. If you would post a copy of your workbook I could suggest formulas implementing this concept.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login