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" <gbeard12@sbcglobal.net> wrote in message
news:Nxc7e.1248$J12.1078@newssvr14.news.prodigy.com...
>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