Variance spreadhseet

Smithat Crown

New Member
Joined
Oct 10, 2002
Messages
1
I need to develop a variance spreadsheet that list sales production by month by item as follows: The user will type in the month "January" and based on that(budgeted sales and actual sales---both listed by item) will pull from other areas in the workbook and match the item number and give the budgeted sales in one col and forecasted sales in another per item for that month requested. The spreadsheets have production part numbers by row and the columns are sales by month.

Thanks

I just want the spreadsheet to be easy...type in one word and get the results.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
sounds very straight forward - you simply need to name the 2 areas of data - i.e. the budget and the actual sales (e.g. BUDGET and ACTUAL - and ensure that the preceding column of data to the numbers are the codes).

Then you will do a VLOOKUP - say A2 is code 10

ColB is Budget

b2 = vlookup(a2,budget,2,false)

ColC is Actual Sales

c2 = vlookup(a2,actual,2,false)

If you want to do this for more than one month then you will need to use an offset within the vlookup to get the correct column for that month..

e.g. a table that shows which column number relates to which month - this would be named (eg MONTHOFFSET)

Jan = 2
Feb = 3
Mar = 4

then adapting the earlier code, say you want to pick up the numbers for month Mar - which is in cell b1...

b2 = vlookup(a2,budget,vlookup(b1,monthoffset,2,false),false)

so this would result in the budget vlookup looking in column 4 (Mar in MONTHOFFSET)

Hope this makes sense...
 
Upvote 0

Forum statistics

Threads
1,218,836
Messages
6,144,752
Members
450,567
Latest member
Mplz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top