Help with Program

[juanpablo]

Hi all,

I have the following problem.

% Lan %Lan % Lan % Aqua % Aqua % Aqua
W MIA LAX JFK MIA LAX JFK
1 45 85 90 89 12 45
2 90 45 100 85 10 10
3 78 52 54 96 78 10
4 65 78 74 41 50 25
5 12 21 63 74 52 46
6 74 10 12 23 90 74

So in other sheet, I want to have a menu with two options,
Select W(week#) and destination(MIA-LAX-JFK).
When you select week 1 for example, it should display all the information
related to week 1 depending the destination, if MIA is selected, then show
%LAN MIA and %Aqua MIA.

How can this be done???

JP

 

[Max]

Here's one design ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3f8ig
Enquiry sys design.xls

Source data as posted assumed in sheet: x, cols A to G

In another sheet: Enquiry,
A2 contains the destination DV, eg: JFK
Labels placed in D2:E2 : % Lan, % Aqua

Select D38 (D3 active), paste the below into the formula bar, then press
CTRL+SHIFT+ENTER to confirm the formula (multi-cell array-enter):
=OFFSET(x!$A$3,,MATCH(1,(x!1:1=D3)*(x!2:2=A2),0)-1,6)

Repeat to array-enter into E3:E8
=OFFSET(x!$A$3,,MATCH(1,(x!1:1=E2)*(x!2:2=A2),0)-1,6)

(C3:C8 would simply contain the week# labels:1-6,
with autofilter applied on C2 for selection of week#, as desired)

Selection of the destination in A2 will retrieve the corresponding cols of
info from x

 

[juanpablo]

Perfect.

Thanks so much.

Here's one design ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3f8ig
Enquiry sys design.xls

Source data as posted assumed in sheet: x, cols A to G

In another sheet: Enquiry,
A2 contains the destination DV, eg: JFK
Labels placed in D2:E2 : % Lan, % Aqua

Select D38 (D3 active), paste the below into the formula bar, then press
CTRL+SHIFT+ENTER to confirm the formula (multi-cell array-enter):
=OFFSET(x!$A$3,,MATCH(1,(x!1:1=D3)*(x!2:2=A2),0)-1,6)

Repeat to array-enter into E3:E8
=OFFSET(x!$A$3,,MATCH(1,(x!1:1=E2)*(x!2:2=A2),0)-1,6)

(C3:C8 would simply contain the week# labels:1-6,
with autofilter applied on C2 for selection of week#, as desired)

Selection of the destination in A2 will retrieve the corresponding cols of
info from x

 

[Max]

Welcome. Glad to hear that.