How do I lookup a cell in another sheet based on a common label?

[some_evil]

Hi All,


I have several tables with water meter readings in them, and I am trying to create a single summary table that shows the water meter readings over the past several years.


Not all tables have the same number of columns, and they are not in the same order, so a vlookup wont work (as far as im aware) as I would have to write it different for each sheet.


I am wondering if I can write a formula to read the water meter number from the current table [Summary], look at the table called [2014], look in the column labeled 'Meter_No' for each meter number, read the line of that meter number and bring back the 'consumption' value into the original summary sheet.


Here is how I want my Summary sheet to look:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Meter_No[/TD]
[TD]2014 Consumption[/TD]
[TD]2013 Consumption[/TD]
[TD]2012 Consumption[/TD]
[/TR]
[TR]
[TD]Z10587[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z104873[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T321475[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here is how the 2014 Sheet looks:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Meter_No[/TD]
[TD]Consumption[/TD]
[TD]Read_Date[/TD]
[TD]Route[/TD]
[/TR]
[TR]
[TD]Z10587[/TD]
[TD]105[/TD]
[TD]10/3/2014[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]T21458[/TD]
[TD]150[/TD]
[TD]10/3/2014[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]




Here is how the 2013 Sheet looks:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Consumption[/TD]
[TD]Read_Date[/TD]
[TD]Meter_No[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]135[/TD]
[TD]1/3/2013[/TD]
[TD]Z10587[/TD]
[TD]LHS[/TD]
[/TR]
[TR]
[TD]87[/TD]
[TD]1/3/2013[/TD]
[TD]T21458[/TD]
[TD]Driveway[/TD]
[/TR]
</tbody>[/TABLE]


and so on. Can someone please suggest a way to do this? All the tables have the same column names (Meter_No and Consumption) if that helps. The other columns are irrelevant


Thanks

 

[Fazza]

Hi. What about a pivot table? Would that be OK? Fazza

 

[some_evil]

Can you create a pivot table that reads a dozen tables?

 

[Fazza]

Yes. One, a dozen or a hundred. No problem. www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html

 

[some_evil]

Yes. One, a dozen or a hundred. No problem. www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html

Hi Fazza, unfortunately after a couple of hours trying to get the suggestion in the suggested thread to work, I am still no closer to being able to do it.

Are there any other suggestions anyone can think of? I am really stumped with this one.

Someone here at work suggested an INDEX/MATCH but I have been unable to successfully apply that one...

Here is a sample set of my data, keep in mind I only need to keep Meter and the consumption for that period...

Cheers

 

[Fazza]

Hi,

The sample data looks to be the same structure on all three source data sheets. It'd be handy if there is such consistency to instead put the data on a single sheet - then just have a normal pivot table. Though I'll assume that is not suitable, as per the original post.

If it were me, I'd probably write some VBA for this. I will however describe a manual way to do the pivot table. And I suspect this is already described in the reference link I gave earlier, or a post it links to.

This answer is for your specific data. This is from memory so it may not be exactly right. Save the data file. From a new file, ALT-D-P to start the pivot table wizard, choose external data source at the first step, Get Data, OK, Excel files, browse for your data file, OK, if you get a message about no visible tables just OK to acknowledge then via 'options' choose 'system tables' to see the worksheets. Pick one as a data source. Select the two fields you want - Meter No, Consumption. Follow the wizard to the end and then take the option to edit in MS Query. Via the SQL button change what you see (it is only text & you can copy & paste from below to replace the original text) to

SELECT [Meter No], Consumption, '2014-2' AS Period
FROM [2014-2$]
UNION
SELECT [Meter No], Consumption, '2014-1' AS Period
FROM [2014-1$]
UNION
SELECT [Meter No], Consumption, '2013-3' AS Period
FROM [2013-3$]

OK to enter this, OK to acknowledge a message about 'not being able to represent graphically' if you get that, then the 'open door' icon to exit MS Query. Complete the pivot table. Resultant worksheet can be moved into the source data file if you want.

Again, some code to automate this would be good if you have to do this more than a couple of times. Or there are other approaches that you might investigate.

regards