How do you pull sheet names into the Master sheet?

Microsoft Excel 2013 32/64-bit - license...
January 10, 2018 at 10:08:53
Specs: Windows 8
I have a compound problem that I would like some help on, if possible:

I have a workbook that contains 10 sheets. The first sheet is the Master and all the other sheets are locations. For ease of discussion, the Master sheet’s name is Quebec and each of the following sheets represents one of the following: Montreal, Sherbrooke, Vaudreuil, etc.

Row 2 on all sheets list job types: (D2) Analyst, (E2) Driver, (F2) Manager, etc.
All job types are displayed on the master sheet but individual sheets only show Job Types available at that location. If the job type is not available at the site, the column is hidden (not deleted) on that respective sheet.

Column 2 on all sheets displays the different regulations.

Each sheet will be sent to its appropriate location to complete. Each sheet has a pull down menu that says ‘Yes’ if the location’s job type follows a regulation. If the job type doesn’t follow the regulation the cell is left blank.

I am looking to summarize the information, in the Master (Quebec) once I get back all the sheets from the locations. If a location has a job type, I would like to know the location name (sheet name) on the Master (whether or not the location follows the regulation). That said, for those locations that follow the regulation I would like some kind of confirmation shown beside the location name.

For example: if Montreal and Vaudreil have an ‘Analyst’ job type and only Montreal’s follows the regulation listed in cell B11. Then the Master Sheet (Quebec) cell D11 would display ‘Yes’
Montreal Yes

I put the following formula in cell D11 on the Master but it didn’t work: =SUM(IF('Montreal'!D2="Analyst")+(IF('Montreal'!D11="Yes")),Yes,)

Does anyone have any suggestions or help they can offer? I am on a tight deadline and running out of time!

See More: How do you pull sheet names into the Master sheet?

Reply ↓  Report •
Related Solutions

Ask Question