IF function to recognize data and place it in another worksheet in a proper column

See Worksheet CORE SCHEDULE (2) and SHEET 2.

Core Schedule (2): I need excel to recognize the Clinic name in J5, J11 (Ortho App), the MA name in J7, J8, J13, J14 (Kathy H) the hours worked in M7, M8, M13, M14 example is Monday, but would need it to look at the same cells for Tuesday through Sunday, and then total the hours worked in the proper cells under the correct clinic and MA name in Sheet 2.

Example: Kathy H is the MA; excel takes the hours from M7, Y8, and AK7 and totals them in Sheet 2/E30; and takes M13 and Y14, and totals them in E31.



FYI: In Sheet 2 the clinic names are listed on the top row from E1 to S1

I can't see a way of doing this with a formula alone (unless there are very clever people out there who are good with array formulas).

I played with OFFSET formulas, but you still need to create a table with a concatenation of name and clinic (I think).

Are you open to having a macro which will generate such a table. (This could also then be useful for Pivot Tables).

Regards, David

Yes I’m open to it.

Originally Posted by doop4204

See Worksheet CORE SCHEDULE (2) and SHEET 2.

Core Schedule (2): I need excel to recognize the Clinic name in J5, J11 (Ortho App), the MA name in J7, J8, J13, J14 (Kathy H) the hours worked in M7, M8, M13, M14 example is Monday, but would need it to look at the same cells for Tuesday through Sunday, and then total the hours worked in the proper cells under the correct clinic and MA name in Sheet 2.

Example: Kathy H is the MA; excel takes the hours from M7, Y8, and AK7 and totals them in Sheet 2/E30; and takes M13 and Y14, and totals them in E31.



FYI: In Sheet 2 the clinic names are listed on the top row from E1 to S1

OK - I'll come up with something, but it will be after the weekend. I am fully booked, and away from my computer for a few days.

Trust you can wait a few days.

David

This there any follow up on my request?

Sorry doop4204,

I was otherwise detained yesterday, and couldn't get to your problem.

I have something that I'm working on, but now realise I'm on the wrong track a little.

There was nothing to distinguish the two "bands" being analysed, so my initial macro added up whether Kathy was MA1 or MA2. I now realise that for the Ortho App clinic, there is something different about row 3 and row 9, and that's what you are trying to separate. I can't find anything to tell me which is which. Is there anything?

Regards,

David

Im open to editing the spreadsheet to make it work. You have permission.

Is there an update?

Hi doop4204 - How did you get on? Haven't seen a response, so don't know if you need anything more!

David

Thanks for the work on this. Is not exactly what I was looking for but still a starting point. Looking for something cleaner.

Sorry to hear that. I can't think of another solution. Good luck!

David