×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Hi, I am trying to get my head rou

Hi, I am trying to get my head rou

Hi, I am trying to get my head rou

(OP)
Hi,
I am trying to get my head round INDEX MATCH for the first time but am not doing very well

Could someone help me with this please

I tried to use =INDEX('Master List'!E5:E5:E5020,MATCH(E5,IF('Master List'!AQ5:AQ5020=C3, 'Artwork and Sculpture'!C3:C150),0))
But the result is just "value"

The column in the sheet 'Master List'!E5:E5020 has the name of the person I am trying to look up

The column in the sheet'Master List'!AQ5:AG5020 has records with a Unique ID Number

The Column in the sheet 'Artwork and Sculpture'!C3:C150 has the matching Unique ID Number

I want to look up the name of the person from the Master List and add the name to the cell on the 'Artwork and Sculpture' sheet next to the "C" Column in the "B" Column

Example: Franz Joseph Hayden's name is in the column in the sheet 'Master List'!E5:E5020
The column in the sheet 'Master List'!AQ5:AG5020 has records with a Unique ID Number in this case the Unique ID Number is "1"
The Column in the sheet 'Artwork and Sculpture'!C3:C150 has the matching Unique ID Number of "1"
So 1 needs to match to 1 and Franz Joseph Hayden to be the result in the sheet 'Artwork and Sculpture' in cell B3

Any help would be great appreciated

RE: Hi, I am trying to get my head rou

Hi,

In sheet Art and Sculpture, does any artist appear more than once?

It's odd that that sheet has 147 entries while your Master sheet has 5000???



Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi SkipVought,

The Tile of Art and Sculpture slightly misleading
The name of the person is the Musician

Art and Sculpture sheet contains the information about his or her Portrait, or Bust
Yes the Musician could have up to two items associated with them

Yes the Art and Sculpture sheet only has a small number of records compared to the number of musicians

RE: Hi, I am trying to get my head rou

So you want to see the notation from the Art and Sculpture sheet next to each name in your Master List column E?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi SkipVought,

I was really thinking that, I would like put the name from the Master List next to the notation information on the Art and Sculpture sheet, as the Art and Sculpture sheet is additional information associated with the name on the Master List.

Do you think that is the correct way to do this?

RE: Hi, I am trying to get my head rou

One sheet has about 150 entries while the other has over 5,000.

You apparently have a one-to-many relationship. So you can't do what you want to do, or else I'm totally misunderstanding your data structure.

Or are you saying that here are about 150 entries for Franz Joseph Hayden on A & S ?

I was Hayden in Bach trying to get a Handel on things from the Liszt I had.


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi SkipVought,

The Master List, has the name of the musician just once
The Art and Sculpture has the information for many different musicians and some musicians have multiple Items e.g. a Portrait and Bust
so there would be two rows of information about each item both connected to a single musician.
so it would be a one-to-many relationship

RE: Hi, I am trying to get my head rou

So, why only 3:150? Does not make sense when you have over 5,000 Names?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi SkipVought,

Yes I appreciate it sounds odd
the 5,000 names are a membership list
the 150 items are simply owned by the music society, so there are 130 Portraits of different members and 12 busts of different members and some have a Portrait and a Bust
I hope that helps

RE: Hi, I am trying to get my head rou

So is Liszt on your list?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi SkipVought,

No

RE: Hi, I am trying to get my head rou

So not every Member has a artifact?

Do any members have multiple artifacts?

What is the maximum number of artifact records for any member currently?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi,

So not every Member has a artifact? - Correct

Do any members have multiple artifacts? - Yes

What is the maximum number of artifact records for any member currently? - 3

RE: Hi, I am trying to get my head rou

What version of Excel are you using?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

Assuming some things and simplifying an example, I offer 2 solutions: 1 for Excel 365 and 2 for other versions greater than Excel 2007.

Both employ the use of Structured Tables. Below is a screenshot of example 2


Both examples use the OFFSET function to grab the array of artwork associated with the composer's ID.

The difference between examples 1 & 2 is the use of the UNIQUE() & TRANSPOSE() functions in 1, while 2 uses an index in ROW 1 and the INDEX() function.

