1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: excel - data returned based on the value of a specific cell

Discussion in 'Business Applications' started by ricuelo, Jan 31, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. ricuelo

    ricuelo Thread Starter

    Joined:
    Jan 10, 2009
    Messages:
    37
    i have a spreadsheet that updates with data from a number of different sources. i need to filter only the relevant data that i need. i'm almost there but need to find out how to pull 2 more pieces.

    in the attached spreadsheet the cells i need are in column R and T. I need to know the corresponding "name" and "size" of the data that is in column S.

    unfortnuately the actual values don't show up in the spreadsheet b/c they are live data feeds but the formulas written up until now are still there.

    in a nutshell what i'm doing is getting 5 price feeds and i need to filter out which is the highest updated within the last 2 minutes. i actually have the only the price figured out in column S, but i also now which name and how big the size is related to that price.

    your help is very appreciated. :D

    regards,
     

    Attached Files:

  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hi,

    Given that the sheet you posted contains essentially only linked data, it is all gibberish, consisting essentially of nothing but #NAME? in all the cells.
    Some of the formulas do make sense, however, such as that in S which finds the maximum price from 5 selected.
    In the above quote, you say you need to know the corresponding name to that quote. Am I to assume that name1 is tied to price1 and so on? If it is, then that should be possible.
    As for the "size" of the data in S, that doesn't make sense in any particular way. Do you want to know what range of amounts it falls into (e.g. 1-10, 11-20, 21-30) or the number of characters in the amount (123.55 has 6 characters, but I can't see that being any use....)
    Please post another sheet with some dummy data in it - not the links - and some answers for these items that you have calculated so I can write the formulas that will do the same.
     
  3. ricuelo

    ricuelo Thread Starter

    Joined:
    Jan 10, 2009
    Messages:
    37
    hi,
    you are correct in assuming that price 1 corresponds with name 1, etc. the size field just indicates how much is available at that price (really for my information only)

    in the final results column, i'm looking for 3 pieces of information. the price (which i have the formula for), the name of the buyer, and the amount they are willing to buy (size).

    this is all taken from a universe of up to 5 buyers and the data updates automatically as you can see from my previous spreadsheet with all the #name gibberish.

    thank you in advance for your help. i've attached a cleaner spreadsheet i think that will help
     

    Attached Files:

  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Let me see if I have this correct.
    You have 5 bids. In each you list:
    bid px | bid time | bid size
    In the BID boxes (in T-V in your latest file)
    you want to show the maximum bid price, when that bid occurred and how many units (bid size) the bidder was willing to purchase?
     
  5. ricuelo

    ricuelo Thread Starter

    Joined:
    Jan 10, 2009
    Messages:
    37
    correct. i'm trying to have the most up to date information as possible bases off of the universe that i have in columns C thru Q. obviously that i can't mix and match price 1 with time 3, thats why i was trying to pull the size and time (columns U and V) with respect to the data in T.

    FYI, i will be able to post back much quicker now (almost immediately) because i got the site unblocked at work.

    thx again
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I am sure there could be slicker ways to do this - and I based some formulas on the table you have in (what is now) AB:AF. If you don't want to make these tables, it would be possible to do the same thing based on the 5 bid tables, I am sure.
    I tinkered with your labels just a bit - one formula relies on it being "1 bid time" (I think) and the such, and that that word would be over the time of bid #1.

    HTH! :D :rolleyes:
     

    Attached Files:

  7. ricuelo

    ricuelo Thread Starter

    Joined:
    Jan 10, 2009
    Messages:
    37
    i'm not sure if there is a slicker way or not but whatever you did is EXACTLY the results i was looking for. GREAT JOB. i never would have been able to figure this one out without you guys.

    stupid question time. is there a way to pull down the formula you wrote in column W so that it increases the row it looks at each time without having to manually type in the new row. i tried, but it either copies the same row (if i pull it down by itself) or it repeats the pattern (if i manually adjust the first x number of rows, highlight them all and then pull down the formula i.e. 1,2,3,4,1,2,3,4,1,2,3,4.) i need to know because the real spreadsheet i use has over 300 rows and i don't want to make any manual mistakes doing it by hand.

    also, just for my fyi...how come column X (time column) has a 0 at the end of the formula?

    thanks again for helping me out

    (y) (y)
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I worked on that regarding W and it came in a flash now. Insert a new row, say at Y (so the Time shifts to column Z).
    In cell Y7, enter the number 3 and in Y8 enter the number 4. Drag those down as far as you want.
    Now change the formula in W7 to read
    =HLOOKUP(U7,C$5:Q7,Y7)
    Drag it down, and the Y7 will become Y8, etc, so those numbers will change automatically also! :)
    Oh, and the 0 at the end of the formula (you could also use FALSE) forces the formula to find an exact match, rather than the a nearly correct one that it encounters first.
    Try changing the 0 to a 1 and see the difference it makes. (Then undo your edits!)
    Glad to have helped!
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Oh, and if this is all fixed (at least for now!) please use the button at the top of the page to mark this thread as Solved. ;)
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/796294

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice