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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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...
 

Forum statistics

Threads
1,144,275
Messages
5,723,448
Members
422,497
Latest member
dougy99

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
Top