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

[Tony Canevaro]

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

 

[Biff]

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" <[email protected]>
wrote in message

 

[Max]

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

 

[Tony Canevaro]

Sorry, this formula is IN F4

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

 

[Tony Canevaro]

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 XLS/

 

[Anne Troy]

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

"Tony Canevaro" <[email protected]>
wrote in message
news:[email protected]...

 

[Tony Canevaro]

'click here' (http://home.armourarchive.org/members/ewan/Sample XLS/)


Sorry, don't know how that happened:



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

Thanks again!

 

[Max]

.. (http://home.armourarchive.org/members/ewan/Sample XLS/)

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 Insert>Name>Define)
---------------
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.))