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.
Bookmarks