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!L101,SUM(ByLoc!L10,ByLoc!L75,ByLoc!L14 0....,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.
--
when u change the way u look @ things, the things u look at change.

One thought on your 2nd Q:
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.

Maybe you could try it this way:
=text(index(1),"00")&"x"&index(2)
where the TEXT function is used to format the numeric? return from index(1)
to show the desired leading zero
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---