Forum Discussion
Need help with selecting specific worksheets and copy data based on user input.
Hi Everyone!
I have the following use case. I have an excel workbook with multiple unformatted(No Tables) worksheets. The target sheets contains data below column headers(namely Date, ID, Name, Location).I need to allow the user to select specifc worksheets(Multiple selections allowed). Once the user has selected the sheets, I need to allow the user to input a Date and ID, which needs to be cross referenced against the rows in selected sheets. Once that is done, I need to copy the data from the selected worksheet rows rows containing the same Date and ID as per the user inputs of Date and ID and paste it into a new worksheet.
How can I work my way around it?
Thanks everyone for reading.
Why no Tables or at least named Ranges. Without that, how do you know where to find data?
Do you absolutely need a hard copy of the filtered records or would a formula do?
Power Query is pretty good at appending and filtering Tables though setting up the filters would probably require M to set up the filters from the master sheet.
Otherwise, provided you are using 365, it is possible to combine references to the tables within a single array by using Thunks. You could build a list of table names or numbers and return them as an appended list before filtering to meet you Date and ID criteria.
Create a new sheet. Move all data from the sheets in this new sheet. Delete the old sheets.
Format as table.
Filter the table:
- Filter
- Slicer
- FILTER()
- Or work with a pivot table.
