Showing the Formula Based on Values from Different Worksheets

[gall3on]

I'm trying to create a macro that will perform calculations for an invoice. I know that I can do much of the calculations in the back end within a script, but I'm writing the macro for my boss who would be interested in seeing the formulas in the Excel Spreadsheet.

Here's my main problem. For several formulas in my summary worksheet, I'm either adding or multiplying for expenses.

Sample for Cell A2 in my Summary Worksheet:

Expense = sum(CP!G60 * 1.025 - HS!A5 - CP!G69)

where G60 refers to 'Estimate' & G69 refers to 'Adjusted Amount' from worksheet CP, and A5 refers to 'Losses' from worksheet HS.

What makes this difficult is that the values that show up on'G60', 'G69', and 'A5' are not absolute locations. They can be dynamic based on other things happening on their respective worksheets (ie for November, they're 'G70', 'G70', and 'A15' respectively)

Someone suggested naming ranges, but I don't know how to name a range when the cell is dynamic.

If range is the way to go, at least the formula can look like this:

=sum(Estimate*1.025 - Adjusted Amount - Losses)

Maybe I'm making this overcomplicated. I've been working on this all day and it's driving me crazy. Any help would be much appreciated.

 

[SkipVought]

Hi,

You have really not adequately described the source data.

Is the source data a TABLE? You might just be able to do this on the sheet with lookup functions, not VBA.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[gall3on]

Nevermind. I've figured it out. I realized that you can set the formula on the backend using Row and Column references.

IE:
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"

Thanks though!

 

[Guest_imported]

I am having somewhat the same problem. I want to be able to capture the row and column names from a field use it in a formula and make sure that it can be autofilled relative instead of absolute. If I use activecell.address I always get the absolute reference of the cell. If I use The
R[ ] C[ ] method I get a relative formula based on the cell that houses the formula. I need the cells to be relative. Any suggestions?

Thanks,

Rob

 

[SkipVought]

Rob,

Please post your question in a new thread.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}