Dynamically Updating Funtion/Formula

[MrCD]

Hey Guys,

Hopefully I am posting this in the right place, and am phrasing my question as clearly as possible.

What I need to do is basically use one sheet from my workbook as a 'template' for all the other sheets, but in a way that when I make a change to a formula on my 'Template' sheet all the other sheets based off of it follow suit. I need to do this because for 100000's of rows, spread across different sheets, I have product information populating Columns A-G, and their unique cost in Column H. I need to apply markups to each product (row), but all of these markups are standard across every product and thus every sheet. So in the perfect world I would be able to edit the formula on Sheet #1, and then have that change be reflected in every other sheet.

So let's say I have something like this on Sheet #1...

http://imgur.com/k1YFrPD (sorry wouldn't let me embed the image)

Where Column C is a product of A & B...ie. "=A2*B2"

What I need to happen is on Sheet #2 is for it to somehow pull the same formula that is on Sheet #1 in Column C, but apply that formula to 'A2' & 'B2' on sheet #2. And I also want it to update whenever I change the formula in Sheet #1.

If it requires that I set up a template on a completely different workbook, that is perfectly fine as Sheet #1, my 'Template' Sheet, does not hold in actual product information.

Thank You


***Please let me know if you guys need more information/pictures, it is really important that I figure this out***

[hoyasaxa215]

You can edit multiple sheets simultaneously. Hold down the 'ctrl' button and select all the sheets you'd like to edit, which is called "grouping" worksheets. Once you've grouped the worksheets, any changes you make in your current worksheet will be made to every worksheet that has been grouped together. So, typing "=A2&B2" in Cell C1 of Sheet 1 will actually enter the equation into cell C1 of every grouped worksheet. Is that what you're trying to accomplish?

[Jack7774]

I can help however you will have to upload a workbook with at least 3 sheets. One sheet being the one you want to modify that adjusts all the other sheets. The other two sheets with some fake data if its sensitive. Make sure they have at least 5-10 rows of data for testing out what you want and so I can write the macro. Also please include what you want the desired results to look like because it saves a lot of typing in the long run (hopefully).

[Jack7774]

You can edit multiple sheets simultaneously. Hold down the 'ctrl' button and select all the sheets you'd like to edit, which is called "grouping" worksheets. Once you've grouped the worksheets, any changes you make in your current worksheet will be made to every worksheet that has been grouped together. So, typing "=A2&B2" in Cell C1 of Sheet 1 will actually enter the equation into cell C1 of every grouped worksheet. Is that what you're trying to accomplish?

Great idea. I learned something new today.

[ben_hensel]

Sounds like a job for User Defined Functions.

What that will do is, instead of creating the "model" function in a sheet, it will live in a VBA module.

So for example you would create a function in VBA like this:

Please Login or Register  to view this content.

Then actually in the worksheet:

Please Login or Register  to view this content.

So whenever you change the function you defined, it will change how it is calculated in the worksheet.

...Be aware that VBA is slower than the native functions, so your spreadsheet could get slow if you do a lot of this.

[MrCD]

Ya that's basically exactly what I'm trying to accomplish, and is how I am currently doing it, but it would be much more efficient if I could do it without having to select all of the sheets. The reason I don't want to have to select all of the sheets each time I have to update the formula is because not necessarily every sheet within the workbook gets the update (meaning I can't simply use the 'select all sheets' button), and also the project I'm working on will eventually have to be spread across multiple workbooks (each with its own 'Template Sheet').

I was looking into use the indirect function, but am not completely sure how to use it, do you know if that could work?

[MrCD]

That would be amazing, but I would have to wait until Monday to check with the boss-man and make sure it's okay to share the info, as it could be considered sensitive.

Do you know if (and if so how) it would be able to accomplish this using basic excel functions? I was looking into the 'indirect' function and it seemed promising, but I couldn't get it to work.

[ben_hensel]

I was looking into use the indirect function, but am not completely sure how to use it, do you know if that could work?

As a rule INDIRECT makes it harder to keep funtions updated, not easier, so my instint is that it will actually make things worse for you, not better.

Anyway what that one does is change which cells the function is looking at, but not how the function operates, so it's probably a dead-end for you if I understand your need.

[Jack7774]

I can help however you will have to upload a workbook with at least 3 sheets. One sheet being the one you want to modify that adjusts all the other sheets. The other two sheets with some fake data if its sensitive. Make sure they have at least 5-10 rows of data for testing out what you want and so I can write the macro. Also please include what you want the desired results to look like because it saves a lot of typing in the long run (hopefully).

I'm still up for attempting to write a macro to accomplish the desire if you want me to give it a try.

[globalsourcing]

hi Mrcd

try this code
Sub dural()
Dim r As Range, ady As String
For Each r In Sheets("Sheet1").Cells.SpecialCells(xlCellTypeFormulas)
ady = r.Address
r.Copy Sheets("Sheet2").Range(ady)

r.Copy Sheets("Sheet3").Range(ady)

r.Copy Sheets("Sheet4").Range(ady)

r.Copy Sheets("Sheet5").Range(ady)
Next1
End Sub

run this code whenever u change farmula in sheet 1 same wil be reflected to all sheet.
if u dont want to update specific sheet for e.g. sheet 5 in above code then delete r.Copy Sheets("Sheet5").Range(ady) this line

[MrCD]

As a rule INDIRECT makes it harder to keep funtions updated, not easier, so my instint is that it will actually make things worse for you, not better.

Anyway what that one does is change which cells the function is looking at, but not how the function operates, so it's probably a dead-end for you if I understand your need.

Ya, I was getting that feeling after my research didn't lead anywhere. My train of thought was that in my template I would put "A1*B1" without a "=" in C1, then use the indirect function (referencing C1 on Sheet #1) in C1 in the rest of my sheets, but that doesn't seem to work.

[MrCD]

I'm still up for attempting to write a macro to accomplish the desire if you want me to give it a try.

Hey Jesse,

Is it possible for me to update you on Monday about where I am at? I would love your help, but I need to check to see if I can share the info on my project, and I wouldn't want you to spend your time and energy for no reason.

[Jack7774]

Monday it is. I agree with your thought process. Also don't forget to try globalsourcing's post.

[MrCD]

Monday it is. I agree with your thought process. Also don't forget to try globalsourcing's post.

Hey Jesse,
Sorry I forgot to reply to you least monday, we got GlobalSourcing's code to work quite nicely, but thank you for offering to help...it's much appreciated

[MrCD]

hi Mrcd

try this code
Sub dural()
Dim r As Range, ady As String
For Each r In Sheets("Sheet1").Cells.SpecialCells(xlCellTypeFormulas)
ady = r.Address
r.Copy Sheets("Sheet2").Range(ady)

r.Copy Sheets("Sheet3").Range(ady)

r.Copy Sheets("Sheet4").Range(ady)

r.Copy Sheets("Sheet5").Range(ady)
Next1
End Sub

run this code whenever u change farmula in sheet 1 same wil be reflected to all sheet.
if u dont want to update specific sheet for e.g. sheet 5 in above code then delete r.Copy Sheets("Sheet5").Range(ady) this line

This has been working great for us for the past week...thanks so much for posting this code, it saved me so much time and stress I really appreciate it.