Excel Forum

Select table headers

Pieter Cox

Member

Members

Forum Posts: 85

Member Since:
August 3, 2020

Offline

June 26, 2021 - 6:52 pm

Hello,

I am creating a workbook to distribute to a number of users, using Power Query to pull data from their own sources. The resultant table, with about 20 fields, should have different headers depending on the type of user and I'm looking for the simplest, most elegant, solution.

Example: Let us say that the users represent a Town or a City. I therefore want the headers to say either "Town Name, Town Population" or "City Name, City Population" etc. It's not always just a matter of replacing "Town" with "City", though.

Current position: I currently use VBA to test the content of a particular cell and then run a subroutine containing

With Sheet1.ListObjects("tblData")

.HeaderRowRange(1) = "Town Name"
.HeaderRowRange(2) = "Town Population"

End With

And so on.

Is there a better solution? I was thinking of having the values set out in a separate table or list in the workbook and then using VBA to transfer those values to the row headers.

Many thanks in advance.

Pieter

See Answer

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 925

Member Since:
December 7, 2016

Offline

June 26, 2021 - 8:51 pm

Hello,

Simplest solution is to just use Name, Population etc, skipping the need to use Town or City.

Br,
Anders

Pieter Cox

Member

Members

Forum Posts: 85

Member Since:
August 3, 2020

Offline

June 27, 2021 - 12:02 am

Thanks for your reply, Anders. I get that and I do exactly that where possible. In this case it isn't and my underlying question is simply: how to select table headers dependent on the contents of a workbook cell.

As ever,

Pieter

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

June 27, 2021 - 12:46 am

Hi Peter,

You can have a separate table with as many columns you need, and write the headers in a single operation:
ActiveSheet.ListObjects("Table2").HeaderRowRange.Value = Application.Transpose(ActiveSheet.ListObjects("HeadersTable").ListColumns("Column1").DataBodyRange.Value)

I assume the number of rows in the headers table is equal to the number of columns in the destination table.