Forum Discussion
Get the column of a cell via it's reference in another sheet
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.
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?
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.
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
HansVogelaarSorry, I don't understand. Is "Order" the name of a range? Or is it the name of a column in a table?