Referencing another sheet's rows when a cell in that row contains a specific value

[eulogy]

Ok, so this may be a little hard for me to explain, so bare with me.

Basically I have one sheet with a lot of data.

Column A has a 'group' identifier where 10-20 rows may have the same group, and another 10-20 later may have a different group.

On another sheet, I have a cell where the group identifier is specified.

On that sheet, I have rows with cells that need to reflect only the rows that match the group identifier with what is specified in another cell on this sheet.

For example... My "datasheet" looks like this:

Group Id	Member Name	Member Status
101	Jeff	Active
101	Greg	Inactive
102	John	Active
102	Jane	Active

Then I have a "groupsheet" that I want to look like this:

Group Id:	101
*********	*********
Member Name	Status
Jeff	Active
Greg	Inactive

So it only shows the rows where the group ID matches whatever it is set to (in the above case, 101, which would show only Jeff and Greg).

My actual data is a lot larger than this so I need a formula I can copy and paste and have it reflect the next row. They are all sorted by group.

This is the closest I've come...

The formula where "Jeff" is in my "groupsheet":

Please Login or Register  to view this content.

However it always shows the first match (or last if I set it to TRUE), I need it to increment as I paste it down the column.

I've also done these formulas to get the first and last rows that contain the data I need, if I could somehow use the returned values in an INDIRECT function and select a range between the 2 values these return...

Please Login or Register  to view this content.

(returns 2 - the first row where A contains '101')

Please Login or Register  to view this content.

(returns 3 - the last row where A contains '101')

[Pete_UK]

You can do this with 3 basic formulae. Post an example workbook and I'll set them up for you.

Pete

[martindwilson]

here is one way with a helper column

[eulogy]

Thanks a ton! Here it is.

[eulogy]

here is one way with a helper column

I'm not sure this will work because the datasheet is referencing the groupsheet, but I forgot to mention that I will have multiple group sheets, one for each group number.

[martindwilson]

i'll leave it to pete

[Pete_UK]

Thanks Martin !!

I've put this formula in A3 of the group sheet:

Please Login or Register  to view this content.

and copied this down beyond the amount of data you have in the datasheet. It gives a sequence, identifying the records which match the chosen code in G1.

Then I put this formula in B3:

Please Login or Register  to view this content.

and this finds the rows in the data sheet where those matching records occur. In both these formulae I return hyphens instead of empty cells, so you can see how far they have been copied down.

This formula is in C3:

Please Login or Register  to view this content.

and this can be copied across to E3 to return the appropriate data from the datasheet. The formulae in B3:E3 can then be copied down as far as needed (not as far as the formula in column A).

Once it is all set up, then the sheet can be copied (use Ctrl-drag) and the copy renamed - just change the group code in cell G1 to get the data for another group.

Incidentally, the data in the datasheet does not need to be sorted in any way - it can be in any order. Try it by adding another record for 101 below the data you already have.

Hope this helps.

Pete