Hi Guys,

I have a sheet "sheet 1" which has data looking like as follows:

Store Number / Periods of year ----- Period 1 Period
2

201 X
W

202 Y
Z


Now, in "sheet 2" , I have two dropdown comboboxs, One to select store
numbers(Say 202) and one to select WEEKS (say week 1 or week 2 ....Not
Periods) of the year (Say week 2).

Now I need to put this logic somewhere , whereby say week 1 to week 4 belong
to period 1.

So when I select store 202 from first combobox and week 2 from second
combobox, I should get a result (in cell or textbox) of "Y" as week 2 is in
period 1.

How do I do this....? I would prefer not to alter data layout of sheet 1.

I will happy with both programming or simple excel lookup solution
suggestions.

Please help..

Veeraan

Hi,

- Set the LinkCell of the 2 comboboxes to 2 cells:
a. If a combobx from the Control Toolbox toolbar, then Right-click the
combo, choose Properties from the popup menu, then set the LinkedCEll
property to, say, Sheet2!A1 fot the store combo, and say, Sheet2!A2 fo the
week combo.
b. if you are using a Combobox from the Forms toolbar, it is a similar
setup.
This way, everytime the user changes choice, it is automatically reflected
to these 2 cells.

- Now, assuming the table is in Sheet1!A1:M50, in a cell of sheet 2, enter:
=INDEX(Sheet1!A1:M50
,MATCH(A1,Sheet1!A1:A50,0)
,MATCH(A2,Sheet1!A1:M1,0))

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"veeraan" wrote:

Hi Guys,

I have a sheet "sheet 1" which has data looking like as follows:

Store Number / Periods of year ----- Period 1 Period
2

201 X
W

202 Y
Z


Now, in "sheet 2" , I have two dropdown comboboxs, One to select store
numbers(Say 202) and one to select WEEKS (say week 1 or week 2 ....Not
Periods) of the year (Say week 2).

Now I need to put this logic somewhere , whereby say week 1 to week 4 belong
to period 1.

So when I select store 202 from first combobox and week 2 from second
combobox, I should get a result (in cell or textbox) of "Y" as week 2 is in
period 1.

How do I do this....? I would prefer not to alter data layout of sheet 1.

I will happy with both programming or simple excel lookup solution
suggestions.

Please help..

Veeraan