Help writing VB in a Budget Spreadsheet to add or subtract amount stated on Master from individual Budget Worksheets...

[Gingerbikki]

Hi everyone!

I'm trying to make improvements to a Budget Sheet I use at work, to automate processes that my colleagues currently do manually. I'm sure there must be a way to do this using VB but alas my knowledge of VB is still too limited to know where to start!

The Budget Sheet currently has 6 worksheets: Master, Budget 1, Budget 2, Budget 3, Budget 4 & Budget 5. The way we currently use the Spreadsheet is to enter the details of expenditure onto the master sheet as you would expect, then in the final 2 columns (J & K) we enter the amount £, and the cell reference of which budget we have added this to (e.g. Budget 1, G10)

Then we go into the relevant Budget worksheet and cell referenced before, and we add the amount (rounded up to the nearest £) on to the end of a long formula that looks something like =34+1154+1267.

I'm sure there must be a way to automate this process, so that we update the master and the rest happens automatically, but I'm not sure where to start or even if VB is right for the job? I looked at Formulas but couldnt find one to do everything for me...

If anyone has any advice or suggestions I will be very very grateful!

Thanks

Kelly

 

[Comfy]

Hi Kelly,

Welcome to the board.

Without knowing the exact layout of your workbook I have just made some assumptions. This can be done with a SUMIF and some text manipulation.

My "Master" Sheet looks like this:

Excel 2010

	J	K
1	Amount	Budget
2	5000	Budget 1, G10
3	25000	Budget 1, G11
4
5
6
7	1500	Budget 1, G10
8
9
10
11
12
13
14	2000	Budget 1, G10

<tbody>
</tbody>

Master​

With the Budget 1 sheet looking like this:
Excel 2010

	G
6	0
7	0
8	0
9	0
10	8500
11	25000
12	0
13	0
14	0
15	0

<tbody>
</tbody>

Budget 1​

Worksheet Formulas Cell Formula G6 =SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&", "&ADDRESS(ROW(),COLUMN(),4),Master!J:J) G7 =SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&", "&ADDRESS(ROW(),COLUMN(),4),Master!J:J) G8 =SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&", "&ADDRESS(ROW(),COLUMN(),4),Master!J:J) G9 =SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&", "&ADDRESS(ROW(),COLUMN(),4),Master!J:J) G10 =SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&", "&ADDRESS(ROW(),COLUMN(),4),Master!J:J) G11 =SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&", "&ADDRESS(ROW(),COLUMN(),4),Master!J:J) G12 =SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&", "&ADDRESS(ROW(),COLUMN(),4),Master!J:J) G13 =SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&", "&ADDRESS(ROW(),COLUMN(),4),Master!J:J) G14 =SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&", "&ADDRESS(ROW(),COLUMN(),4),Master!J:J) G15 =SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&", "&ADDRESS(ROW(),COLUMN(),4),Master!J:J) <tbody> </tbody>

<tbody>
</tbody>

The formula takes the sheet name and adds it to the cell address separated by a comma. It then looks for this value in column K of the master sheet and sums everything in column J that match.
Let me know if it's close to what you are after.

 

[Gingerbikki]

Thanks for the reply and the welcome!

Here's some snippets of what my spreadsheet looks like...I wasn't sure how to insert images into the text so I hope this works...


Master looks like:</SPAN></SPAN>

	B</SPAN>	C</SPAN>	D</SPAN>	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>	I</SPAN>	J</SPAN>	K</SPAN>
</SPAN>	Date</SPAN>	Cost Centre</SPAN>	Vendor</SPAN>	Category</SPAN>	Sub-Category</SPAN>	Description</SPAN>	PO No.</SPAN>	Invoice No.</SPAN>	Total £GBP</SPAN>	Cell Ref</SPAN>
3</SPAN>	28/8/12</SPAN>	68ANSY00</SPAN>	Vendor 48</SPAN>	Equipment</SPAN>	Equipment 9</SPAN>	Blah Blah Blah</SPAN>	0000000000</SPAN>	INV00001</SPAN>	£11.03</SPAN>	Budget 1 G3</SPAN>
4</SPAN>	11/8/12</SPAN>	68ANSY00</SPAN>	Vendor 34</SPAN>	Equipment</SPAN>	Stationary 1</SPAN>	Blah Blah Blah</SPAN>	0000000001</SPAN>	INV00002</SPAN>	£42.65</SPAN>	Budget 2 G7</SPAN>
5</SPAN>	16/8/12</SPAN>	68ANSY00</SPAN>	Vendor 22</SPAN>	Equipment</SPAN>	Equipment 1</SPAN>	Blah Blah Blah</SPAN>	0000000002</SPAN>	INV00003</SPAN>	£35.97</SPAN>	Budget 1 G6</SPAN>
6</SPAN>	3/8/12</SPAN>	68ANSY05</SPAN>	Vendor 38</SPAN>	Equipment</SPAN>	Stationary 3</SPAN>	Blah Blah Blah</SPAN>	0000000003</SPAN>	INV00004</SPAN>	£340.00</SPAN>	Budget 3 G9</SPAN>
7</SPAN>	10/8/12</SPAN>	68ANSY00</SPAN>	Vendor 45</SPAN>	Equipment</SPAN>	Equipment 8</SPAN>	Blah Blah Blah</SPAN>	0000000004</SPAN>	INV00005</SPAN>	£7,739.70</SPAN>	Budget 4 G20</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

And the Budget worksheets look like: (this is a sample from Budget 1)</SPAN></SPAN>

	E</SPAN>	F</SPAN>	G</SPAN>	H</SPAN>
</SPAN>	Staff related expenses</SPAN>	Budget</SPAN>	Actual Spend</SPAN>	Remaining</SPAN>
3</SPAN>	Expense 1</SPAN>	10,000</SPAN>	5695</SPAN>	4,305</SPAN>
4</SPAN>	Expense 2</SPAN>	10,000</SPAN>	1036</SPAN>	8,964</SPAN>
5</SPAN>	Expense 3</SPAN>	10,000</SPAN>		10,000</SPAN>
6</SPAN>	Total</SPAN>	30,000</SPAN>	6731</SPAN>	23,269</SPAN>
7</SPAN>
8</SPAN>	Catering recharges</SPAN>	10,000</SPAN>		10,000</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>

So basically the amount from column J in Master needs to be added to the cell and budget sheet referenced in column K.</SPAN>


I tried the SUMIF you gave me above but couldnt get it to work...What are your thoughts?


Thank youuuu!</SPAN>

 

[Comfy]

That's because your example included a comma

(e.g. Budget 1, G10)l

Try this:

=SUMIF(Master!K:K,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,20)&" "&ADDRESS(ROW(),COLUMN(),4),Master!J:J)

The original was looking for the text "Budget 1, G1" etc
I have changed the formula to remove the comma.

Edit:

If you don't already have it in the workbook I would suggest that you data validation in Column K so that you can only select from a drop down of valid Cell References.
That way you know that the correct one will be entered and therefore will show up in the formula on each budget sheet.