Hi there,
I am struggling to get the letters of a column.
I wish to search a row range say (D4:Y4) for “Text1” and then it tell me what the column letters are of the column that has “Text1” in it.
I have tried several options but nothing fits.
So far I have =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") this gives me the column letter but I can’t seem to do the searching of the row.
Can someone point me in the right direction please.
Robin
Hallo
=SUBSTITUTE(ADDRESS(1;MATCH("Text1";D4:Y4;0)+3;4);"1";"")
But one question remains: For which reason do you need this magic Columnletter?
It’s difficult cause i can’t identify “Text1” as TRUE so exsp. “Text10” as FALSE. “Text1” is inherent of “Text10”.
Please upload an ODF type sample file here.
What do you talk about ??
Thank you all. @karolus
This worked for me. It was one column out but changing +3 to +2 seemed to fix that.
To answer your question " For which reason do you need this magic Columnletter?", well it was to be part of a bigger formula.
What I am trying to ultimately do is have many sheets that are almost the same but be able to fill many rows in the first sheet from other sheets. Someone should be able to write the name of a sheet eg “Sheet3” in one cell and it fills the cells under it with the cells from “Sheet3”.
Sheet1:
C3 = Editable cell that people can write the name of another sheet, eg. “Sheet3”.
C4 = Editable cell that people can write some text, eg. “Text1”, that is the the header of a table in sheet “Sheet3” found within a range “D4:Y4” .
C6 = Returned value from “Sheet3” column “Text1”, same row as this row, Row().
Sheet3:
X4 = Text1 Y4 = Text2
X6 = SomeValue X6 = OtherValue
I am almost there thanks to your help, I just need now the bold part of this formula altered for Sheet1 C6.
=INDIRECT(C$3 & CONCATENATE(".",SUBSTITUTE(ADDRESS(1,MATCH(C$4,D4:Y4,0)+2,4),“1”,""),ROW()))
The bold section just needs to look in the user written sheet eg. Sheet3.D4:Y4
I’m not sure I fully understand your idea. What about “without SUBSTITUTE(ADDRESS())”?
=OFFSET(INDIRECT($C$3&".C1");ROW()-1;MATCH($C$4;INDIRECT($C$3&".D4:Y4");0))
Thank you for your time. I am not sure this is quite what I am looking for.
I can appreciate what I am trying to explain is tough without examples.
I will attach a sample Workbook.
As you can see the formula I have in C6 is
=INDIRECT(C$3 & CONCATENATE(".",SUBSTITUTE(ADDRESS(1,MATCH(C$4,C$4:X$4,0)+2,4),“1”,""),ROW()))
The bit I have highlighted in bold is the correct range but it need to be on the sheet as input in cell C3.
Hopefully that is a little bit clearer.
Table reference.ods (13.5 KB)
Are you saying that the proposed formula does not work as you intended?
Table reference 1.ods (17.5 KB)
Wow, that does indeed work! Thank you so much for your time and effort.
I am yet to bend my head around why it works especially the mention of .C1 in the formula.
One small problem, it stops working if I extend the table data beyond the Z Column on Sheet2 and Sheet3. When the table gets added to and it goes into AA, AB etc it fails. Why would that happen?
Sorry, I cannot answer this question - there is too little information. What exactly is fails with the formula? Showing #N/A or #REF! error? Showing data from another column? Don’t show nothing at all? How did you change the formula text when resizing the table?
From the very beginning, you don’t say much about the problem you’re really trying to solve. Why are the column headings in the fourth line, and not in the first or, for example, the tenth? Why does the data start with Column D and not Column A? How wide and tall are the data tables on each sheet? Why is the data distributed across multiple sheets, rather than collected on one sheet into one long table that can be filtered by any criteria? You promise that all texts in the title line are unique and there will be no duplicates in any of the tables?
The answers to all of these additional questions can significantly influence our recommendations. The fact is that the problem described in your first message has many possible solutions and each of these solutions can be better or worse
=OFFSET(INDIRECT($C$3&".C1");ROW()-1;MATCH($C$4;INDIRECT($C$3&".D4:Y4");0))
=INDIRECT(ADDRESS(ROW();MATCH(C$4;INDIRECT(C$3&".4:4");0);1;1;C$3))
=OFFSET(INDIRECT(C$3&".A1");ROW()-1;MATCH(C$4;INDIRECT(C$3&".$4:$4");0)-1)
{=OFFSET(INDIRECT(C$3&".A1");5;MATCH(C$4;INDIRECT(C$3&".$4:$4");0)-1;100)}
etc.
perhaps no solution but my study you may try with translator the infos in deutsch, the problem is that PC-funktionality are not ably to recognise typografics only CODE-characters.
perhaps you will find and pick one crumbly out of our studies you can use:
1_LibreOffice-CALC_fortlaufende UNiCODE-UNiZEiCHEN_iN ARBEiT-v0003.ods (74.1 KB)
1_LibreOffice-CALC_fortlaufende UNiCODE-UNiZEiCHEN_iN ARBEiT-v0003.pdf (348.3 KB)
1_LibreOffice-CALC_typographische Wörter finden in mehreren Zellen_iN ARBEiT-v0008.ods (33.9 KB)
1_LibreOffice-CALC_typographische Wörter finden in mehreren Zellen_iN ARBEiT-v0008.pdf (159.5 KB)