Variance spreadhseet

[Smithat Crown]

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.

 

[DonkeyOte]

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