Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 13th 05, 06:20 PM
gbeard
 
Posts: n/a
Default 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



  #2   Report Post  
Old April 13th 05, 06:53 PM
Bernard Liengme
 
Posts: n/a
Default

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



  #3   Report Post  
Old April 13th 05, 08:04 PM
gbeard
 
Posts: n/a
Default

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


  #4   Report Post  
Old April 14th 05, 09:04 PM
 
Posts: n/a
Default

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


.

  #5   Report Post  
Old April 15th 05, 08:42 AM
gbeard
 
Posts: n/a
Default

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

--
Gary Beard




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I turn off relative cell referencing in excel? Glenn New Users to Excel 1 April 13th 05 03:06 AM
Sheet Modified date in a cell.. ? n666 Excel Worksheet Functions 0 February 16th 05 01:32 AM
Need formula for sheet & cell reference MPH Excel Worksheet Functions 0 January 16th 05 03:39 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM
Naming & renaming a sheet tab Cgbilliar Excel Worksheet Functions 1 November 7th 04 05:57 PM


All times are GMT +1. The time now is 09:18 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017