Jul 14 2021 03:47 AM
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.
Jul 14 2021 03:55 AM
No, that is not possible.
Jul 14 2021 10:50 PM
Jul 15 2021 12:03 AM
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...
Jul 16 2021 03:14 AM
Jul 16 2021 03:57 AM
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
Jul 17 2021 07:27 PM
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.
Jul 17 2021 11:03 PM
Jul 20 2021 06:51 PM
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.
Jul 20 2021 09:40 PM
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
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