Dynamic references and changing formulas frustration

[Sim_Tron]

Hi all,

I need your help with a problem I've spent a lifetime trying to work out. I'd be really grateful for any help.

Problem 1

In the attached excel file I have two worksheets; 'Budget form' and 'Budget forecast'.

Attachment 278962

In 'Budget forecast' is a table where I want the sub-totals for each section of the 'Budget form' to be imported into when a user selects a cell in the in the 'Budget Forecast' table and then press a button in the 'Budget form'.

I want to macro to always use the same source data, which are the cells where the amount for the subtotals lie the 'Budget sheet', which are:

Food and Catering: =D17
Room Hire: =D24
Staff Hire: IGNORE AT THIS POINT
Equipment Hire:=D44
Travel:=D52
Accommodation:=D60

The problem is that when I create a dynamic referenced macro, the reference for the subtotal in the 'Budget sheet' changes when i move along the table. So for example, I create the macro in the Food and Catering line under the column titled M1 in the 'Forecast sheet' it works with the formula =D17. But when I run it with in the next column over, M2, it doesn't work and the formula is =E17.

How can I stop this happening? I just want the source formulas to stay static as M columns are created and used as needed.

Problem 2

If you're after a bit of extra credit I also need help with another issue which feeds in from the first problem.

I want the macro for Staff Hire not only to enter the subtotal from the 'Budget form' (as discussed above) but I also want it to create new lines in the M columns depending on which selections have been made in the drop down lists.

So for example, if a user chooses to use a 'Research Director' and a 'Programme Manager' I want the macro to create new lines in the 'Budget forecast' table under 'Staff' saying 'Research Director' and 'Programme Manager' and then inserting the separate totals for each.

I hope this makes sense.

Cheers.

[TMS]

If you create a Named Range, unless you make the range absolute (either rows, or columns, or both), it will self-adjust in relation to where you refer to it. To address this, you would need to make the column and/or row absolute by putting a dollar ($) sign in front of it.

If you're after a bit of extra credit

I'm OK with your undying gratitude for addressing the first question. But thanks anyway.


Regards, TMS

[Sim_Tron]

Thank you so much TMShucks!

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.