If the assumptions I assumed are correct, you'll need to make adjustments to accommodate the formula to your data structure and probably make your tables Structured via Insert > Tables > Table.

Solution 2 link below.

Musical puns are my f.
It was my conviction that over his lifetime, Franz Liszt compiled a compendium of a conglomerate of composers' compositions. Musical connoisseurs, therefore, lust for the last of Liszt's lists, lest Liszt's lists be lost.


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Thank SkipVought
Sorry for the delay, I have been away for a few days
Looks great
I will check it out and let you know if I have any other questions
Thanks for your help

RE: Hi, I am trying to get my head rou

(OP)
Hi SkipVought,

I am trying to get your code to work in my Spreadsheet, but am not doing very well, I have changed a few things so I would now like to use your code to pull various items of information related to the person on to a Profile Page.
So when you select from the dropdown list, populated from column A on the 'Master List' Sheet. This will then list all the ID's then using =IFERROR(VLOOKUP(H2, 'Master List'!A5:H5009,8, FALSE),"<<< Add the Person's ID") so I can just select one ID Number from the list to show the name of the person in Cell I2 on the Profile Page.

=IFERROR(INDEX(OFFSET(tArtwork[Item],MATCH(tMaster[@ID],tArtwork[ID],0)-1,0,COUNTIF(tArtwork[ID],tMaster[@ID]),1),H$1),"")

tArtwork = 'Artwork and Sculpture' Sheet
Item = column C on the 'Artwork and Sculpture' Sheet
ID = column A on the 'Artwork and Sculpture' Sheet

tMaster = 'Profile Page' Sheet
@ID = H2 on the 'Profile Page' Sheet

I would like your H$1 to use the code to populate A13 on the 'Profile Page' Sheet
I would like your I$1 to use the code to populate A14 on the 'Profile Page' Sheet
I would like your J$1 to use the code to populate A15 on the 'Profile Page' Sheet

So when the ID is selected in H2 on the 'Profile Page' Sheet, the related information is populated in A13, A14, A15 on the 'Profile Page' Sheet

I hope this makes sense and you are able to help me
Thanks again for your help

RE: Hi, I am trying to get my head rou

(OP)
Hi SkipVought,
Just to let you know I am using Excel 2021

RE: Hi, I am trying to get my head rou

You can see in my uploaded example, this is a much simpler solution than I previously imagined.

I used an in-cell Data > Validation > List for the dropdown in H2. The caveat when using Structured Table names is to add a Range definition, in this case rID for the tArtwork[ID] to use in the dropdown reference.

FYI: I never, no, never, what, never?, hardly ever, use the VLOOKUP() function. I almost always use INDEX & MATCH being that VLOOKUP requires the lookup column to be the first column in the reference range, which in my world, rarely occurs.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi SkipVought,

