Returning same value cell in a column but different row.

[sephiroths816]

I'm not sure if my subject heading best explains what I'm looking for
but here is my question in greater detail.

I have a chart of data from which I pull from.

A1 = Name1
A2 = Name2
A3 = Name3
A4 = Name4
A5 = Name5

B1 = Date1
B2 = Date1
B3 = Date2
B4 = Date3
B5 = Date2

C1 = (returning value)
C2 = (returning value)
etc.

D1 = (User defined list box)

(by the way, I hate how this google group thing messes up your format
when you post a mock chart on here and screws up the symmetry of it.)

Ok, if the user choose "Date1" in D1, I want Column C to list all the
names with the corresonding date. My obstacle is that Excel seems to
only see the 1st or 2nd date and returns that name only, repeats that
same name. I'm trying to find a way to list them all. I could be
totally off, but I was thinking there was a way to say that if one
value was already listed in column C it would search the next value
and so forth until all names have been found with the corresponding
dates in Column B.

I realize I may not even have the best approach or asking too much of
Excel, so I welcome an suggestions and/or criticisms.

 

[vezerid]

I'm not sure if my subject heading best explains what I'm looking for
but here is my question in greater detail.

I have a chart of data from which I pull from.

A1 = Name1
A2 = Name2
A3 = Name3
A4 = Name4
A5 = Name5

B1 = Date1
B2 = Date1
B3 = Date2
B4 = Date3
B5 = Date2

C1 = (returning value)
C2 = (returning value)
etc.

D1 = (User defined list box)

(by the way, I hate how this google group thing messes up your format
when you post a mock chart on here and screws up the symmetry of it.)

Ok, if the user choose "Date1" in D1, I want Column C to list all the
names with the corresonding date. My obstacle is that Excel seems to
only see the 1st or 2nd date and returns that name only, repeats that
same name. I'm trying to find a way to list them all. I could be
totally off, but I was thinking there was a way to say that if one
value was already listed in column C it would search the next value
and so forth until all names have been found with the corresponding
dates in Column B.

I realize I may not even have the best approach or asking too much of
Excel, so I welcome an suggestions and/or criticisms.

If your names are in A1:A5. In C1:

=INDEX($A$1:$A$5,small(if($B$1:$B$5=$D$1,ROW($A$1:$A$5)-ROW($A
$1)+1),ROW()-ROW($C$1)+1)

This is an *array* formula, hence commit with Ctrl+Shift+Enter.

HTH
Kostis Vezerides

 

[Guest]

Nick Hodge's site explains how to query external data. However, an Excel
worksheet can also be treated as 'external data'

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

At the end of that article Nick explains how to re-run a query against an
Access database when criteria in an Excel cell changes. You can easily adapt
that from Access to Excel. When the date changes, query for all the names
related to that date.

 

[sephiroths816]

Thanks Vezerid, that worked like a charm. Although, I must add for
other users searching a similar solutions, I had to add the ISERROR
function to avoid the "#NUM!" to display when there were no more
values to report.

Duke Carey, thanks for the link. As of right now, I'm only working
strictly with Excel and do not need it to draw data from Access just
yet. But I will keep the link saved for such future purposes.

 

[vezerid]

ThanksVezerid, that worked like a charm. Although, I must add for
other users searching a similar solutions, I had to add the ISERROR
function to avoid the "#NUM!" to display when there were no more
values to report.

Duke Carey, thanks for the link. As of right now, I'm only working
strictly with Excel and do not need it to draw data from Access just
yet. But I will keep the link saved for such future purposes.

Glad to know it worked!

Kostis