IF statement

[gbeard]

I have 6 different sheets in my workbook 1 for each of my 5 different
districts in my company and one main sheet. Is it possible to put the
district number in a cell on the main sheet and then reference that cell in
an IF statement to pull data from a particular district? By changing the
value in that single cell, I'd be able to find the same data from each of my
districts.
BTW, I can't use a VLOOKUP or HLOOKUP since the data I am trying to pull is
not in numerical order.

Any help or ideas would be appreciated,
Gary

 

[David]

Hi Gary,

To help you I think someone would have to look at your workbook and the
sheets, then you would have to explain exeactly what you want pulled from the
sheet or sheets. Your question is too general in nature, if that makes sense.

Thanks,
David

 

[RW]

Gary,

Yes, it is possible.

Say your districts are A, B, C, D, E, their corresponding worksheets are
named likewise and B3 of each sheet contains the population.

On your main sheet, A1 contains the district.

To get the population (in say A3 of your main sheet) of the whatever
district is specified in A1, you need to put the following formula in A3:

=IF(A1="A",A!B3,IF(A1="B",B!B3,IF(A1="C",C!B3,IF(A1="D",D!B3,IF(A1="E",E!B3)))))

Hope this helps,
Rosemary

 

[macropod]

Hi Gary,

You can use the INDIRECT worksheet function for this.

Cheers

 

[Ron Rosenfeld]

I have 6 different sheets in my workbook 1 for each of my 5 different
districts in my company and one main sheet. Is it possible to put the
district number in a cell on the main sheet and then reference that cell in
an IF statement to pull data from a particular district? By changing the
value in that single cell, I'd be able to find the same data from each of my
districts.
BTW, I can't use a VLOOKUP or HLOOKUP since the data I am trying to pull is
not in numerical order.

Any help or ideas would be appreciated,
Gary

There are many ways of doing this. But where is the data on the district
sheet?

By the way, if the data you are pulling is in a table, if you can accept only
exact matches, there is no requirement that the data be sorted to use HLOOKUP
or VLOOKUP. You can use FALSE for the optional range_lookup argument (see
HELP).

So if you want to do a lookup in a table, and the sheet of the table depends on
the contents of A1, and the data table is in D1:I30 on each sheet, then
something like:

=VLOOKUP(A3,INDIRECT(A1&"!"&"D1:I30"),2,FALSE)

would do a lookup using the contents of A3 as the lookup_value; the contents of
A1 to define the worksheet; and would return the match in column 2 of the
table.




--ron