S
sahafi
Hi All,
I have 3 worksheets in my file: 'Data' where I download my raw data from
Access, 'ByLoc' I have sumproduct formulas that break down the raw data by
location for 3 years ... current year and past 2years, 'Model' where I sum up
the weekly data by location to a division/National level.
The 'ByLoc' sheet organized where each location will have 52 rows for 52
weeks then the total then a few rows between each location and the other...
so It's not one list. On the 'Model' sheet I have this formula
'=IF(ByLoc!L10>1,SUM(ByLoc!L10,ByLoc!L75,ByLoc!L140....,ByLoc!L1570),P11*VLOOKUP(A11,$CA$11:$CD$23,3,FALSE)).
Basically, if the actual production data is availble sum it up, otherwise
use last year data (P11) then refrence the current period (A11) to find the
appropriate forecast rate and multiply that rate with last year data for that
week. The formula is working fine. But it only checking the first location in
the 'ByLoc' sheet. I need the formula to check all the locations, and if any
one location has actual data for the week then use actuals, but if all
locations have zeros then use forecast. The current formula ignor the fact
that if the first location has been down for whatever reason, while all other
locations have actual data, it will still use forecast instead of actual. How
can make that correction?
Second:
For the user to know what's the latest actual data, I have another formula
on the top of the 'Model' sheet that read the data on the 'Data' sheet and
bring back the last period/week (each period equals 4 weeks) periods on Col B
and wk on Col C. Period data format as: 01, 02, 03, ...13 but the weeks: 1,
2, 3, 4.
The formula:
INDEX(Data!$B$2:$C$3000,COUNTA(Data!A:A)-1,1)&INDEX(Data!$B$2:$C$3000,COUNTA(Data!A:A)-1,2).
For Pd 01 Wk 3 the formula will show: 013
How can I format it to show 01x3 ?
I have tried to concatenate &"X"&INDEX.... but it didn't work.
Any help is greatly appreciated.
Thanks.
I have 3 worksheets in my file: 'Data' where I download my raw data from
Access, 'ByLoc' I have sumproduct formulas that break down the raw data by
location for 3 years ... current year and past 2years, 'Model' where I sum up
the weekly data by location to a division/National level.
The 'ByLoc' sheet organized where each location will have 52 rows for 52
weeks then the total then a few rows between each location and the other...
so It's not one list. On the 'Model' sheet I have this formula
'=IF(ByLoc!L10>1,SUM(ByLoc!L10,ByLoc!L75,ByLoc!L140....,ByLoc!L1570),P11*VLOOKUP(A11,$CA$11:$CD$23,3,FALSE)).
Basically, if the actual production data is availble sum it up, otherwise
use last year data (P11) then refrence the current period (A11) to find the
appropriate forecast rate and multiply that rate with last year data for that
week. The formula is working fine. But it only checking the first location in
the 'ByLoc' sheet. I need the formula to check all the locations, and if any
one location has actual data for the week then use actuals, but if all
locations have zeros then use forecast. The current formula ignor the fact
that if the first location has been down for whatever reason, while all other
locations have actual data, it will still use forecast instead of actual. How
can make that correction?
Second:
For the user to know what's the latest actual data, I have another formula
on the top of the 'Model' sheet that read the data on the 'Data' sheet and
bring back the last period/week (each period equals 4 weeks) periods on Col B
and wk on Col C. Period data format as: 01, 02, 03, ...13 but the weeks: 1,
2, 3, 4.
The formula:
INDEX(Data!$B$2:$C$3000,COUNTA(Data!A:A)-1,1)&INDEX(Data!$B$2:$C$3000,COUNTA(Data!A:A)-1,2).
For Pd 01 Wk 3 the formula will show: 013
How can I format it to show 01x3 ?
I have tried to concatenate &"X"&INDEX.... but it didn't work.
Any help is greatly appreciated.
Thanks.