help with quote program using muliple prices for one quantity {cross post}
 

Okay,
Forgive me ahead of time, I really don't know much about Excel. I can
work with templates and create simple formulas but alot of what I read
here comes across as "Excelspeak" to me.

Here is what I want to do:

I have a price list sheet created. This price list has cells labled by
name and then "duration", basically for every item there are four
prices, based on duration of a rental or sale. I want to create a quote
program that will pull data from the price list based on the quantities
entered in the quote program.

For example
Price list is "System A, description, $1, $7, $14, $100"
Each of these items divided by commas is a different cell so my plan is
to basically duplicate the price list on another sheet with no prices
displayed but when I ented a number into the respective column for each
duration the program pulls the base data and multiplies it by the number
entered and only displays this number. Then of course there are sums and
sales taxes etc but I think I can do that already.

Sorry, I know I'm not decribing this accurately but I hope you get the
gist of what I am trying to do. I have tried to get "help" within Excel
but, frankly I just don;t follow most of what is said.

Basically what I am trying to do is this: =('Price List'!$F$4)*F4
I know this is circular....I just don't know what to do about it.

Help


--
Tony Canevaro
------------------------------------------------------------------------
Tony Canevaro's Profile: http://www.excelforum.com/member.php...o&userid=28292
View this thread: http://www.excelforum.com/showthread...hreadid=478680

help with quote program using muliple prices for one quantity {cross post}
 

Hi!

Basically what I am trying to do is this: =('Price List'!$F$4)*F4
I know this is circular....I just don't know what to do about it.

What makes you think that's circular?

'Price List'!$F$4 refers to cell F4 on the sheet named Price List and:

*F4 refers to cell F4 of the ACTIVE sheet.

Biff

"Tony Canevaro"
wrote in message
news:Tony.Canevaro.1xe4ib_1130130301.6057@excelfor um-nospam.com...

Okay,
Forgive me ahead of time, I really don't know much about Excel. I can
work with templates and create simple formulas but alot of what I read
here comes across as "Excelspeak" to me.

Here is what I want to do:

I have a price list sheet created. This price list has cells labled by
name and then "duration", basically for every item there are four
prices, based on duration of a rental or sale. I want to create a quote
program that will pull data from the price list based on the quantities
entered in the quote program.

For example
Price list is "System A, description, $1, $7, $14, $100"
Each of these items divided by commas is a different cell so my plan is
to basically duplicate the price list on another sheet with no prices
displayed but when I ented a number into the respective column for each
duration the program pulls the base data and multiplies it by the number
entered and only displays this number. Then of course there are sums and
sales taxes etc but I think I can do that already.

Sorry, I know I'm not decribing this accurately but I hope you get the
gist of what I am trying to do. I have tried to get "help" within Excel
but, frankly I just don;t follow most of what is said.

Basically what I am trying to do is this: =('Price List'!$F$4)*F4
I know this is circular....I just don't know what to do about it.

Help


--
Tony Canevaro
------------------------------------------------------------------------
Tony Canevaro's Profile:
http://www.excelforum.com/member.php...o&userid=28292
View this thread: http://www.excelforum.com/showthread...hreadid=478680

help with quote program using muliple prices for one quantity {cross post}
 

One interp .. Try this sample file which outlines one possible way using
HLOOKUP & MATCH to retrieve the "duration dependent" unit prices for the
particular "system" from a source table set-up in sheet: Price List, and
calcs the required quotes in col E in sheet: Quote

http://www.savefile.com/files/1662287
QuoteProgram_DurationDependent_TonyCanevaro_misc
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Tony Canevaro"
wrote in message
news:Tony.Canevaro.1xe4ib_1130130301.6057@excelfor um-nospam.com...

Okay,
Forgive me ahead of time, I really don't know much about Excel. I can
work with templates and create simple formulas but alot of what I read
here comes across as "Excelspeak" to me.

Here is what I want to do:

I have a price list sheet created. This price list has cells labled by
name and then "duration", basically for every item there are four
prices, based on duration of a rental or sale. I want to create a quote
program that will pull data from the price list based on the quantities
entered in the quote program.

For example
Price list is "System A, description, $1, $7, $14, $100"
Each of these items divided by commas is a different cell so my plan is
to basically duplicate the price list on another sheet with no prices
displayed but when I ented a number into the respective column for each
duration the program pulls the base data and multiplies it by the number
entered and only displays this number. Then of course there are sums and
sales taxes etc but I think I can do that already.

