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

Copper Contributor

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.xlsm]AAA!B1 in the excel file BBB so if I insert/delete column in the file AAA, the value of TransactionNo will stay good.

 

Is there a way to use [AAA.xlsm]AAA!TransactionNo in my formulas when I'm in the BBB file?

9 Replies

@Heron09 

While AAA.xlsm is open, the formula can be

 

=[AAA.xlsm]!TransactionNo

 

No need to specify the worksheet unless you defined TransactionNo explicitly as a worksheet-level name.

Then close AAA.xlsm. Excel will automatically add its full path to the formula.

Finally, save the workbook with the formula.

@Hans Vogelaar 


I have to mention the sheet because I have many sheets in my file AAA. Also, I have TransactionNo define in 2 differents sheets in the same file.

 

In my file BBB, I took ='[AAA.xlsm]AAA'!$B1 and changed it by  ='[AAA.xlsm]AAA'!TransactionNo and the result is #NAME?

 

@Heron09 

Can you post a screenshot of the Name Manager dialog of the AAA.xlsm workbook?

I just realize that I did a mistake in the name. I did the correction and it works.
Now I'm trying to use it in the formula VLOOKUP to replace the "$B:$B" by using the column of ColTransaction.

=VLOOKUP($A15,'[AAA.xlsm]Old data!$B:$B,'[AAA.xlsm]Old data'!$F:$F,"",0)

 

P.S. I will do the same with the $F:$F once the first one will work.

Capture.JPG

The sheet I try to use is Data and the cell's reference is colTransaction.
For your info, "Old data" is another sheet that I'm using as an archive.


My screen shot is in French. The columns of the screen shot are:
Name, Value, Refer to, Sheet reference, Comment

@Hans Vogelaar 

@Heron09 

Sorry, I don't understand. Is "Order" the name of a range? Or is it the name of a column in a table?

Order is the value of the cell.
colTransaction refer to one cell that I'm using in the code to be able to get the column in the vba code.

@Heron09 

That's too complicated for me, sorry.

@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)
)