Values in multiple sheets pulled to 1 sheet as a summary

Resolved · Medium Priority · Version 365

Tue 25 Apr 2023, 10:54 Delegate Dale said...

Dale has attended:
Excel VBA Introduction course
Excel Pivot Tables course
Excel VBA Intermediate course

Values in multiple sheets pulled to 1 sheet as a summary

Hi
I have a workbook with multiple sheets. In each sheet I would like to place something in a row that will allow the information in that row to be pulled to a separate specific sheet, like a summary sheet of priority activities.
I'm not sure what formula I need to do this or do I need to use VBA to do this?
XLookup doesn't work as the data to pull the information will probably just be 2 letters like TP in a column for that information that I want copied or to be visible on the separate sheet.

Thu 27 Apr 2023, 10:24 Trainer Jens said...

RE: Values in multiple sheets pulled to 1 sheet as a summary

Hi Dale,

Thank you for the forum question.

I can see two solutions. You can write a lot of VBA or you can use Power Query. I would use Power Query.

It took me 5 minutes to create the solution you can find in the attached file.

In the attached file I have 3 source tables. If you type tp in the transfer column and click refresh, you will see that the record will appear on the output sheet.

All source tables must be in the table tool. Add the tables to Power Query close and load connection only.

Create an a append query in Power Query and append the source queries into one. Filter the transfer column to only display tp in the append query. Right click the transfer heading and click remove column.

Click close and load on the home tab.

You can see the steps I have done in Power Query if you open up Power Query and click on the different steps to the right in the Power Query window.

I do not know if you have any knowledge using Power Query in Excel but Power Query is a magically tool, which can handle and automate almost everything.

STL has a 1 day Power Query course, if it has any interest.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

append tp.xlsx

Sat 6 May 2023: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Multiple Lines of Text in a Cell

As an alternative to the Text Wrapping facility, type a word or two, press Alt+Enter to get a new line, type more text, and continue the process for as many lines as you need. Enter as normal when you have finished.

The line break is not affected by changing the column width, as text wrapping. To remove this you must edit the cell and remove the invisible character and replace with a normal space.

View all Excel hints and tips