E
EZ
Hi All,
I have 3 sheets in my workbook: 'Data' contains raw data coming from Access,
'ByLocation' lookup the actual data by location from the 'Data' sheet, and
finally the 'Model' sheet which sums all the locations data as one number.
The data arranged by year/month/week/location/amount (LBs). I have a
sumproduct in the 'ByLocation' sheet that matches the Yr/Month/Wk/Loc_Code in
the sheet with the 'Data' sheet and brings in the actual LBs. I also have
another sumproduct formula with an IF statement that sums all the actuals
from all locations together... if there's no actuals then I use prior year
data (already in the 'Model' sheet) and multiply that by a certain rate% to
fill in the forecast data. Everythings so far works fine.
My users would like to have a dropdown boxes in the 'model' sheet where they
can select the month/Wk to bring in actual data. Currently if I have actual
data through month 11, wk 4 (11x4) the model sheet will show that data along
with forecasted data for month 12, but if we needed to see our forecasted
data through 9x4, we have to delete the actual data for month 10&11 from the
'Data' sheet. How can I incorporate the dropdown boxes along with my
sumproduct fromula to give the users the flexibility to show actual data up
to a certain point regardless of what the 'Data' sheet contains, or without
having to delete any data?
Sample formuls:
=IF(SUM(ByLoc!L10,ByLoc!L140,ByLoc!L205)>1,SUM(ByLoc!L10,ByLoc!L140,ByLoc!L205,ByLoc!L335,ByLoc!L400,ByLoc!L465,ByLoc!L530,ByLoc!L595,ByLoc!L725,ByLoc!L790,ByLoc
!L855,ByLoc!L985,ByLoc!L1050,ByLoc!L1115,ByLoc!L1180,ByLoc!L1245,ByLoc!L1440,ByLoc!L1505,ByLoc!L1570),M11*VLOOKUP(A11,$C J$11:$CM$23,2,FALSE))
.....>1 = if there's actual data in the 'ByLoc' sheet
M11= prior year LBs for that week
VLOOKUP... = %rate
Thanks.
I have 3 sheets in my workbook: 'Data' contains raw data coming from Access,
'ByLocation' lookup the actual data by location from the 'Data' sheet, and
finally the 'Model' sheet which sums all the locations data as one number.
The data arranged by year/month/week/location/amount (LBs). I have a
sumproduct in the 'ByLocation' sheet that matches the Yr/Month/Wk/Loc_Code in
the sheet with the 'Data' sheet and brings in the actual LBs. I also have
another sumproduct formula with an IF statement that sums all the actuals
from all locations together... if there's no actuals then I use prior year
data (already in the 'Model' sheet) and multiply that by a certain rate% to
fill in the forecast data. Everythings so far works fine.
My users would like to have a dropdown boxes in the 'model' sheet where they
can select the month/Wk to bring in actual data. Currently if I have actual
data through month 11, wk 4 (11x4) the model sheet will show that data along
with forecasted data for month 12, but if we needed to see our forecasted
data through 9x4, we have to delete the actual data for month 10&11 from the
'Data' sheet. How can I incorporate the dropdown boxes along with my
sumproduct fromula to give the users the flexibility to show actual data up
to a certain point regardless of what the 'Data' sheet contains, or without
having to delete any data?
Sample formuls:
=IF(SUM(ByLoc!L10,ByLoc!L140,ByLoc!L205)>1,SUM(ByLoc!L10,ByLoc!L140,ByLoc!L205,ByLoc!L335,ByLoc!L400,ByLoc!L465,ByLoc!L530,ByLoc!L595,ByLoc!L725,ByLoc!L790,ByLoc
!L855,ByLoc!L985,ByLoc!L1050,ByLoc!L1115,ByLoc!L1180,ByLoc!L1245,ByLoc!L1440,ByLoc!L1505,ByLoc!L1570),M11*VLOOKUP(A11,$C J$11:$CM$23,2,FALSE))
.....>1 = if there's actual data in the 'ByLoc' sheet
M11= prior year LBs for that week
VLOOKUP... = %rate
Thanks.