Hi Guys,
I am in the process of automating a sheet for convenience, yes, but to save time as the preparation is a pain.
Firstly I have data in my "Generic" sheet Column A that I want in my "ATTYP" sheet as a header. I am doing this with =TOROW(UNIQUE(GENERIC[SITE_REP]),0,FALSE)
So I get my headers, copy and paste the values and convert the range to a table. This I can manage with a VBA, but. Now I have to put in my INDEX MATCH formula. The 1st condition is G1 (Header) to match with "Generic" sheet Column A. The Second Condition is "ATTYP" Sheet C2 to match with the "GENERIC" Sheet Column C - the formula looks something like this =INDEX(GENERIC[SATNR],MATCH(1,(GENERIC[SITE_REP]=Table3[[#Headers],[S011]])*(GENERIC[SATNR]=ATTYP[@LEFT]),0))
My problem is the 1st condition, the header will never be the same value, and headers G:K will either be less or more. So I need this formula to adapt according to the number of columns there will be and the value that will be in row one for the corresponding column. This formula has to repeat in each column that will have a header.
Hope it makes sense, and any assistance will be highly appreciated.
I am in the process of automating a sheet for convenience, yes, but to save time as the preparation is a pain.
Firstly I have data in my "Generic" sheet Column A that I want in my "ATTYP" sheet as a header. I am doing this with =TOROW(UNIQUE(GENERIC[SITE_REP]),0,FALSE)
So I get my headers, copy and paste the values and convert the range to a table. This I can manage with a VBA, but. Now I have to put in my INDEX MATCH formula. The 1st condition is G1 (Header) to match with "Generic" sheet Column A. The Second Condition is "ATTYP" Sheet C2 to match with the "GENERIC" Sheet Column C - the formula looks something like this =INDEX(GENERIC[SATNR],MATCH(1,(GENERIC[SITE_REP]=Table3[[#Headers],[S011]])*(GENERIC[SATNR]=ATTYP[@LEFT]),0))
My problem is the 1st condition, the header will never be the same value, and headers G:K will either be less or more. So I need this formula to adapt according to the number of columns there will be and the value that will be in row one for the corresponding column. This formula has to repeat in each column that will have a header.
Hope it makes sense, and any assistance will be highly appreciated.