help with Indirect formula

[jaymerozendaal]

Here's what i've got:

Worksheets: AllStores, 100, 101, 102, 103, (goes to 120, these are all
store numbers)

on each store's worksheet (all except the AllStores worksheet) ,
Column A is Quantity, Column B is the type of plant to order: Here's
store 100:

A B

4 QTY Description

5 30 4.5" Cyclamen

6 50 4.5" Kalanchoe

continues down 30 rows. All worksheets are identical.

On the AllStores worksheet. It shows quantities by store by variety:

A B C D
6 Variety 100 101 102 continues
across


7 4.5" Cyclamen 30 xx xx

8 4.5" Kalanchoe 50 xx xx

continues down

The formulas for each column (rows 7-30) is:
B is: =INDEX('100'!A:A,MATCH('All
Stores'!$A7,'100'!B:B,0))

C is =INDEX('101'!A:A,MATCH('All Stores'!
$A8,'101'!B:B,0))

D is =INDEX('102'!A:A,MATCH('All Stores'!
$A8,'102'!B:B,0))

I want to use INDIRECT to reference the store numbers (B6:B20) to look
up the info on each store's worksheet. the formula would change to
=index(indirect(????),match(indirect(????,0)) i think. i just cant
get this to work.l

Please let me know if i need to clarify anything. thanks for your
help!!!

 

[T. Valko]

B is: =INDEX('100'!A:A,MATCH('All Stores'!$A7,'100'!B:B,0))

Try this:

=INDEX(INDIRECT("'"&B$6&"'!A:A"),MATCH($A7,INDIRECT("'"&B$6&"'!B:B"),0))

Biff

 

[Peo Sjoblom]

Try

=INDEX(INDIRECT("'"&B6&"'!A:A"),MATCH('All
Stores'!$A7,INDIRECT("'"&B6&"'!B:B"),0))

 

[jaymerozendaal]

Brilliant, thank you both so much! I almost had it....

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff