Can you use table structured references with a 3D reference Same column through multiple tables.

Copper Contributor

Hi All,

I'm using Windows 10 home ver 20H2, 64 bit Op sys, build 19042.1052, with Excel ver 365.

 

I want to know if its possible to use a 3D reference in a structured reference formula.

I have one file containing several tables, each on a separate worksheet, with each having exactly the same column format. The only difference being the number of rows in each table, and the data they contain.

 

I am currently using the XLOOKUP function to lookup each member number from the Membership table, on Table01 class list, and return the attendance data in that class for that member.

=XLOOKUP(Table_Membership[@[Member'#]],Table01[[All],[Member'#]],Table01[[All],[Attendance]])

that works for Table01; but would like to know if its possible to reference a 3D formula syntax that uses Table01:Table20, for both the search criteria in column [Member] and the return criteria [Attendance].

 

Any ideas gratefully received.

9 Replies
you can do a data consolidate to make one big table00 for table01:table20, then do a search on table00

Hi @Debs_au 

 

Given that your tables appear to be consistenly named (Table01, Table02...Table20) with exactly the same columns it would be pretty straightforward to consolidate them all as a big Table with Power Query. You would then perform your XLOOKUP on that big Table

Is this an option for you? Do you need a sample?

 

Note: if you keep the same naming convention for your tables, the query can auto. pick any additional table, i.e. Table21, Table25...

Hi L z,
I was thinking along those lines to use a Pivot table. Is Power Query the same thing or different?

Hi @Debs_au 

 

In short: Power Query & Pivot Tables are 2 different things but the first can help the second...

 

Not clear to me what you ultimately want to achieve. However, assuming you want to build a Pivot Table from the data in 20 Tables, in any case you need Power Query to "take" your 20 Tables and consolidate them as 1 big Table that would be the Source for your Pivot Table

 

Note that if you build a Pivot Table out of the Power Query query, you don't necessarily have to load the big Table on a worksheet

 

Hope this clarifies things a bit

I ended up just using XLOOKUP with structured table references with '+' between them and using a 'Total' line at the end of the main table. Although the formula was a little unwieldy it worked well and will do for the moment until I read up on Power Query and can update what I have done to make it more elegant.

That's a short term way to go. Thanks for updating
If you need to sample to consolidate your tables with PQ, feel free to ask
Nice day...

I would really appreciate an example of a power query.

 

The main student worksheet has student numbers in column "a". Column "w" holds the result of the "xlookup" from all of the class attendance worksheets.

 

The subject worksheets (class attendance) have the student number in column "c" with class term attendance total in column "h", and individual attendance date columns "i" to "t" (which are summed in "h"). So the lookup on the main student worksheet (column "w") looks through each class sheet for the student, and adds the attendance (if any) to their attendance total in column "w" of the main studen worksheet.

 

 I hope this is enough info for you to give me an example of a power query. Thank you for your time.

Hi @Debs_au 

 

I'm afraid this can't work like this. You don't know Power Query (no problem at all) so if I put together a sample that doesn't reflect what you have on your side this won't help you

 

Below is (incomplete - goes until column T = [Attendance Date 12]) is supposed to represent one subject attendance table according to the columns you described

 

Demo.png

 

As highlighted in orange I can't determine what should go in those columns. This can be seen as irrelevant but this is actually quite important with Power Query

 

So, could you please take 5 minutes and anonymize the Student names in one of your tables and attach to your next reply a workbook containing that table (a few rows are enough)?

 

Thanks