Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have 2 excel files - A and B. In cell A1 of file B, I use an index formula to refer to a row in file A. "INDEX('[A.xlsb]A'!$10:$10,1,2)" is the formula I use (referring row 10 in file A).

If I update the row number in another cell of file B, is it possible for this formula to refer to that cell to get the row number from file A?

Any help is appreciated.

Thanks

share|improve this question

2 Answers 2

up vote 0 down vote accepted

The 1 in this formula

=INDEX('[A.xlsb]A'!$10:$10,1,2)

defines the row relative to the range (as you only have one row, row 10, it can only be 1), so assuming you always want a row between 1 and 100 (adjust as required) you can use this version

=INDEX('[A.xlsb]A'!$1:$100,C3,2)

where C3 contains the row number you want

share|improve this answer
    
Thanks, it worked. –  Anshuman Chanda Dec 8 '12 at 14:02

Syntax to refer to another file is

'[FileNameAndExtension]SheetName'!Range

eg

=INDEX('[A.xlsb]Sheet1'!$10:$10,1,2)

Note, the other workbook must be open

share|improve this answer
    
Sheet name is the same as the file name. The formula in file B is INDEX('[A.xlsb]A'!$10:$10,1,2). Is it possible to modify this formula to update the row number (in file A) from a cell in file B? –  Anshuman Chanda Dec 8 '12 at 12:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.