JuicyMusic
Board Regular
- Joined
- Jun 13, 2020
- Messages
- 210
- Office Version
- 365
- Platform
- Windows
These INDIRECT formula's are absolutely testing me, but I'm going to learn how to adjust them if it's the last thing I do….with your help, of course.
I can see now that I need to add a criteria to my formula. I will try my best to explain.
Question 1: Is it possible for the formula in column R of the 1st attachment/tab to bring back all the job numbers that the employee, named in column B of the source tab, worked on instead of just the first occurrence that it finds there? Can it bring back the result as a string? Example: "Job# 9358A, Job# 9405J"-----------------AS LONG AS the text "Activity on Job" is in column D of the target row on the source data tab.
AaronA is an excellent example. His name was listed twice on the source tab. His name was simply included in a note for Job #9358A. He didn't actually didn't work on job #9358A. It was just a note and should not be counted. You can see the text "No activity on Job" in column D of the corresponding row......….His name IS listed on an active job - and the text in column D for that row is "Activity on Job". This means that he did work on Job #9405J...….SO, this is the job number that should be returned in column R on the 1st attachment/tab...…SO counted in column T as "1".
Formula in column R:
Formula in column T:
Sorry that I can't use the XLBB here at work. I've tried and I've been denied. I hope that attachment images are acceptable.
The 1st attachment/tab: This spreadsheet shows us what job numbers employees worked on - by day. 126 Employees are listed here and they work on ONE job 99.9% of the time. Only a handful will work on more than one job in a day. I filtered this tab by the two example employees because this sheet is very long. See the formula in column
The 2nd attachment/tab: The source data for the 1st attachment is on this tab. The wildcard formula on the other tab looks for employee names is in this tab, in column M.
I filtered this tab so you can see a portion of it and easily see the issue I'm having with 2 employees as an example. I did this because this tab is 4000 rows long.
Thank you so much.
I can see now that I need to add a criteria to my formula. I will try my best to explain.
Question 1: Is it possible for the formula in column R of the 1st attachment/tab to bring back all the job numbers that the employee, named in column B of the source tab, worked on instead of just the first occurrence that it finds there? Can it bring back the result as a string? Example: "Job# 9358A, Job# 9405J"-----------------AS LONG AS the text "Activity on Job" is in column D of the target row on the source data tab.
AaronA is an excellent example. His name was listed twice on the source tab. His name was simply included in a note for Job #9358A. He didn't actually didn't work on job #9358A. It was just a note and should not be counted. You can see the text "No activity on Job" in column D of the corresponding row......….His name IS listed on an active job - and the text in column D for that row is "Activity on Job". This means that he did work on Job #9405J...….SO, this is the job number that should be returned in column R on the 1st attachment/tab...…SO counted in column T as "1".
Formula in column R:
Excel Formula:
=IF(OR(Q10="Off/Vacation",Q10="--"),"--",VLOOKUP("*"&$C10&"*",INDIRECT(Q$8&"!$A:$B"),2,0))
Formula in column T:
Excel Formula:
=IF(Q10="Off/Vacation","--",
IF(ISERROR(COUNTIFS(INDIRECT(Q$8&"!M:M"),"*"&Q10&"*")),"--",COUNTIFS(INDIRECT(Q$8&"!M:M"),"*"&Q10&"*")))
Sorry that I can't use the XLBB here at work. I've tried and I've been denied. I hope that attachment images are acceptable.
The 1st attachment/tab: This spreadsheet shows us what job numbers employees worked on - by day. 126 Employees are listed here and they work on ONE job 99.9% of the time. Only a handful will work on more than one job in a day. I filtered this tab by the two example employees because this sheet is very long. See the formula in column
The 2nd attachment/tab: The source data for the 1st attachment is on this tab. The wildcard formula on the other tab looks for employee names is in this tab, in column M.
I filtered this tab so you can see a portion of it and easily see the issue I'm having with 2 employees as an example. I did this because this tab is 4000 rows long.
Thank you so much.