Create Summary Page Referencing Multiple Sheets

[mjb0810]

Hi All,

New to the forum but really hoping someone can help.

I've been tasked with creating a new excel sheet for my company which can be implemented across all of our offices, so we can keep better track of the hardware we are installing on our projects.

Each type of hardware (for the use of this example we shall just say A, B & C) has it's own tab/sheet in excel where we want to store the details for each device. Each sheet will have different columns based on the hardware type but there will be a few columns that contain the same information on every sheet. As an example every device will have a name, device type, location & IP address detailed regardless of which tab it is on.

What I've been asked to do is create a summary sheet that will detail all of the "common" information in one place to make it easier for the PM's to look at and if needed to issue to the customer without overloading them with information.

I'll upload a basic sheet for you guys to see what I mean. In the example there are 4 tabs (A, B, C & Summary). There are 4 columns for the information on each tab (Name, Device Type, Location & IP Address). Each hardware tab (A-C) has rows 1-20 that could contain the information. I then need the summary tab to list all of the information from all 3 tabs in a single list, but to ignore any blank cells. So if there are only 5 rows on tab A, 3 on tab B & 2 on tab C, I need the summary sheet to show this in the first 10 rows, But if someone added a 6th row to tab A I need the summary to update the list.

I'm not sure this is even possible but hopefully it can be done either with a formula or a VBA code that I can link to a button on the summary tab to go and pull the information I need back from the hardware tabs.

[AliGW]

Welcome to the forum.

PowerQuery is made for this. Load each of the individual tabs to connection only (can be done in one go as long as your tables are real tables or named ranges), then append all of them together - filter out the data that you don't want in the append query.

[mjb0810]

Would it then update automatically once it's created or would you have to do it manually each time?

This is going to be distributed throughout all of our office worldwide and for want of a better word needs to be idiot proof as some of the people using it will not be technical in any way so if it needs to be run manually each time it could get a little complicated for them. I'm sure I can create a guide if needed but the ideal solution would be for it to auto-update as/when the information is added or on demand using a simple one click button if using a macro.

[AliGW]

Yes - the query needs refreshing, but you can set it to do so automatically when the workbook opens and can be set to refresh automatically up to once a minute. A small bit of VBA could be added to detest changes that would then trigger a refresh.

Provide some dummy data instead of empty grids and I can show you.

[mjb0810]

Great thank you, I've included some dummy data in the attached.

[AliGW]

From what you said in your opening post, I don't believe that is truly representative.

Please provide three sheets with EXTRA data that you won't want in the summary, and on the summary page, manually mock-up the bits you want to extract. Then I will have a look, otherwise we are going to have a shifting goalposts situation. Been there ...

[mjb0810]

Ah sorry, crossed wired there I think on the example you were after

Updated one attached.

[AliGW]

OK - I can work with that, even through you didn't add the mocked-up result data ... Let's hope I guess right!

Give me 10 minutes ...

[mjb0810]

Thanks, and sorry. I just need it to collate the information from the common tabs on the summary page.

[AliGW]

OK, so here's what I did:

1. Convert the ranges in each sheet (A, B and C) to proper tables (that will grow and shrink as you add and delete rows - this is the most important prep step) - each must have a name beginning with Table - look at Formulas | Name manager to review these.
2. Get Data (Data ribbon) | From Other Sources | Blank Query.
3. Open the Advanced Editor (on the ribbon in the PQ editor) and paste the following code into that window, overwriting everything there:

Please Login or Register  to view this content.

4. Click Close & Load to choose where to put it.

All you have to do know is click Refresh (Data ribbon in Excel itself) to update your summary. You can set the query to refresh every minute automatically if you wish.

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	Device Name/Number	Device Type	Location	IP Address
2	A1	PC	Finance	10.10.10.1
3	A2	LAPTOP	HR	10.10.10.2
4	A3	PRINTER	Design	10.10.10.3
5	A4	PC	Warehouse	10.10.10.4
6	A5	LAPTOP	Engineering	10.10.10.5
7	B1	CCTV	Reception	10.10.10.6
8	B2	CCTV	Store Room	10.10.10.7
9	B3	CCTV	Warehouse	10.10.10.8
10	Print Server	Windows Server	IDF Room	10.10.10.9
11	File Server	Windows Server	IDF Room	10.10.10.10

Sheet: Summary

[mjb0810]

Thanks,

I've just added a couple of lines to tab A and it's not expanding the table or updating the summary.

[AliGW]

You probably didn't do this:

All you have to do know is click Refresh (Data ribbon in Excel itself) to update your summary.

I also said this:

You can set the query to refresh every minute automatically if you wish.

You can do this in the query properties.

[AliGW]

You have added the rows BELOW the table. You need to tab from the last entry in the table to insert a new row.

You can also type into the first cell of the new row and then TAB to the second column, but if you just move your cursor manually, it won't be recognised as part of the table.

[mjb0810]

I clicked refresh on the Data & Query ribbons, it didn't update. It also didn't expand the table/formatting down on tab A when I added the new info.

[AliGW]

See post #13. It's just a case of learning how tables work.

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	Device Name/Number	Device Type	Location	IP Address
2	A1	PC	Finance	10.10.10.1
3	A2	LAPTOP	HR	10.10.10.2
4	A3	PRINTER	Design	10.10.10.3
5	A4	PC	Warehouse	10.10.10.4
6	A5	LAPTOP	Engineering	10.10.10.5
7	A6	PC	Warehouse	10.10.10.4
8	A7	LAPTOP	Engineering	10.10.10.5
9	B1	CCTV	Reception	10.10.10.6
10	B2	CCTV	Store Room	10.10.10.7
11	B3	CCTV	Warehouse	10.10.10.8
12	Print Server	Windows Server	IDF Room	10.10.10.9
13	File Server	Windows Server	IDF Room	10.10.10.10

Sheet: Summary

[mjb0810]

You have added the rows BELOW the table. You need to tab from the last entry in the table to insert a new row.

I edited the information in the bottom row of the table, when using them (tables) in the past as soon as I've entered data in the last row it's added a new one, never had to use tab before. Guessing they've changed it since last time I used it.

[AliGW]

No, I don't think they have. It works that way so that you can add subtotal lines without invoking a table row.

Anyway, it's really easy - just a slight adjustment in operation needed.

I have managed to train Luddites to do it properly, so it shouldn't be a deal-breaker.

PS The ENTER key works, too, in terms of adding the row, but moves you down as well.

[AliGW]

So, if you are happy that this takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[AliGW]

Thanks for marking the thread as solved. Any feedback? I presume you've now managed to get it working and have added the automated query refresh?

[mjb0810]

Yeah it's working, thanks.

[AliGW]

Great. Thanks - it's always nice to round off a thread for the sake of those who have offered the free help.