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

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

Not open for further replies.

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.

regards,

File size:
31.5 KB
Views:
52
2. ### 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.

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

File size:
14.5 KB
Views:
106
4. ### 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?

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

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!

#### Attached Files:

• ###### efgm tech guys worksheet with new formulas.xls
File size:
22 KB
Views:
43

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

8. ### 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!)

9. ### 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.

As Seen On