On-going list that that populates other worksheets with no duplication

**RossN8** · 05-04-2014, 03:20 PM

I am using an Index & Match formula (=INDEX('Master Log'!B:B,MATCH("X",'Master Log'!$C:$C,0)))which has helped me, but I have a more complex list I am trying to create. I have a list "Master Log" on sheet one where I have a running list that will continually be updated. That list goes vertically, horizontally is a list of the other sheets in the workbook that a change in the list would apply to. What I am trying to create is that when I add something to the master list and put an "X" under the column of the affected worksheet, that when I go to that affected worksheet, the information that was added to the "Master Log" is populated on a list for that worksheet. The goal would be to skip any information that doesn't have an "X" for that specific worksheet, and not duplicate any entry's from the "Master Log". This is roughly what my "Master Log" looks like (table 1). This would be a running list that would be added to over time. I would like Sheet 1 in my workbook to generate a list with only the entries on 5/1/14, 5/3/14, and 5/8/14 in the example below (table 2). Any help is appreciated.

Date Change Made Sheet 1 Sheet 2 Sheet 3
5/1/14 Test 1 X
5/2/14 Test 2
5/2/14 Test 3
5/3/14 Test 4 X
5/5/14 Test 5
5/8/14 Test 6
5/8/14 Test 7 X

Sheet 1:
Date Change Made
5/1/14 Test 1
5/3/14 Test 4
5/8/14 Test 7

**azumi** · 05-04-2014, 11:33 PM

its difficult to figure it out the problem without your workbook, maybe better upload it your sample workbook at the forum

**RossN8** · 05-05-2014, 02:13 PM

Azumi- attached is a dummy file to take a look at. You can see that on "Sheet 1" I have the formula I was trying to use, unsuccessfully. On "Sheets 2-4" I have manually copied what I am hoping a formula will produce for me manually. Any help you can give me is greatly appreciated.

**sktneer** · 05-05-2014, 09:45 PM

Try this Array Formula on Sheet 1 in cell A4. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. hold down the Ctrl+Shift and then press Enter)

=IFERROR(INDEX('Master Log'!A$2:A$20,INDEX(ROW('Master Log'!$C$2:$F$20)-ROW('Master Log'!$C$2)+1,SMALL(IF(INDEX('Master Log'!$C$2:$F$20,0,MATCH("Sheet 1",'Master Log'!$C$1:$F$1,0))="X",ROW('Master Log'!$C$2:$F$20)-ROW('Master Log'!$C$2)+1),ROWS(A$4:A4)))),"")

and then drag across and down.

If this is what you are trying to achieve, for further sheets you need to change the sheet name in the formula. For ex. on sheet 2, change the sheet name in the formula inside the match function to sheet 2 and same for other sheets as well.

For detail see the attached sheet.