+ Reply to Thread
Results 1 to 17 of 17

Newbie question Pulling data from one sheet to another based on

  1. #1
    Registered User
    Join Date
    10-23-2005
    Posts
    15

    Newbie question Pulling data from one sheet to another based on

    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

  2. #2
    Anne Troy
    Guest

    Re: Newbie question Pulling data from one sheet to another based on

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




  3. #3
    Registered User
    Join Date
    10-23-2005
    Posts
    15
    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.

  4. #4
    Registered User
    Join Date
    10-23-2005
    Posts
    15
    why won;t this : =('Price List'!$F$4)*F4
    work?

    This really what I am trying to do

  5. #5
    Anne Troy
    Guest

    Re: Newbie question Pulling data from one sheet to another based on

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




  6. #6
    Registered User
    Join Date
    10-23-2005
    Posts
    15
    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.

    ??????????

  7. #7
    Roger Govier
    Guest

    Re: Newbie question Pulling data from one sheet to another basedon

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


  8. #8
    Registered User
    Join Date
    10-23-2005
    Posts
    15
    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.

    click here
    Last edited by Tony Canevaro; 10-24-2005 at 01:58 PM.

  9. #9
    Max
    Guest

    Re: Newbie question Pulling data from one sheet to another based on

    "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 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.))
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  10. #10
    Registered User
    Join Date
    10-23-2005
    Posts
    15
    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.

  11. #11
    Registered User
    Join Date
    10-23-2005
    Posts
    15
    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?

  12. #12
    Max
    Guest

    Re: Newbie question Pulling data from one sheet to another based on

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



  13. #13
    Anne Troy
    Guest

    Re: Newbie question Pulling data from one sheet to another based on

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

    "Tony Canevaro" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >




  14. #14
    Max
    Guest

    Re: Newbie question Pulling data from one sheet to another based on

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



  15. #15
    Registered User
    Join Date
    10-23-2005
    Posts
    15
    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.

  16. #16
    Max
    Guest

    Re: Newbie question Pulling data from one sheet to another based on

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



  17. #17
    Anne Troy
    Guest

    Re: Newbie question Pulling data from one sheet to another based on

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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1