referencing a sheet named in a cell then using data from that sheet
 

I have on my main worksheet information that I am trying to use in formulas
to reference data on other worksheets. My worksheets are named for the
different counties I do business in. My columns on those sheets are as
follows: A are my vendor names, B-E are prices for different quantities,
and F-I are different shipping prices based on priority. On my main
worksheet I would like to put in cell Z1 the name of the county (sheet) I
want to reference, Z2 the quantity I need to purchase, Z3 the priority of
the shipping (overnight, 2-day, parcel post, etc.). Then based on cells Z1
through Z3 I want column A of the main sheet to return the name of the
vendor whose pricing is less than Z2+Z3. In column B of the main sheet I
want the price of the vendor in column A.
So, if I'm sitting with a client, I want to enter the county we're in, the
price the client is willing to pay and how fast he needs the product, and I
want the data show up in columns A and B of the main Sheet.
Bare in mind, the price in Z2 must be within the threshold of column B to
use column B pricing. If not, it must be within the threshold of column C
to use column C pricing and so on. Same thing with the shipping.

Is this possible?

Thanks for any help,
Gary

Read Help about INDIRECT

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"gbeard" wrote in message
m...
I have on my main worksheet information that I am trying to use in formulas
to reference data on other worksheets. My worksheets are named for the
different counties I do business in. My columns on those sheets are as
follows: A are my vendor names, B-E are prices for different quantities,
and F-I are different shipping prices based on priority. On my main
worksheet I would like to put in cell Z1 the name of the county (sheet) I
want to reference, Z2 the quantity I need to purchase, Z3 the priority of
the shipping (overnight, 2-day, parcel post, etc.). Then based on cells Z1
through Z3 I want column A of the main sheet to return the name of the
vendor whose pricing is less than Z2+Z3. In column B of the main sheet I
want the price of the vendor in column A.
So, if I'm sitting with a client, I want to enter the county we're in, the
price the client is willing to pay and how fast he needs the product, and
I want the data show up in columns A and B of the main Sheet.
Bare in mind, the price in Z2 must be within the threshold of column B to
use column B pricing. If not, it must be within the threshold of column C
to use column C pricing and so on. Same thing with the shipping.

Is this possible?

Thanks for any help,
Gary

Bernard,
I'm familiar with INDIRECT, but am not adept enough to get it to work. I
can reference data from the other worksheets and bring that data to the main
sheet, but I'm having trouble getting the data from a row without hard
coding the row into the formula.

Thanks,
Gary

Try:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))
+1,255)

-----Original Message-----
Bernard,
I'm familiar with INDIRECT, but am not adept enough to
get it to work. I
can reference data from the other worksheets and bring
that data to the main
sheet, but I'm having trouble getting the data from a row
without hard
coding the row into the formula.

Thanks,
Gary


.

Thanks for the suggestion...I'll give it a try.

--
Gary Beard