Taking value from column without hard coding

[ihb95]

Hey guys,

I have attached a file, which has two tables. Data from the table "Data" is taken to produce the table in "Table2".

Now I have two different issues that give me a hard time:

1st Problem
When two persons are written in the same cell, textjoin just puts those names next to each other. I would like them to be on top of each other while remaining in the same cell. I have tried CHAR(10) but I am not really getting anywhere with it. --> I solved this on my own! The problem was that I put CHAR(10) at the wrong spot in the function. There is no need to further engage with this issue, thanks nonetheless!


2nd Problem
If you look at the textjoin function, you can see that I am taking the data to be presented in the table2 from the columns of "data". What I would like to have is though, that the data is taken from the 'category' of the information written in "data". I know it sounds confusing. What I mean is, e.g. "Name". The "Name" information is currently taken from the colum A2:A200, while A1 says 'Name'. Now if I move the whole column to some random place, let's say column R, then I would like the function in table2 to still present it correctly. In other words, I would like to make it dependent on the category title and not by the defined column.

3rd Problem
moved

How do I realize that?

I know it's a lot and I feel a bit bad, but I am such an Excel rookie! I'm Sorry

Best regards!!

[AliGW]

Yes, this is three different problems. However, the first two relate to TEXTJOIN, so can stay here. For the third, please start a new thread.

I shall amend your thread title in this thread.

[ihb95]

Oh it makes sense to move the last question. Thank you Ali!
Hope to find answers to the first two questions still

[AliGW]

You will. Just be patient.

[ihb95]

So in the vba/macro forum an user helped me a lot by writing a vba code to solve a different problem.
But the problem mentioned above still remains, but might have to be targeted differently.
here is the file now:

how can i change, that the rows that are taken are not hard coded?
Like e.g. if I move the rows in table1 that in the other table it's not changed

[JeteMc]

I feel as if you're asking about structured references. If so:
1. Select cell I2 on the Table 2 sheet and paste the following into the formula bar:

Please Login or Register  to view this content.

2. Simultaneously press the Ctrl, Shift and Enter keys
3. Copy down and across.
Let us know if you have any questions.