Forum Discussion

Heron09's avatar
Heron09
Copper Contributor
Apr 11, 2024

Get the column of a cell via it's reference in another sheet

I have an excel file AAA with names to refer to some cells. Ex: TransactionNo refer to cell B1 when I'm into the file AAA.   What I want to do is to be able to use TransactionNo instead of [AAA.xl...
excel
Formulas and Functions
Heron09's avatar
Heron09
Copper Contributor
Apr 12, 2024
colTransaction refer to one cell that I'm using in the code to be able to get the column in the vba code.
SnowMan55's avatar
SnowMan55
Bronze Contributor
Apr 15, 2024

Heron09 

I will address the problem as formula-only, without regard to VBA.

 

I hope you are using Excel 2021 or a later version, because the LET function (fonction LET) supported since then allows for more understandable formulas.


The COLUMN function (fonction COLONNE) returns the number of the column for your specified cell.


But to work with a range, it is probably easier to work with column letters than column numbers, so you additionally will want to use the ADDRESS function (fonction ADRESSE) to derive an cell reference from that (and use the LEFT and LEN functions (GAUCHE et NBCAR) to extract the column letter(s) from the cell reference).


Then, to build a qualified range reference for second argument to the VLOOKUP, you need to use the INDIRECT function (fonction INDIRECT).


INDIRECT is a volatile function (fonction volatile), so I hope you will not require many occurrences of it.


Note that below I used a "through" column of AZ (52nd column); depending on your data, you may need to use a column reference further to the right; or if you are confident the retrieved data will never extend that far, you may use a column reference to its left.


The third argument to VLOOKUP (RECHERCHEV) is a column number relative to the range specified as the second argument. That might be a static number in your case, but it can also be calculated. (I do not see a reference to column F in your screenshot, so I made up and used named cell colCompany.)


So, putting that all together, this is a formula for doing that lookup:

=LET( TransMarkerColNum, COLUMN('[AAA.xlsm]Data'!colTransaction),
    TransMarkerAddr, ADDRESS(1, TransMarkerColNum, 4),
    TransColLetters, LEFT(TransMarkerAddr, LEN(TransMarkerAddr) - 1),
    ResultMarkerColNum, COLUMN('[AAA.xlsm]Data'!colCompany),
    ResultRelativeCol, ResultMarkerColNum - TransMarkerColNum + 1,
    LookupRange, INDIRECT("'[AAA.xlsm]Data'!$" & TransColLetters & ":$AZ"),
    VLOOKUP($A15, LookupRange, ResultRelativeCol, FALSE)
)

 

Resources