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
Hello,
Simplest solution is to just use Name, Population etc, skipping the need to use Town or City.
Br,
Anders
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
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.
Hi Catalin,
Thanks very much - I think that's just what I was looking for. I'll have a play and revert if I get stuck. Thanks again.
Pieter
