Requesting Special Macro - Replacing constants and formulas

0
votes

This might sound dumb, please excuse me for that. Hopefully someone can help me out.

Situation: I have a worksheet (not whole workbook) that contains constants (Assumption values, manually fed in the sheet) and also formulas (Again assumption values, but not constants). These assumption values are accessed by 1000's of cells in the whole workbook.

Problem: Other people in other departments, forcibly have to open this huge excel file to add their assumptions in, and their PCs are slow for that.

Goal: Goal is that I can take out this assumption tab out of the huge excel sheet and make it a separate excel sheet, so that people from other departments can update this separate file and my own huge excel file will pull this data in and let other tabs in the huge excel file use these user inputted values.

What I am trying to do is, trying to write a special macro:

1) Searches for constants in the worksheet (not book), then searches for all the dependent cells in the whole workbook, and replaces the formulas in those dependent tabs, to refer to the new assumption sheet that I ultimately want to take out of the huge excel file.

Also, all the dependent cells have formulas that refer to FIXED cell within the assumption sheet. That is formula for them is ...'Assumptions'!$J$34...notice the $ signs.

Right now, I am manually going to each assumption tab cell with constant, getting its address, doing a CTRL + F and replace all the dependent cell's formulas with the new sheet's corresponding cell address. I am keeping the new separated sheet and its cells at exact corresponding address as in the huge excel sheet. This is tough, since constants in my assumptions tab are 100s in number (The huge file is 13 years old, hence obviously sucky)

Please let me know if this is possible via one macro? If not, can the goal be achieved via multiple macros?

OR: Is there a way, I can link Multiple cells of a sheet, to multiple corresponding cells of another sheet eg: A is linked to B as -> Value in cell A = "=B".

I would like a macro that does this for All A's in a particular range to new values of Bs from other sheet eg new value in A = "=NewValueOf_B".

I am not sure if I can post this to other forums? Can I?

1
excelexcel-formulavba
Instead of changing all your formulas throughout the workbook why not just keep your assumption sheet and link those values to the new external sheet. Now you don't have to worry about finding every reference and maybe missing one. – Automate This
That could have been done, yes. But I thought process was more to get a hang of a macro, that could 'link at once' for all constants..you know what I mean. Whether I do this your way, or my way..the end goal is to find a macro that can replace all constants at once so that I save the effort of manually linking hundreds of cells. – Ash
I'm not sure this is do-able. IIRC, there is no way to get off-sheet dependencies from Excel-VBA. – RBarryYoung

1 Answers

0
votes

If I'm understanding your problem, your goal is to have a exact duplicate of your Assumption sheet in another file and change all the formulas to link to this new file.

If I'm right you don't need a macro for that. You can create this new file, and then just do a Replace changing 'Assumptions'! with '[New File Name.xlsx]Assumptions'!