How to reference table column header name and table row number?

[riskywhat]

I'm trying to populate a new table in a new worksheet with data from an existing table in a different worksheet. I need to reference the column header name because the positions of the columns may change.

I currently have this:

=TableName[[ColumnHeaderName]]

This works but the problem is when I try to sort any of the columns in the new table, it doesn't sort because it is referencing the same row in the existing table. I'm guessing I need to reference the column name and row number, but when I try =TableName[[ColumnHeaderName]] 2:2 it displays #VALUE!.

Any help would be greatly appreciated.

[AliGW]

Welcome to the forum.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[riskywhat]

Thanks for the welcome!

Here is the sample spreadsheet. If you try sorting any column in the "Forward Calculations" worksheet, you will see it doesn't sort.

Edit: So just to be clear, the desired outcome is to have a table that sorts and still dynamically updates when the table in the "Forwards" worksheet changes. Currently it does update but does not sort.

[AliGW]

I see. The result table will take its sort order from the source table.

This will always take the data from A2 and so on down:

=ForwardsTable[@[Name ]]

This is not really a formula - it's a cell reference only.

[riskywhat]

Yes the sort order always comes from the source table. I need to be able to sort the data in the output table while still keeping the cell references (data in the source table will be updated weekly). Is there a way to do that?

Most of the columns are very simple formulas. The first 2 columns are cell reference only.

[AliGW]

No, that's what I am telling you: you cannot do that because they are just cell references.

To sort the columns you'd need to have one column that's hard-coded and the other columns would need to use this as a lookup value. So your name column should NOT be a cell reference, but simply a copy of the list in the other table. Then you'd use VLOOKUP or INDEX MATCH for the other columns.

[riskywhat]

The source table is scraped from a webpage so I could have it fill the first column of the second table at the same time.

If I understand correctly, I then need to have the other columns in the second table look at the first column in the second table, and use that to find the the corresponding data in the source table?

[AliGW]

Yes - you need something that will adjust if you change the sort order - a simple cell reference won't.

[riskywhat]

How about using a cell reference with a specified row number?

=INDEX(TableName[ColumnHeaderName],1)
=INDEX(TableName[ColumnHeaderName],2)
=INDEX(TableName[ColumnHeaderName],3)

It seems to work and I can sort the columns but I need to enter it manually into each cell. When I pull down to autofill it just copies the same formula without changing the row number.

[AliGW]

If it works for you, fine.

My point is (and remains!) that a simple cell reference will not work. Trouble is you will need to hard code the row number.

[riskywhat]

Ok thank you very much for the help!

[AliGW]

Let us know how you get on.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.