In the Table Design I have renamed my two tables tMaster and tArtwork
When I first opened your table I could see
=tMaster[[#Headers],[ID]]
=tMaster[[#Headers],[Name]]
=tArtwork[[#Headers],[ID]]
=tArtwork[[#Headers],[Column1]]
=tArtwork[[#Headers],[Item]]

But now I can't, I must have been in a setting somewhere.
I tried to add these into the Header cells, but that is not working
How and where do I add this information?
Sorry to keep asking you all these questions.
Thanks for your help

RE: Hi, I am trying to get my head rou

I don't know what you mean by "opened your table"

If you reference the elements of the Structured Table from outside the table, that is what you might see.

If the table somehow became UNstructured, then you need to make if structured via Insert > Tables > Table

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi SkipVought,

I think I have sorted it,
It did not like Merged cells, so I had to remove the merged cells
so thank you so much for all your help

Now is it possible to Modify =OFFSET(tArtwork[ID],MATCH(H2,tArtwork[ID],0)-1,2,COUNTIF(tArtwork[ID],H2),1) show all the information in the cells to the right
A13 - B13 - C13 - D13 etc
A14 - B14 - C14 - D14 etc
A15 - B15 - C15 - D15 etc

RE: Hi, I am trying to get my head rou

What columns in tArtwork correspond to columns B, C, D etc in Profile sheet?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi SkipVought,

C has the "Item" info - This is the information we have retrieved for A13, A14 and A15
So the next columns are D to S

RE: Hi, I am trying to get my head rou

Maybe this in A13 and copy/paste across...

A13: =OFFSET(tArtwork[ID],MATCH($H$2,tArtwork[ID],0)-1,COLUMN()+1,COUNTIF(tArtwork[ID],$H$2),1)

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
It gave me a message - "Spill", it said it would put the info in the neighbouring blank cells, so I said ok
But then there was nothing in the neighbouring blank cells

RE: Hi, I am trying to get my head rou

...and be sure that NOTHING, not even SPACES, are in ANY of the cells in the MAX range that the arrays will fill

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Sorted
Just realised that you had the Formula in A13, A14, A15 Etc
Brilliant, thank you so much

RE: Hi, I am trying to get my head rou

My formula is only in row 13.

The OFFSET() function returns an array. In Excel365 and Excel 2021, I believe, handles rendering arrays into the appropriate cells. It is those rendered array cells that cannot contain ANYTHING, not even SPACES.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Sorry
I mean A13, B13, C13 Etc
So Yes just Row 13

RE: Hi, I am trying to get my head rou

Good! Glad it's working the way you need.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi Skip,

I took your advice and removed the vlookup and replaced with Index/Match

This works most of the time =INDEX(tMaster[Name],MATCH(E2, tMaster[ID],0))
I was getting a result some times of 0
So I changed it to =INDEX(tMaster[Name],MATCH(E2, tMaster[ID],0))&""
Which cured the problem of the result of 0
but does not work with Dates so used =INDEX(tMaster[Name],MATCH(E2, tMaster[ID],0)) for dates which worked but then if the date cell was blank it returns 00/01/1900
I have tried =IfError(INDEX(tMaster[Name],MATCH(E2, tMaster[ID],0)), "") but returns 00/01/1900 if the cell is blank and the correct date if the cell has a date

Can you help please?

RE: Hi, I am trying to get my head rou

(OP)
Hi Skip,

Regarding =OFFSET(tArtwork[ID],MATCH($H$2,tArtwork[ID],0)-1,COLUMN()+1,COUNTIF(tArtwork[ID],$H$2),1)

Is there away that would allow the information to be entered into a Merged Cells so I don't get the "Spill" Error

Thanks for your help

RE: Hi, I am trying to get my head rou

(OP)
Sorry Skip,

Regarding =OFFSET(tArtwork[ID],MATCH(H2,tArtwork[ID],0)-1,2,COUNTIF(tArtwork[ID],H2),1)
Thanks

RE: Hi, I am trying to get my head rou

1) Regarding dates: I would use Conditional Formatting to make the Font Color the same shade as the Cell Color when the Cell value is 0.
FAQ68-5827: Understanding Dates and Times & why they seem to be so much trouble?

2) Regarding merged cells: I can't visualize what problem you're having here. But in general, merged cells present a challenge. At a minimum, I'd need to observe what you're referring to, like a screenshot. Ideally, I'd like to have a representative example in an Excel workbook in which I could maneuver.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi Skip,

I have attached a representative sample.

1.) Dates
In the Cells on the Profile Page you will see I have used =INDEX(tMaster[Date of Birth],MATCH(E2, tMaster[Master ID],0)) &"" for all the cells except the dates
when I use =INDEX(tMaster[Column Name],MATCH(E2, tMaster[Master ID],0))&"" for a date cell - See "Burial Date" it does not return the correct Date format
when I use =INDEX(tMaster[Column Name],MATCH(E2, tMaster[Master ID],0))&"" if the cell is blank on the Master List then it is is blank on the Profile Page
If I use =IFERROR(INDEX(tMaster[Column Name],MATCH(E2, tMaster[Master ID],0)),"") I get 00/01/1900 if the cell is blank on the Master List sheet

Further down on the Profile page rows 15 to 25 where it returns the results from the tArtwork sheet
In cell A17, I have used =IFERROR(OFFSET(tArtwork[Master-ID],MATCH($E$2,tArtwork[Master-ID],0)-1,COLUMN()+1,COUNTIF(tArtwork[Master-ID],$E$2),1),"No Information") I added an IfError to return "No Information" when there was No Information

In Cell C17, I have used =IFERROR(OFFSET(tArtwork[Master-ID],MATCH($E$2,tArtwork[Master-ID],0)-1,COLUMN()+1,COUNTIF(tArtwork[Master-ID],$E$2),1),"")This time I added the IfError to return ""

In Cell F17 I have used =IFERROR(OFFSET(tArtwork[Master-ID],MATCH($E$2,tArtwork[Master-ID],0)-1,COLUMN()-1,COUNTIF(tArtwork[Master-ID],$E$2),1),"") This time I added the IfError to return ""
But I had to modify the Column() from +1 to -1 as the +1 returned the wrong Column on the tArtwork sheet

"Spill Error"
If you now merge A17 and B17 or C17 and D17 or F17 and G17 you get the spill error

The Idea of the Profile Page is to have a quick over view of all the information in one place - Is this the best way to do this?
I hope I have made myself clear for you
Any Questions let me know

RE: Hi, I am trying to get my head rou

As I stated earlier, merging cells presents some challenges. This is one of those situations in which merge cannot be used.
Be advised that the Column Offset in the OFFSET() function (argument 3) must correspond to the column number minus 1 in the tArtwork table.

Regarding dates prior to Jan 1 1900, date formatting has no effect. Rather, since all your dates are prior to 1900, change the Number Format to General and concatenate a "" to each formula.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

...and in addition

You have a relatively complex set of requirements that we got in dribs and drabs. We just got a peek at your data structure and content.

If I had additional DATA for at least 1 more composer, I'd be able to test another interactive solution for displaying Item, Creator & Title in individual columns using the Camera feature. This would involve VBA, where changing the SelectedID, the Range Name I assigned to E2, would filter tArtwork and display the filtered result on the Profile sheet.

I have used the Camera Feature in a similar circumstance, where I had selection data above the of selected data where the summary data columns did not fit the table data columns.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi Skip,

If your happy to do that for me, that would be wonderful, I will send you a new file with some additional data.

Talk soon

RE: Hi, I am trying to get my head rou

I've got an afternoon appointment in an hour. I might not get to this today.

But, Yes, I'd be happy to do what I can do.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

Quote (cneill)

Is this the best way to do this?

Based on the exchange here with Skip, looks like it is doable in Excel, but...
Wouldn't be better to do all of that in the data base? Access maybe ponder

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Hi, I am trying to get my head rou

(OP)
Hi Andrzejek,

Thanks for looking at the thread
I think Access would be better, but the society I am helping (Volunteering) don't have a licence for Access and they said they wanted it in Excel.
But a very good thought.

RE: Hi, I am trying to get my head rou

@Andy, from what I understand, the problem is not database specific but rather being able to display the selected data on the Profile summary sheet in such a way that is conducive with the data displayed above it.

The single occurrence per ID data at the top of the sheet is displayed with Column Widths that are significantly smaller than the Column Widths of the multiple rows of tabular data returned for any specific ID. So, IMNSHO, it's a DISPLAY challenge of representing heading data and detail data in a pleasing format all on one summary sheet, that is unless the OP has a different opinion than I.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Hi, I am trying to get my head rou

(OP)
Hi Skip,
Sorted out the Index/Match date problem with Blank Cells, works for all Pre and post 1900 Dates
so this =INDEX(tMaster[Name],MATCH(E2, tMaster[Master ID],0))
Becomes this
=IF(INDEX(tMaster[Name],MATCH(E2, tMaster[Master ID],0))="","",INDEX(tMaster[Name],MATCH(E2, tMaster[Master ID],0)))
Such a simple solution
Thanks for all your help

RE: Hi, I am trying to get my head rou

You can simplify and change technology for filtering required data. I enclosed a workbook with some modifications. It has 'changed' sheet with description of changes.
In general, I used:
- names, to simplify references,
- INDIRECT with table column references in data validation formulas,
- power query queries to filter artwork by master (power queries available since Excel 2016),
- event driven macro to update queries when master changes (it needs a trigger, the workbook was in macro-enabled format, so I added one).

So, when you enable macros and connections (to internal data in fact, power query works this way), a selection of master fires event procedure and updates queries.
Take care of table sizes, even with format continuation, one of tables did not contain full data.
Queries can overwrite data below.

combo

RE: Hi, I am trying to get my head rou

(OP)
Hi combo,

Thank you
There is a lot to take in with all these changes (some are new to me)
So I will make the changes to my Excel work book one by one, then If/when I will have any questions I will get back to you
I appreciate all your help

RE: Hi, I am trying to get my head rou

(OP)
Hi combo,

How do I change the "Full Profile" to show Table Design and Query tabs
I tried Insert - Table - Table but it keeps creating Headers which I can't delete

Thanks

RE: Hi, I am trying to get my head rou

If you mean the three tables - under 'Artwork and Sculpture 2' - they are three separate query results (cols: A, C and F). You can go Data>Show queries (can depend on Excel version). From the ribbon or queries side bar after right-click, you can edit the query in power query desktop.
In fact that are query results in tables.

combo

RE: Hi, I am trying to get my head rou

(OP)
Hi combo,

Sorry
Just getting my head round this
On the Full Profile sheet
If I clink in cell E2 the "Table Design" option pops up on the ribbon at the top
Then when I click on the "Table Design" option I can see the new table name tMasterSel
I don't have this option on my Full Workbook yet, so I need to add this, how do I do it?
I tried Insert - Table - Table but it keeps creating Headers which I can't delete

I hope this makes sense now

RE: Hi, I am trying to get my head rou

Yes, I added one row and one column table. It is because in Excel the interaction between workbook and power query environment is via tables.

So I need an Excel table with data, next a PQ query imports this table to PQ environment. The 'tArtwork' table is imported in the same way. The new query with inner join extracts data for selected master and returns only single column (there are three copies for three required columns). Each query returns data to table in worksheet.


combo

RE: Hi, I am trying to get my head rou

(OP)
Hi Combo,

Thank you
Can I ask for some hand holding please, I lot of what you have done is new to me and I have only just upgraded from Office 2010 to 2021 so there is a lot of new stuff that I am still learning about.
Re: Full Profile sheet
You Said: I added one row and one column table
Can you explain how I do this, so I can replicate what you have done in the Master Workbook?
Many Thanks

RE: Hi, I am trying to get my head rou

Select cell E2. Insert table, confirm that your table has header. Excel adds structured table; from the table menu you can change name and table layout. I choose top-left option from the gallery - no formatting, so your original formats were not changed (https://support.microsoft.com/en-us/office/create-...).

A new feature in Excel (since Excel 2016) is a Power Query com addin, for data transformation. If a data source is in excel workbook, it has to be a structured table. When you select a cell inside such table and add a query, you will land in PQ desktop (https://support.microsoft.com/en-us/office/create-...).
A query in PQ is a series of transformations, with filtering, unpivoting, merging tables, creating custom columns, deleting columns, functions, etc. The result can be returned to worksheet (in table) or stay for future processing. The steps in PQ query are recorded, so it is easy to start for the beginner. In advanced view one can see the code behind the query - M language was created for the use in PQ. M offers far more power than basic recorded and modified actions. It can access many different data sources, as Access, SQL Server, file system, xml, pdf tables, and many other.

MS in the second link mentioned the Data Model (I haven't used it in in the uploaded workbook). It is possible to create relations between tables added to the model, it can be queried, and pivot table using multiple tables in DM can be created. With cube functions aggregations similar to pivot table can be generated (it is possible to convert pivot table to a set of cube functions and leave one cell only, for instance). Another com add-in, Pover Pivot, is used to manage the data model with its own DAX language (I don't know if it is available in all Office versions, I have 2016 Professional and it was here, but not in Standard).

So a lot changed in the meantime. Personally I think that the best new Excel feature is Power Query.

combo

RE: Hi, I am trying to get my head rou

(OP)
Hi Combo,
In my Master Workbook
I made a copy of the Full profile Sheet and renamed the current one as Full profile Old (full back position if I do something wrong)
Clicked on E2 Full profile Sheet and Inserted the table, I confirmed the table was located at $E$2, I confirm my table has a header, choose top-left option from the gallery - no formatting.
Now cell E2 has two dropdown arrows, one as a Header arrow and one for the dropdown list
My updated sample you sent me only has the one for the dropdown list
So why am I getting a header when I confirmed I already had headers?
Many Thanks

RE: Hi, I am trying to get my head rou

In the table formatting ribbon, untick filters.

combo

RE: Hi, I am trying to get my head rou

(OP)
Hi Combo,

Sorted Thank you
Re Changes
1.) Resize 'tArtwork' table to cover full data - How do I do this?
2.) Master-ID' field in 'tArtwork' table DV formula changed to =INDIRECT("tMaster[Master ID]") - I can't find this change you made, where is it?
Thanks

RE: Hi, I am trying to get my head rou

1) there is a small blue dot at the right-bottom corner of the structured table. Drag it to resize the table. Alternatively, when active cell is inside the table, the change table size option is available from table design tab in the ribbon.
2) DV - data validation, see formula applied.

If you have never before workeed with structured tables, try it in new workbook. Write a small set of data in tabular format, with text, dates and and numbers. Convert it to table. Experiment with table styles and options. Add/remove totals row. Add new data in row and see how the table resizes automatically. Write formula in adjacent column referring to the same row - see how excel adds new column and fills it with structured references; repeat this step in cell in other column - excel will create external structured reference, with table name, in a single cell.
Try slicers - alternative to filters in tables and pivot tables. Create pivot table - note that data range refers to table name, so no problem with changes in source size.

combo

RE: Hi, I am trying to get my head rou

(OP)
Hi Combo,
I think I have now made all your changes
When I go into the Power Query
It is telling me
Expression.Error: The column 'Master ID' of the table wasn't found.
Details:
Master ID
In the code it says = Table.TransformColumnTypes(Source,{{"Master ID", Int64.Type}})

Also my workbook is now linked to the sample data workbook, so I have now unlinked this, as I need it to be standalone wookbook
Also after I added =@INDEX(tMaster[Name],@IndexMasterID) on the Profile page next to cell E2 it changed to #Ref!
and the code stops at >> If Not Intersect(Target, Me.Range("MasterSel")) Is Nothing Then

I must be missing something, any Ideas?

RE: Hi, I am trying to get my head rou

(OP)
Hi Combo,

Forgot to say
When I add =INDIRECT("tMaster[Master ID]") to the data validation for E2 on the profile page it gives me a message to say "The Source currently evaluates to an error, do you want to continue Yes or No
What have a missed

RE: Hi, I am trying to get my head rou

So it is likely that the name of the column was changed. Power Query formulas are case sensitive.
To debug PQ queries, select query, select the first action in query, and check the resulting view. If it is ok, select next action, etc.

I don't understand the '@' in =@INDEX(tMaster[Name],@IndexMasterID). INDEX is an Excel function, IndexMasterID is a name, in both cases '@' shouldn't be there.
'@' is used to reference a cell in column in the same row as calling formula.

combo

RE: Hi, I am trying to get my head rou

(OP)
Hi Skip & Combo,
Just a quick thank you for all your help

Combo
I got your Power Query to work, which is great, but in the end I settled for a more simplistic approach and used the =Filter function
Which is easier for me to implement.

One other question to you both
If I use the "Master ID" on the tMaster table to filter the records, is it possible (if there is a corresponding "Master ID" match on The tArtwork table) to filter the tArtwork table at the same time?
Your thoughts would be great

RE: Hi, I am trying to get my head rou

You need VBA. There is no special event for filter change. Instead, you may create formula to calculate sum of 'Master ID' visible entries (default for table's totals row). Then Calculate event can catch the filter change. Next you need to code: (a) visible ID(s), (b) apply (a) in to build filter in second table.

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close