Tiffany Gray
asked on
Returning Data from Multiple Sheets through a Dropdown
I am using Excel 2016 and have a large workbook with 110 sheets. I am only interested in the data on 72 on these sheets. We are currently formatting the sheets to look identical in the column headings and the column placement.
Our goal is to be able to create a sheet that we can choose a name from the Owner column and have it return
Owner
ITM
Application
Sheet Name
We want to then copy and paste this into another workbook that we can send to the Owner so they can verify that the application is valid. If they say it's not valid we need the sheet name so we can go and either update to indicate that it is no longer valid or delete the line.
I am attaching the file as reference. Can anyone help me and I am willing to discuss via a phone call.
Thanks
Tiffany
9K-Tracker-7.23.18.xlsx
Our goal is to be able to create a sheet that we can choose a name from the Owner column and have it return
Owner
ITM
Application
Sheet Name
We want to then copy and paste this into another workbook that we can send to the Owner so they can verify that the application is valid. If they say it's not valid we need the sheet name so we can go and either update to indicate that it is no longer valid or delete the line.
I am attaching the file as reference. Can anyone help me and I am willing to discuss via a phone call.
Thanks
Tiffany
9K-Tracker-7.23.18.xlsx
ASKER
Thanks for the help. When I downloaded it I got many errors upon opening so I am not sure it opened correctly. It never asked me to accept macros.
I am attaching a more concise workbook and sheets. We are only working with the ones in this book.
This looks like a good start so let me try to explain what I had envisioned. On my attachment you will see a sheet called Output. This is where the results would be after the search from the 72 sheets. We are only interested in these columns that are outlined on the Output sheet. The Sheet labeled Owner List would be a drop-down pick list of the Owners found in the sheets. Yes there are variations of a name and they will be fixing those in time. They want to search for Bradley, Amy and have only her information come back. The idea so to send her that list to verify the applications.
Also the row number that you added will be a HUGE help in location that line withing the sheet.
We wanted this to be dynamic so that should they make changes on a sheet and then that change would be reflected by running the macro again.
I want to thank you for taking time out to look at my issue and to try and help find a solution.
Tiffany
9K_Tracker_8.08.18.xlsx
I am attaching a more concise workbook and sheets. We are only working with the ones in this book.
This looks like a good start so let me try to explain what I had envisioned. On my attachment you will see a sheet called Output. This is where the results would be after the search from the 72 sheets. We are only interested in these columns that are outlined on the Output sheet. The Sheet labeled Owner List would be a drop-down pick list of the Owners found in the sheets. Yes there are variations of a name and they will be fixing those in time. They want to search for Bradley, Amy and have only her information come back. The idea so to send her that list to verify the applications.
Also the row number that you added will be a HUGE help in location that line withing the sheet.
We wanted this to be dynamic so that should they make changes on a sheet and then that change would be reflected by running the macro again.
I want to thank you for taking time out to look at my issue and to try and help find a solution.
Tiffany
9K_Tracker_8.08.18.xlsx
On sheet "Owner List" select the name in C2.
That trigger the macro to run.
If not, check the security settings for macros.
9K_Tracker_8.08.18.xlsm
That trigger the macro to run.
If not, check the security settings for macros.
9K_Tracker_8.08.18.xlsm
ASKER
Ejgil -
WOW!!! That's amazing!
So the owner list was just a sample How can we capture ALL the owners from all the sheets to have a comprehensive list?
Can the same be done for ITM search?
Lastly what if I were to add a new sheet, would it be included in the look-ups?
This is very exciting to see.
Thank You!!
Tiff
WOW!!! That's amazing!
So the owner list was just a sample How can we capture ALL the owners from all the sheets to have a comprehensive list?
Can the same be done for ITM search?
Lastly what if I were to add a new sheet, would it be included in the look-ups?
This is very exciting to see.
Thank You!!
Tiff
All sheets are searched, so just add a new.
A new table for ITM names added in column E, and a macro using ITM as input.
Selection in C5.
Macro for tables update added.
Start with the Update button.
9K_Tracker_8.08.18.xlsm
A new table for ITM names added in column E, and a macro using ITM as input.
Selection in C5.
Macro for tables update added.
Start with the Update button.
9K_Tracker_8.08.18.xlsm
ASKER
This is AMAZING!
I cannot thank you enough for what you have done. The support here exceeded my expectation.
Ejgil from the bottom of my heart a huge THANK YOU!
I will let you know how it is received.
Tiff
I cannot thank you enough for what you have done. The support here exceeded my expectation.
Ejgil from the bottom of my heart a huge THANK YOU!
I will let you know how it is received.
Tiff
ASKER
Can we add 3 columns to the output titled Port, Connected Device and Device. They should be column A, B and C from the data sheets.
That should finalize our need
Thank You
That should finalize our need
Thank You
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ejgil could not been any better. Ejgil was awesome and highly knowledgeable in this subject. If there was a level above SME that would be Ejgil. Thanks so much for helping us out.
All the best
Tiffany
All the best
Tiffany
ASKER
Just one question....Can the "Output" sheet be arranged as follows
Owner ITM Port ID Connected Device Device Application Sheet Name Row
Just asking
Thanks again
Owner ITM Port ID Connected Device Device Application Sheet Name Row
Just asking
Thanks again
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again. This is complete. I tried but could not get it to work.
Your amazing!
Tiff
Your amazing!
Tiff
ASKER
This morning I moved the Macros to the attached workbook and got this error (see screen shot)
I figured I could just move it across, guess I was wrong.
Screen-Shot.JPG
9K-Tracker-8.13.18.xlsm
I figured I could just move it across, guess I was wrong.
Screen-Shot.JPG
9K-Tracker-8.13.18.xlsm
Don't know what that is related to.
When you get something like that, press Debug to see where the problem is.
I get some errors when opening, about missing links, so I will not correct and return the workbook.
You must copy everything in the module, including the lines above Sub FindData()
When I do that it works.
The macro for Tables update is missing.
When you get something like that, press Debug to see where the problem is.
I get some errors when opening, about missing links, so I will not correct and return the workbook.
You must copy everything in the module, including the lines above Sub FindData()
When I do that it works.
The macro for Tables update is missing.
ASKER
Yes thanks found the problem. The macro address was pointing to the other sheet. Removed the path and all is good.
Probably not exactly what you want, but a start.
Accept macros when opening the workbook
You start the macro by pressing the button on sheet Owners.
The macro loops the sheets.
Search for the header row, identified by "Port ID" in column A, within the first 20 rows.
If found, loops the columns, to find the columns for headers Owner, ITM and Application.
If Owner column is found, loops the rows below, and if Owner cell is not empty and not error, write the information to Owners sheet.
I have added the row number to the information, because some owners exist more than once.
Tiffany-Gray-9K-Tracker-7.23.18.xlsm