Pulling in worksheet name and answer

Microsoft Excel 2013 32/64-bit - license...
January 3, 2018 at 13:33:29
Specs: Windows 8
Hi,

I have a Workbook with different spreadsheets. The first worksheet is a master that collects data from the other sheets. Every sheet has a job type in the second row and qualification in the second column. Each sheet (except the master) has a pull down menu that will display yes if the site (sheet) has the job type that follows the qualification. A site may have the job type but may not follow the qualification.

My boss has asked that the master sheet (first sheet) pull in the site name (sheet name) for all sites that have the specific job type (whether or not yes was selected) and show if the site selected yes in the cell that corresponds to the job type/qualification. Not all sites have the same job types and not all sites have the job types follow the regulations.

Right now I have a countA formula (in the master) that will count the yeses selected by in a sheet. What is missing is the site name and which site selected yes.

So that you have the full picture - there are also 3 columns (on each sheet) that the end user can enter information in. When they do enter information the mastersheet will display the sites and the notes that correspond to the site. If no notes are entered the column is blank (this was all done in VBA)...

Can anyone help me since I am just learning VBA? This can be done in a formula or in VBA - whatever works best.

Danielle

message edited by Daniej


See More: Pulling in worksheet name and answer

Reply ↓  Report •

#1
January 4, 2018 at 06:05:23
I've read your description of your workbook multiple times and I've got to admit, I'm not seeing "the full picture".

Not only am I having trouble picturing what your data sheets look like, I don't know what what you mean by "pull in the site name". Well, I know what mean by "pull in" but there doesn't seem to be any information as to where it should go, when it should be pulled in, etc. what would trigger the "pull", etc.

We (I) need more info.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
January 4, 2018 at 08:57:28
Thank you DerbyDad03 for replying.

Each sheet is a site which means that each sheet name equals site name.

Each sheet is populated with the job types that are found at that location. Not all job types are in each location. The master sheet has all the job types on it. Each cell on the site sheets have pull down menus that only have Yes on it. The site selects the cell that corresponds with job type (2nd row) and the regulations (listed down the second column) if the job type follows that regulation.

The master sheet will display the count (in each corresponding cell) for the sites 'job type' that follows the regulation. So for example if 3 of the sites (so 3 different sheets) select 'Yes' in the cell that corresponds with cleaning crew and adherence to regulation 1013 the master sheet will display 3 in the corresponding cell.

What I need is for the master to display (in the cell) the site name for all sites that have the job type and then place the count (which will always be 1) next to the site name... So for the example above - if we have 5 sites with cleaning crews I need to put the site name in the cell... if the site answers 'Yes' I need to put the count (1) next to the site name. If the site doesn't follow the regulation but has the job type the site name will be listed in the cell with no number next to it. If the site doesn't have the job type the site name should not appear in the cell.

Please let me know if this is clearer. Thank you for your time!


Reply ↓  Report •

#3
January 4, 2018 at 09:52:55
Your last paragraph is still confusing to me.

You said: "...if we have 5 sites with cleaning crews I need to put the site name in the cell"

"5 sites" & "site name in the cell" reads to me as "Multiples sites" & "single site name in a single cell".

That is confusing.

Then you want to put a count next to each site name. I'm not sure how to do that since I don't yet know how the address the confusing part I mentioned.

Bottom line is that I am not able to visualize what your sheets looks like now and certainly can't visualize how those site names and counts are supposed to be displayed on the master sheet.

I think that you are going to have to supply a sample workbook so I know exactly how is data is laid out and how you want it laid out in the future. Posting a file at zippyshare.com and then posting the link back here in the forum will accomplish that. If you decide to do that, make sure that your do not include any personal or confidential data.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
January 4, 2018 at 11:35:48
I am trying to attach the workbook so you can see it but can't seem to be able to... is there something I am missing?

Reply ↓  Report •

#5
January 4, 2018 at 12:20:57
It is my understanding that you can not attach files to posts in this forum. That is why I mentioned zippyshare.com in my previous post.

Feel free to upload the file to any file sharing site that you like, but keep in mind that most of us (including me) don't like to use file sharing sites that force us to register before getting access to the files. zippyshare does not require registration; that is why I suggested it.

If you can not use zippyshare, I might provide an email address, but I'd rather that the file be available for others to access in case they have suggestions also. Email takes the thread out of the "public domain" which is contrary to the spirit of this forum.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
January 4, 2018 at 12:21:41
Let's say the workbook has 10 sheets.

Each sheet is a site - each sheet's name is the site name.

Not all sites have the same job types but all the regulations are the same.

The master sheet will display all job types in the workbook. The site sheets will only display the name of the job types available at their site, again all regulations are the same and listed down column B

Each site (sheet) will receive only their sheet. Each cell has a pull down menu that says Yes. If the site's Job Type (listed in Row 2) follows the Regulation (listed in column B) they must select Yes.

The Master Sheet needs to be updated and display under the Job Type (Row 2) and in the Corresponding Regulation (Column B) all the names of the sites that has the job type and which of these selected Yes.
For example:

Site 1
Regulation
Cleaning Crew Operator Office Worker

1020 Yes
1030 Yes
1040 Yes

Site 2
Regulation
Cleaning Crew Operator Office Worker

1020 Yes
1030 Yes
1040 Yes

Site 3
Regulation
Loader Operator Manager

1020 Yes
1030 Yes
1040 Yes


Master
Regulation
Cleaning Crew Loader Operator Office Worker Manager

1020 Site 1 Site 1 Yes
Site 2 Yes Site 2
Site 3

1030 Site 1 Yes Site 1
Site 2 Yes Site 2
Site 3 Yes

1040 Site 1 Site 3 Yes
Site 2 Yes


Please let me know if this is any clearer. I cannot find a way to attach the file so I provided an example above.


Reply ↓  Report •

#7
January 4, 2018 at 12:23:09
Zipshare is blocked from our intranet. I can't access it.

If you provide your email address I can Wetransfer it to you. No need to register.

message edited by Daniej


Reply ↓  Report •

#8
January 5, 2018 at 09:53:41
DerbyDad03 please advise how you would like to proceed.

Reply ↓  Report •

Ask Question