Very Long (Working) Furmula. Can it be condensed or turned into a Macro?

[Beeferson]

Sorry guys, new to Excel and it was hard to make this formula. It is Literally the extent of my knowledge at this point to have even gotten it to work.

This formula takes a cell and looks for the persons name in a specific column on a sheet. If the name is found it returns the text in cell B1 of that sheet. I repeat the formula after an "&" Symbol to Check another sheet (Job) in the same way and return THAT text in its B1 Cell after the returned text from the first sheet. so the formula will return multiple job names separated by a space and comma if the worker is doing multiple jobs.

My issue Is I will have a min of 21 pages to check for the name, and that will be a huge formula that I will have to edit the sheet name, and column to search for different types of workers.
Here is the formula.

=IF(COUNTIF('Job Name Here'!$B$3:$B$15,'Labor Pool'!A2)>0,'Job Name Here'!$B$1&", ","AVAILABLE")&IF(COUNTIF('Job Name Here (2)'!$B$3:$B$15,'Labor Pool'!A2)>0,'Job Name Here (2)'!$B$1&", ","AVAILABLE")

If there is an easy way to make this a macro (sub) that would be easy to add to that would be awesome
If I over complicated what I'm trying to do, then an easier way would be much appreciated.

 

[Jeffrey Mahoney]

This may or may not be what you want to hear. Yes, a macro could be written for that. Could you manage the macro as the sheet names change and the number of sheets increase or decrease? That's for you to answer.

Here is a solution. Instead of creating one formula to get all the sheet results. Create a formula for each sheet that looks at a cell with the sheet name in it.

This formula was placed in cell B2 and is looking at cell B1 for the sheet name.
Cell B2: =IF(COUNTIF(INDIRECT("'"&B1&"'!"&"$B$3:$B$15"),'Labor Pool'!A2)>0,INDIRECT("'"&B1&"'!"&"$B$1"),"AVAILABLE")

Cell B1: Job Name Here

You copy that formula across the number of columns for which you have sheets. Put the sheet names above each. Use one formula to concatenate them all.

Cell A2: =B2&", "&C2&", "&D2

You could simply hide the columns until you need to edit them.

Jeff