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.

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=478640

Try this, Tony:
http://www.officearticles.com/tutori...soft_excel.htm
You'll just happen to have more Price columns. :)
************
Anne Troy
www.OfficeArticles.com

"Tony Canevaro"
wrote in message
news:Tony.Canevaro.1xdtef_1130115915.092@excelforu m-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.

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=478640

Anne,
That is very helpful, in general thank you.

The problem is that each part number has four prices. So qty doesn't
work in this instance. What I wanted to do was be able to enter a
number into the appropriate column & cell (i.e. part#,daily rate) and
have that number multiplied by the price already entered into the price
sheet in the same work book.

I don't know if this makes sense or not. Basically I have a price list
and now I would like a nearly identical sheet that references the
prices list for certain cells. I know this can be done and probably
isn't that hard...I just don't have the lingo for this stuff yet.


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

why won;t this : =('Price List'!$F$4)*F4
work?

This really what I am trying to do


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

How are you designating the 4 different prices? You probably need something
like this, then:
http://www.cpearson.com/excel/lookups.htm
************
Anne Troy
www.OfficeArticles.com

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

Anne,
That is very helpful, in general thank you.

The problem is that each part number has four prices. So qty doesn't
work in this instance. What I wanted to do was be able to enter a
number into the appropriate column & cell (i.e. part#,daily rate) and
have that number multiplied by the price already entered into the price
sheet in the same work book.

I don't know if this makes sense or not. Basically I have a price list
and now I would like a nearly identical sheet that references the
prices list for certain cells. I know this can be done and probably
isn't that hard...I just don't have the lingo for this stuff yet.


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

I think my core problem may be that I am trying to use the data entered
into a field to affect and display data in that same field.
I think I will have to create a "dummy" price list with seperate labels
for each type of price for each part number.

??????????


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

Hi Tony

Let's say your pricelist is on Sheet1. Row 1 contains the headings as you
describe and A2:F2 contains the data, or more correctly A2:F100 assuming you
have 99 items listed.

On your quote sheet, Sheet2, you enter in cell A2 the item name, and in B2
the time period, 1 to 4.
To pick up the other information, then use Vlookup formulae
If you want description to appear in C2 then in C2
=VLOOKUP(A2,Sheet1!$A$1:$A$100,2,0)
To pick up the price for the relevant time period, in column D then in D2
=VLOOKUP(A2,Sheet1!$A$1:$A$100,B2+2,0)
So, if you put a 1 in B2 you would get prices from Column C on Sheet1, if
you put 4 in B2, you would get prices from column F.

Change ranges to suit.
Note, if your sheet names are not sheet1 sheet2 etc, and have spaces in the
names, then you must enclose the sheet names in single quotes
'My Pricelist'!$A$1:$A$100


Regards

Roger Govier


Tony Canevaro wrote:
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.

Help

I feel like I am so close.

Thanks for all of you help so far.
Roger, I feel like that should work...but I can't seem to get it right.


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/member.../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=478640

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

As responded in your other post in .misc ...

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

Max,

If you are married, please tell your wife that you are a God. :)

I sooooo need to take a course on Excel, there is so much you can do
with it, when you know how.


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

Wow, sooo close.

I added a Qty column and then a Price column. Then I used the formula
=(E3*D3) copied down.

Works great except for the cells that have no data, I get a #Value
error. As sson as there is data in the field it all works well enough
for my purpose but how can I hide, or fix this?


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

Glad you liked it !
... please tell your ... that you are a ...
... only at the immeasurable risk of feeling very mortal ! <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

=if(iserror(e3*d3),"",e3*d3)
or
if(or(isblank(e3),isblank(d3)),"",e3*d3)
************
Anne Troy
www.OfficeArticles.com

"Tony Canevaro"
wrote in message
news:Tony.Canevaro.1xfweb_1130213112.422@excelforu m-nospam.com...

Wow, sooo close.

I added a Qty column and then a Price column. Then I used the formula
=(E3*D3) copied down.

Works great except for the cells that have no data, I get a #Value
error. As sson as there is data in the field it all works well enough
for my purpose but how can I hide, or fix this?


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

Another alternative (along the same lines as the error trap used in col D in
the sample's set-up) with "Qty" input in E3 down and "Price" to be
calculated in F3 down ..

Put in F3, copy down: =IF(OR(D3="",E3=""),"",E3*D3)

Or, we could amend the 1st part of the formula in col D
to return zeros instead of blanks: ""
(the null string "" is what causes the downstream error #VALUE! in col F)

i.e. use in D3, copied down: =IF(OR(A3="",C3=""),0, ...
instead of: =IF(OR(A3="",C3=""),"", ...

Then to calc the price, just use in F3, copied down: =E3*D3

New lines w/o data will then simply show up as zeros in cols D & F

And if desired, for a cleaner look,
we could suppress the display of zeros in the sheet via clicking:
Tools Options View tab Uncheck "Zero values" OK
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Anne and Max the God and Goddess of Excel!

Seriously, thanks very much both of you. I learned a fair bit.

Still not "Why" you do the crazy stuff you you do but a bit of how.


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

You're welcome, Tony !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Anne imagines self in long flowing white with gold accessories, her Excel
tiara set slightly off-center atop her tresses, which are being blown gently
by the wind...then wakes up. :)
You're welcome, Tony!
************
Anne Troy
www.OfficeArticles.com

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

Anne and Max the God and Goddess of Excel!

Seriously, thanks very much both of you. I learned a fair bit.

Still not "Why" you do the crazy stuff you you do but a bit of how.


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