Results 1 to 14 of 14
  1. #1
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Lookup (Office '03)

    I am trying to construct a spread sheet with several refrences.
    I have in a dropdown box 8 different items, when the user selects #1 then that number 1 appears in the cell link. That part is working ok, and for that matter the sheet that I built so far is working too. My problem is this: When I select #1, then 7 cells fill in with their corresponding bytes.
    The formula for 1 cell is =IF(P3=4,O5,0) and so on down the line. Now how can I easily fill in those same cells with their data w/o using =IF(P3=1,O5,IF P3=2,O6) etc.etc.
    I tried using a lookup table, but that isnt working correctly.
    So, I have 5 different items, with 7 different sets of data. The Data all must (or at least should) go into the same corresponding cell. I have attached the spreadsheet. The only modem that is currently selectable is modem 3.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup (Office '03)

    I am confused about what you are after exactly. Your formulas only give a number when P3=4.

    Are you looking for an Index:

    <pre>=INDEX(O5:O11,P3)</pre>


    If not could you give and indication of what you want to "Lookup"?

    Steve

  3. #3
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Lookup (Office '03)

    Steve, Sorry for the confusion. I have cleaned the spreadsheet up a bit so that it easier to use and follow.
    Here is an explanation:
    The user selects a modem from the dropdown list in Step 1. The corresponding values D2329, then show up in J5:J10. The values there, are used to do the calculations for Cells M18:M23, and Cells B14:I14 and B15:I15.
    The results of the calculations Bytes etc. show in B14:I14, Which are daily usage, and B15:I15, which is monthly usage. The total costs shown also daily and monthly show in I14 & I15 respectively.
    In summary, the user selects a modem, and the costs of using that modem including the number of bytes, are calculated using the information listed, as well as the variables listed in Steps 2-6.
    I hope that helps. I am not familiar with index's but will read up on that.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (Office '03)

    Why does the list box (it's not a dropdown list) display Modem1 through Modem5, while the Modem Variables table only lists Modem1, Modem2 and Modem4?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (Office '03)

    Does the attached version do what you want? (I completed the modem table with dummy data)

    If not, please try to explain clearly what you want.

  6. #6
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Lookup (Office '03)

    Hans and Steve,
    After I replied to Steve's initial reply, I got to thinking that I had done something like this a few years back, using CHOOSE. I looked up that old spreadsheet, and used that format. It looks as if the CHOOSE, is now INDEX?. Yes Hans yours does exactly what I wanted to do. I have attached mine back to you.
    You guys are terriffic. And as an added bonus, with your questions back to me it really makes me think.
    THANKS A HEAP
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup (Office '03)

    Choose and Index both work. I prefer index, especially with long lists since I don't have to enter each individual in the formula. Index can also be 2-D (cols and rows)

    Steve

  8. #8
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Lookup (Office '03)

    How do you do that Seve?
    Also now they are asking that all of the totals show $xx.95 instead of $12.xx. I looked at round and a few others and cant find one that will do that. I was able to however by adjusting the round get one to increase from xx.59 to xx.95, but that doesnt work if the total is xx.25.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (Office '03)

    Please explain exactly what you want. Which cells should be "rounded", and how exactly? Should they be rounded up to the next dollar minus 5 cents?

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup (Office '03)

    <hr>How do you do that Seve?<hr>
    In j19:
    <pre>=INDEX(B23:F23,$H$22)</pre>

    copy j19 to J20:j25.

    I have the same question as Hans asked about how you want to round, so will wait for your response before I try to answer it.

    Steve

  11. #11
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Lookup (Office '03)

    yes Hans that would do it. Now why didnt I think of that!! =ROUND(xx,0)-.05 ?
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (Office '03)

    ROUND will round to the nearest dollar. If you want to round up, use ROUNDUP or CEILING:

    =ROUNDUP(xx,0)-.05

    or

    =CEILING(xx,1)-.05

  13. #13
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Lookup (Office '03)

    Thank you Kind Sir
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  14. #14
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Lookup (Office '03)

    Hans, etal. I am finally just about done with this spreadsheet, and I wanted to thank you and all the others for their input. Now if my suppliers would quit changing their parameters, I could actually finish it. It is finished enough though to send to my dealers, and that is OK for now. Again THANKS for all of the great tips. Whats even better, is those that told me I couldnt convert kilbytes to an actual cost with all of the markups involved, and have it come out correctly are eating a little bit of crow. I even had one of my suppliers ask me to do one just for there products, after they reviewed the sheet.
    My boss wanted me to insert a few things, such as the cost of an SMS message, and I told him that sure that can be done, but it isnt as simple as putting it in, because once I change one item I have to recalculate others. He still doesnt get it, but thats why he's the boss!.
    Anyway thanks again keep up the great work, and HAPPY NEW YEAR.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

Posting Permissions

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