Sorry, I know I'm not decribing this accurately but I hope you get the
gist of what I am trying to do. I have tried to get "help" within Excel
but, frankly I just don;t follow most of what is said.

Basically what I am trying to do is this: =('Price List'!$F$4)*F4
I know this is circular....I just don't know what to do about it.

Help


--
Tony Canevaro
------------------------------------------------------------------------
Tony Canevaro's Profile:
http://www.excelforum.com/member.php...o&userid=28292
View this thread: http://www.excelforum.com/showthread...hreadid=478680

help with quote program using muliple prices for one quantity {cross post}
 

Sorry, this formula is IN F4

Basically I have 4 columns of prcing for each product number. I would
LIKE to be able to enter a quantity in a cell under the prropriate
column and have THAT cell display the result of the formula.


--
Tony Canevaro
------------------------------------------------------------------------
Tony Canevaro's Profile: http://www.excelforum.com/member.php...o&userid=28292
View this thread: http://www.excelforum.com/showthread...hreadid=478680

help with quote program using muliple prices for one quantity {cross post}
 

I feel like I am so close.

Thanks for all of you help so far.
Max, I feel like that should work...but I can't seem to get it right
and get it formated the way i want....

I uploaded a copy of the file I am trying to work with.
Is there something fundamentally wrong with this pricelist that would
cause me to have problems?

If anyone is willing to try and tackle this Iwould be forever in debt.

http://home.armourarchive.org/membe...n/Sample%20XLS/


--
Tony Canevaro
------------------------------------------------------------------------
Tony Canevaro's Profile: http://www.excelforum.com/member.php...o&userid=28292
View this thread: http://www.excelforum.com/showthread...hreadid=478680

help with quote program using muliple prices for one quantity {cross post}
 

Tony, your link is no good.
************
Anne Troy
www.OfficeArticles.com

"Tony Canevaro"
wrote in message
news:Tony.Canevaro.1xez2j_1130169910.0186@excelfor um-nospam.com...

I feel like I am so close.

Thanks for all of you help so far.
Max, I feel like that should work...but I can't seem to get it right
and get it formated the way i want....

I uploaded a copy of the file I am trying to work with.
Is there something fundamentally wrong with this pricelist that would
cause me to have problems?

If anyone is willing to try and tackle this Iwould be forever in debt.

http://home.armourarchive.org/membe...n/Sample%20XLS/


--
Tony Canevaro
------------------------------------------------------------------------
Tony Canevaro's Profile:
http://www.excelforum.com/member.php...o&userid=28292
View this thread: http://www.excelforum.com/showthread...hreadid=478680

help with quote program using muliple prices for one quantity {cross post}
 

'click here' (http://home.armourarchive.org/member.../Sample%20XLS/)


Sorry, don't know how that happened:



Anne, I crossposted here when I saw how much more traffic this forum
received.

Thanks again!


--
Tony Canevaro
------------------------------------------------------------------------
Tony Canevaro's Profile: http://www.excelforum.com/member.php...o&userid=28292
View this thread: http://www.excelforum.com/showthread...hreadid=478680

help with quote program using muliple prices for one quantity {cross post}
 

"Tony Canevaro" wrote:
.. (http://home.armourarchive.org/member.../Sample%20XLS/)

Based on your actual set-up, Tony
here's a sample implementation you might like:
http://www.savefile.com/files/6916100
QuoteProgram_v2_TonyCanevaro_misc.xls

In sheet: Quote
------------------
Col A: Part Number (Select from DV droplist)

Col B: Description (Auto-retrieved, toplines only [for multi-line cases])
Placed in B3, copied down
=IF(A3="","",INDEX('Price List'!B:B,MATCH(A3,'Price List'!A:A,0)))

Col C: Rate (Select from DV droplist)

Col D: Quote (Auto-retrieved)
Placed in D3, copied down:
=IF(OR(A3="",C3=""),"",HLOOKUP(C3,'Price List'!$F$3:$I$100,MATCH(A3,'Price
List'!$A$3:$A$100,0),0))

Adjust the ranges to suit
----------

Defined names (PartNum, Rate) used in DV droplists
(names created via InsertNameDefine)
---------------
PartNum: =OFFSET(Index!$A$1,,,COUNTA(Index!$A:$A))
Rate: =Index!$B$1:$B$4

DV droplists are created in "Quote" via:
Data Validation (Allow: List, Source: =PartNum (for e.g.))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--