Link Master Formula to Different Data

Greetings folks, first post here. Might be here more in the future, thinking of getting a double masters in mechanical engineering and excel is surely my friend! Any help ahead of time is much appreciated! Been scouring the internet to figure this out, and haven't had any success.

What I'm trying to do is link a formula to a different sheet/workbook, but be able to reference new data in a separate sheet/workbook. My "Master Formula" will need to be modified many times, and I need it to update in new sheets with different data. I don't want to copy/paste the formula as the master formula will need to be changed as I fine tune my work. formula reference.jpg

I would recommend using a UDF (User Defined Function).

Please Login or Register  to view this content.

For Sheet1's formula, make sure you explicitly reference everything on Sheet1. So, rather than having Sheet1!C4 use the formula =A4-B4, make Sheet1!C4 use the formula =Sheet1!A4-Sheet1!B4.

Then, on Sheet2!C4, you can use =CopyFormula(Sheet1!C4,"Sheet1","Sheet2")

I am not terribly familiar with the EVALUATE command, so there may be easier ways of doing this. Perhaps you do not need to be quite so explicit with the sheet references.

I just tested it, and it does not appear to require all of that. Instead:

Formula:

Please Login or Register  to view this content.

Then, on Sheet2!C4, you can use =CopyFormula(Sheet1!C4)

Much easier. The only problem is with recalculations. It does not explicitly recalculate when a change is made. So, if you enter that formula and change cell Sheet2!A4, it will not update cell Sheet2!C4 unless you click on the formula in the cell and click enter. I bet there is a way to force a recalculation, but I am not sure how.

Hmmm. I'll play around with this for the next few days. I was hoping to avoid using any macros, as I haven't touched visual basics since my undergrad. But I'm working on a big project that I know will require a lot of macros. There's not a way to use the transpose, inderect, lookup, or something similar functions is there? I might just copy/paste for now, as I'm mostly focusing on the math to research a theory I'm working on than the workbook functions at the moment. Ultimately I will need to have a real-time updating formula. I'm trying to research a lot of different data (separating via different sheets) with common equations that I'll have to modify.