help with date refrence

[nartnart]

failure record.xlsx
i need help if any can help me.
i have worksheet for recording the failures o computers, if there is a failure in a specific it is indicated by "OFF" in the sheet. how can i have the dates of failure to be inserted in another sheet depending n the status of the PC. attached sample of required data. (if PC 1 is OFF in 2-May-2104 the date should be inserted in second sheet named PC1 and so on)

[misrasomendra]

Hi,

See the file. I had illustrated the formula on PC1 sheet, same can be repeated for all computers.

[nartnart]

thanks a lot. it works well. i tried to add more dates and changed the function with the new area but it only show the date with old results, what i have to change also.
=IFERROR(INDEX(Sheet1!$B$1:$AE$1;;SMALL(IF(INDEX(Sheet1!$B$2:$AE$3;MATCH('PC 2'!$B$1;Sheet1!$A$2:$A$3;0)="OFF";COLUMN(Sheet1!$B$1:$AE$1)-COLUMN(Sheet1!$B$1)+1);ROWS($A$2:A2)));"")

[misrasomendra]

=IFERROR(INDEX(Sheet1!$B$1:$AE$1;;SMALL(IF(INDEX(Sheet1!$B$2:$AE$3;MATCH('PC 2'!$B$1;Sheet1!$A$2:$A$3;0)="OFF";COLUMN(Sheet1!$B$1:$AE$1)-COLUMN(Sheet1!$B$1)+1);ROWS($A$2:A2)));"")

Change the red part also as per your range if you are including more rows.

[nartnart]

i did not include any new rows. only columns. attached my sheet. still not working

failure record.xlsx

[misrasomendra]

Unable to download file, kindly re-upload it.

[nartnart]

Unable to download file, kindly re-upload it.

thanks for response. uploaded

[misrasomendra]

See the file, You missed a comma highlighted in RED.

=IFERROR(INDEX(Sheet1!$B$1:$AE$1,,SMALL(IF(INDEX(Sheet1!$B$2:$AE$3,MATCH('PC 1'!$B$1,Sheet1!$A$2:$A$3,0),)="OFF",COLUMN(Sheet1!$B$1:$AE$1)-COLUMN(Sheet1!$B$1)+1),ROWS($A$2:A9))),"")

[nartnart]

thanks a lot. working perfect

[misrasomendra]

Thanks for the feedback. If you think my feedback had helped you you can give me some reps point by clicking the star of Add reputation below my comments.

[nartnart]

help if you can.
i did it for 25 PC manually. and i have another 20 PC. is there away to do it automatically ( to create a sheet for every PC that listed in sheet 1)

[nartnart]

i found this macro that will create new sheets but how to include the formula (the one that copy dates to the PC sheet) in the macro.

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Summary").Range("A10")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Sheets(Sheets.Count).Cells(1, 1).Value = "Failure Dates"
Sheets(Sheets.Count).Cells(1, 2).Value = MyCell.Value
Next MyCell
End Sub

[nartnart]

failure record (1).xlsm
my new file

[nartnart]

i need help if any can