Newbie question Pulling data from one sheet to another based on

T

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.

Help :confused:
 
T

Tony Canevaro

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

Tony Canevaro

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

This really what I am trying to do
 
T

Tony Canevaro

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.

??????????
 
R

Roger Govier

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
 
T

Tony Canevaro

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/members/ewan/Sample XLS/
 
M

Max

Tony Canevaro said:
.. (http://home.armourarchive.org/members/ewan/Sample XLS/)
Click to expand...

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 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.))
 
T

Tony Canevaro

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

Tony Canevaro

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

Max

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
 
T

Tony Canevaro

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with quote program using muliple prices for one quantity {cross post} 7
Moving Data on opening Excel 3
Auto-Calculate price based on width x height 0
pulling data from one sheet to another 12
comparing from one sheet to another 1
Count the columns from one sheet and paste it in another sheet 0
Moving data from one worksheet to another 1
Help with pulling data based on latest date for a year 2

Top