Hi
For the benefit of other respondents and the archives :-
I received a Workbook form Nicole with a subset of her data on a Sheet
called Data.
Nicole has a sheet called Summary Report, with a Combobox dropdown on cell
B4 liked to cell C4
In cells B7:B13 there were names of Salespersons.
The Data sheet contained Product in column A, Salesperson in B and Months
Jan through Nov in C:M
These are the notes I sent back to Nicole with the workbook
to explain the formula I created in cell C8 of her Summary Report
=SUMIF(Salesperson,$B7,Usedata)
I moved your Months lists to a sheet called Setup, as normally your data
will be extending down the sheet called Data.
I also got rid of row 1 (month numbers not necessary) and row 3 (blank row).
I set up some named ranges
lrow = COUNTA(Data!$B$B) as this gives us a count of how many rows of
data we have including the header).
data = Data!$C$2:INDEX(Data!$C:$C,lrow)
This gives the data for the first Month. It is a Dynamic formula, so the
range will grow as more lines are added, and lrow gets increased.
offset = 'Summary Report'!$C$4 just a name for the linked cell to your
dropdown, which gives us the Month number that has been selected.
usedata = OFFSET(data,,offset-1) this will offset the column of data
selected by data, by the number of columns as per the offset value minus 1,
as when the Month is January (1) we don't want an offset, so it must always
be 1 less.
(Alternatively, you could make Offset = $C$4-1)
salesperson = Data!$B$2:INDEX(Data!$B$B,lrow) this gives just the list of
Salesperson names for use in the Sumif formula.
months = Setup!$B$1:$B$12 just a list of the full month names. I added
the year on the end, so you don't get varying spaces between the length of
the month name and your 2008 which was in a separate cell.
Choosing the long month name is now easy. We know the Index number from C4
so INDEX(Months,C4) gives us the value for the heading.
