Lookup, index, match, range, oh my...

[kenbquik]

I'm a newbie to using this discussuion board. so, I'm not sure if I posted
this correctly. But, I can really use some help.


Here's what I've got:
sheet#1 (data input below)
Fresno
A B C D E
1 Fruit Jan'09 Feb '09 Mar '09 Apr '09
2 pear 23 31 12 0
3 cherry 15 3 0 0
4 Apple 12 7 0 0

Sacramento
A B C D E
7 Fruit Jan'09 Feb '09 Mar '09 Apr '09
8 pear 2 1 10 0
9 cherry 10 3 0 0
10 Apple 3 4 0 0

Total California (sum of Fresno and Sacramento)
A B C D E
12 Fruit Jan'09 Feb '09 Mar '09 Apr '09
13 pear 25 32 22 0
14 cherry 25 6 0 0
15 Apple 15 11 0 0

Here’s what I get in the table:
sheet #2 (table result)
=LOOKUP(1E+100,1/('sheet1'!B13:E13),'sheet1'!B13:E13)
=LOOKUP(1E+100,1/('sheet1'!B14:E14),'sheet1'!B14:E14)
=LOOKUP(1E+100,1/('sheet1'!B15:E15),'sheet1'!B15:E15)

Total Jan'09 Feb '09 Mar '09
pear 25 32 22
cherry 25 6 6
Apple 15 11 11

Here’s what I want in the table
total Jan'09 Feb '09 Mar '09
pear 25 32 22
cherry 25 6 0
Apple 15 11 0

How can I get above?

I know that probably I'll have to do a offset formula to so the table will
know how to pick up the previous months (Jan & Feb) ---- Which I can't
figure out as well!

I'm confused.... I've read so much, I don't know what to do. like a lookup,
index, offset, name range.


Please guide me to the best answer. Any help appreciated!

 

[T. Valko]

I don't know why you're using these formulas:

=LOOKUP(1E+100,1/('sheet1'!B13:E13),'sheet1'!B13:E13)

They return the rightmost non-zero number from the referenced range.

Why don't you just use direct links?

='sheet'!B13

Is that you're real sheet name?

Copy across then down as needed.