[SOLVED] Help with advance if, match and index

[abhineet.sabharwal]

Hello,

In the attached file,I am trying to automatically populate job tabs from the timesheet tab. I only want to populate where there are hours next to the name. I have attached a sample of what I am trying to do. I

[Glenn Kennedy]

Use this:
=IFERROR(INDEX('Job 1'!B$2:B$5,MATCH('Time Sheet'!$A3,'Job 1'!$A$2:$A$5,0)),"")

copy across the 3 rows for job 1 and copy again, chaning Job 1 for Job 2, etc...

[dflak]

Instead of Match / Index, consider using a pivot table. It will take a bit of re-alignment of the source data. You will have to change the column headings so they are unique: e.g. ST1, OT1, DT1, ST2, etc.

Then you'll have to use some helper columns (these can be hidden) to see if that worker has any hours for the particular job. This can then be used as a filter on your pivot table.

I took the liberty of making the data source a table since the pivot table will adjust to encompass it as it grows or shrinks.

Also you might consider using data validation on the Name so you always have a consistent spelling.

For table information, see this wiki: http://www.utteraccess.com/wiki/inde...ables_in_Excel
For data validation, see this wiki: http://www.utteraccess.com/wiki/inde...ata_Validation

[JeteMc]

This solution requires a few automated steps to get to a table that looks like the ones that you want. It would only require that you manually change the input formula for the column header in cell B1 (i.e. from ='Time Sheet'!C2 to ='Time Sheet'!F2) on each of your Job tabs and then drag that formula across to cell D1. The formula that populates the first table is:

=IFERROR(IF(INDEX('Time Sheet'!$C$3:$Z$50,MATCH($A2,'Time Sheet'!$A$3:$A$50,0),MATCH(B$1,'Time Sheet'!$C$2:$Z$2,0))=0,"",INDEX('Time Sheet'!$C$3:$Z$50,MATCH($A2,'Time Sheet'!$A$3:$A$50,0),MATCH(B$1,'Time Sheet'!$C$2:$Z$2,0))),"")

The formula that populates the second table is:

=IF(AND($B2="",$C2="",$D2=""),"",A2)

and the two formulas that populate the final table are:

=IFERROR(INDEX(F$2:F$100,AGGREGATE(15,6,(ROW(F$2:F$100)-ROW(F$2)+1)/(F$2:F$100<>""),ROWS(E$1:E1))),"")

=INDEX($G$2:$I$11,MATCH($K2,$F$2:$F$11,0),MATCH(L$1,$G$1:$I$1,0))

The first two table can be hidden as is shown on the Job2 sheet.
Copy of test-4 (index-match-aggregate).xlsx
Let me know if you have any questions.

[abhineet.sabharwal]

This solution requires a few automated steps to get to a table that looks like the ones that you want. It would only require that you manually change the input formula for the column header in cell B1 (i.e. from ='Time Sheet'!C2 to ='Time Sheet'!F2) on each of your Job tabs and then drag that formula across to cell D1. The formula that populates the first table is:

=IFERROR(IF(INDEX('Time Sheet'!$C$3:$Z$50,MATCH($A2,'Time Sheet'!$A$3:$A$50,0),MATCH(B$1,'Time Sheet'!$C$2:$Z$2,0))=0,"",INDEX('Time Sheet'!$C$3:$Z$50,MATCH($A2,'Time Sheet'!$A$3:$A$50,0),MATCH(B$1,'Time Sheet'!$C$2:$Z$2,0))),"")

The formula that populates the second table is:

=IF(AND($B2="",$C2="",$D2=""),"",A2)

and the two formulas that populate the final table are:

=IFERROR(INDEX(F$2:F$100,AGGREGATE(15,6,(ROW(F$2:F$100)-ROW(F$2)+1)/(F$2:F$100<>""),ROWS(E$1:E1))),"")

=INDEX($G$2:$I$11,MATCH($K2,$F$2:$F$11,0),MATCH(L$1,$G$1:$I$1,0))

The first two table can be hidden as is shown on the Job2 sheet.
Attachment 433366
Let me know if you have any questions.

It works great.Thank you for your help.

[FlameRetired]

Another way. The setup is a little tedious, but if you have many Job # sheets it might be worth the time.

With a helper row inserted in TimeSheet cells C1, F1 and I1 place each Job #.


Edit I forgot to mention to insert a row above the tables in the Job # sheets in order for this next step to work.
Then in A1 of each Job # sheet type the sheet name of that particular sheet, or if you have numerous sheets you can group the sheets and use this formula in A1 of the first grouped sheet.

=TRIM(RIGHT(SUBSTITUTE(CELL("filename",C1),"]",REPT(" ",99)),99))

it will put the sheet names into A1 of each sheet. Consider copying and pasting values back onto themselves. CELL is volatile and all formulas will "inherit" that volatility if you don't.

Another edit If you are unfamiliar with grouping sheets click on the first Job # sheet, hold down shift as you click on the last Job# sheet. The sheets will be grouped. Whatever you do to the visible sheet will also be done to each of the other grouped sheets at the same time. It can save time and is an alternative to copy/paste to numerous sheets.

Then with sheets still grouped put this formula into B3 fill down and across until you get blanks.

=IFERROR(INDEX('Time Sheet'!$C$4:$K$10,MATCH($A3,'Time Sheet'!$A$4:$A$10,0),MATCH($A$1,'Time Sheet'!$C$1:$K$1,0)+(COLUMNS($A:A)-1)),"")

[abhineet.sabharwal]

It works. Thank you for your help.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools above your first post. Hope that you have a good day.