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

Hi Gary

If I understand your question properly, you could you the "Indirect function"

eg "=INDIRECT(CONCATENATE("Sheet",C3,"!a1"),TRUE) "

This formula will return the value in A1 on the sheet that you choose (1, 2,
3, ...n) in cell C3, where the sheet names are Sheet1, Sheet2, Sheet3,
....Sheenn

you could ammend this formula slightly to match your sheet names and the
cell references that you want to return

hope this helps

Nick

"gbeard" wrote:

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

Nick,
That worked great! Thanks for the help!
Can I combine that with an IF function? What I'm trying to do is gather two
pieces of data from the sheet that the indirect function locates. I need it
to add these two pieces of data and if they are less than a number in a
given cell it returns the vendor represented by that data.
I've basically got sheet1 as my main sheet. I enter two pieces of
information on sheet1 to match in the data on the other sheets in my
workbook. My sheets are the districts in my company. I need to enter which
district I want it to search, then I want it to add two cells (which are the
prices of each piece of a widget) for each vendor on the specified sheet and
return a list of vendors offering the widget for the price I want to pay.

Any ideas how I can do this?
Gary

Hi Gary

I have a couple of ideas about how to do this but it is quite difficult to
explain in this type of forum. If you like, you could email me the excel
file and I will send you some ideas.

Cheers

Nick


"gbeard" wrote:

Nick,
That worked great! Thanks for the help!
Can I combine that with an IF function? What I'm trying to do is gather two
pieces of data from the sheet that the indirect function locates. I need it
to add these two pieces of data and if they are less than a number in a
given cell it returns the vendor represented by that data.
I've basically got sheet1 as my main sheet. I enter two pieces of
information on sheet1 to match in the data on the other sheets in my
workbook. My sheets are the districts in my company. I need to enter which
district I want it to search, then I want it to add two cells (which are the
prices of each piece of a widget) for each vendor on the specified sheet and
return a list of vendors offering the widget for the price I want to pay.

Any ideas how I can do this?
Gary