INDIRECT cell referencing question.

Originally Posted by call_me_bob

Please could someone help me with this excel problem.

This is a variation on a question I posted back in January which was v promptly solved by starguy.

I am using excel 2003 and have 2 workbooks (i'll call them 1 and weekno)

Workbook 1 controls which five cells are chosen for testing from within the 52 page thick workbook weekno.

Workbook 1 has an area 5 columns of 52 cells (one for each of the 5 different tests for each of the 52 weeks).

As the idea behind the 5 by 52 cell block is to give the user the opportunity of carrying out tests on 5 different cells within workbook weekno I could just have the formula hard coded but as a means of future proofing the page a little I would like to be able to provide the end user with the option of tailoring which five cells they are referencing within the weekno sheet.

Workbook weekno has a maximum of 52 pages all of which are similar to each other in layout but contain records for a particular weeks transactions.

My question is how can I create a small area in workbook 1 that specifies which 5 cells are to be included (1 for each column) into the columns within workbook 1. Each of these columns then calls the same cell from all 52 pages of workbook weekno?

I would also like to check to make sure the value being called in is a valid numeric value and if not just leave it blank, so the overall solution would look something like this

=IF(ISNUMBER('[weekno.xls]1'!$G$5),'[weekno.xls]1'!$G$5,"")

where

weekno.xls is the sheet that I'm trying to obtain the values from.
the 1 is the page within weekno.
and G5 is the cell that stores the local reference within worksheet 1 that states exactly where in weekno the values should be selected from.

Any help would be much appreciated.

Cheers

Bob

Hi,

with Bookname in A1, Sheetname in A2, Cell Address in A3, try

=IF(ISNUMBER(INDIRECT("["&A1&"]"&A2&"!"&A3)),INDIRECT("["&A1&"]"&A2&"!"&A3),"")


or, with $G$ in A3

=IF(ISNUMBER(INDIRECT("["&A1&"]"&A2&"!"&A3&ROW()+1)),INDIRECT("["&A1&"]"&A2&"!"&A3&ROW()+1),"")

hth
---

Originally Posted by call_me_bob

Cheers for that Bryan.

I've now managed to get the feature working in a far more practical way than I had previously thanks to you.

And all whilst I slept.

Thanks again Bryan.

Bob

good to see that it worked for you, and thanks for the response.
---

Originally Posted by vkohli

how can I do the above when the other workbook to which I am referencing is closed. In this case, the indirect function will throw up an error. I am simply trying to link a cell in a wkbook with another.

Am using this formula,

A1=INDIRECT("'[FirstName "&RIGHT(myname(),10)&"]"&"Master"&"'!"&"A1")

myname is a function that I have defined which throws up the value of the workbook in which you are working.

It gives FirstName Apr 07.xls

right(myname(),10) then can be used to refer to different months depending on the wkbook.

Please help.

HI,

I have not used it, but hear that the 'MoreFunc' addin will permit access to books that are not currently open.

see http://xcell05.free.fr/english/

hth
---

Originally Posted by vkohli

thanks..it works..

btw, i also found another way to do the same..simply write a macro that finds and replaces 'Apr' with 'May' or so for every month in every tab!

good to see, and thanks for the feedback, but I fear this thread will now be locked as you did not start a new thread for your question.

---