• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDIRECT TO ANOTHER OPEN WORKBOOK

S

srizki

New Member
I am in search of formula that should work like this.

It would go to another workbook, either by hard coded workbook name in the formula or reference to a cell. and then it would see the column header for sheet name (I have number) and then see the row header and bring data from a specified cell from the same row.

To make it clear, I have sheet numbers as column header and row header. I want to bring the value from another sheet in another workbook that contains many sheets.


Thanks
 
There is no need to shout out your TITLE, bad manners, perhaps you should read the green stickies particularly this one.


http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting
 
Hello srizki,


I'm assuming from your title that the other workbook truly is open. INDIRECT won't work on closed workbooks. The formula to pull the sheet name from the column header, and then determine which row to look at based at row header would be something like this:

=INDIRECT("[Book1.xls]"&B$1&"!D"&MATCH($A2,INDIRECT("[Book1.xls]"&B$1&"!A:A"),0))


This formula is looking at Book1.xls

It gets the sheet name from cell B1

It is going to return a value from col D

It figures out which row in col D by doing a MATCH against col A of Book1 looking for the value in A2 (your row header).
 
You must log in or register to reply here.
Back
Top