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

[gbeard]

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 Liengme]

Read Help about INDIRECT

 

[gbeard]

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

 

[Guest]

Try:

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

 

[gbeard]

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