=INDIRECT("Country_"&$B$2&"!"&am

[Lexje]

Hi Folks,

I got this formula:
=INDIRECT("Country_"&$B$2&"!"&CELL("address";A5))

$B$2 = linked cell from a drop downlist, will contain years from 1999:2009
So, basically the formula will look into the sheet with the name:
Country_1999 to Country_2009
In order to get all the values in the relative sheets, I need to copy this formula all the way down a column.
This makes for 0 in all relative 'empty' cells.


How can we finetune this formula so that it will only look at non-empty cells in the referenced sheets?

I was thinking of doing something like:

While Not IsEmpty(ActiveCell)
'macro code that acts on the active cell
'change the active cell to the next cell
ActiveCell.Offset(1, 0).Select
Wend

But I'm not sure on how to do this to adapt to the relative sheets...
Any suggestions?

Thanks for your help!!

Erwin.

 

[Oaktree]

Re: =INDIRECT("Country_"&$B$2&"!&quot

How about:

=IF(ISNUMBER(INDIRECT("sheet"&A1&"!"&CELL("address",A2))),INDIRECT("sheet"&A1&"!"&CELL("address",A2)),"")

 

[Lexje]

Re: =INDIRECT("Country_"&$B$2&"!&quot

Oaktree, thanks for hopping in!!

I tried your formula, but it wouldn't work. It doesn't give me an error, but just shows all blanks.

But, more important, is that my sole purpose was not particularly getting rid of the 0's.

The idea was to fill the sheet with numbers from the 10 other sheets, but to only fill IF there is content present in the source location.

This way, I also avoid wasting processor time in void calculations.

Thanks for your help!!

Erwin