Help Otto

[Tia]

Otto,

I'm using your same formula in a different cell but have run into a new
problem. For example, I'm putting your formula in cell F29, Sheet1 wanting
it to populate cell B28 on Sheet 4. Here's where the problem comes to play.
There are some instances when the entire rows above F29 on Sheet 1could be
deleted, therefore changing the original cell of F29 to say maybe F27. Is
there a way to permanently set F29 as the cell?

Thanks!


Thanks for all your help! It worked!

 

[Otto Moehrbach]

Tia
I don't know. I framed your question in a simplified manner and posted
it in the Functions newsgroup. If and when I get the solution, I'll get
back to you. HTH Otto

 

[Otto Moehrbach]

Tia
The answer I got is simple enough. In your formula, wherever you have
F29, put INDIRECT("ShtName!F29") where ShtName is the name of the sheet in
the formula. Otto

 

[Tia]

Hi Otto,

I believe I'm entering exactly as it should be, but excel is not accepting
the formula. Here's what I've got after I replace everything:

=IF(One! INDIRECT("One!F32") <>"",One! INDIRECT("One!F32"),IF(Two!
INDIRECT("One!F32") <>"",Two! INDIRECT("One!F32"),""))

Does this look right?

Thanks

 

[Otto Moehrbach]

Tia
You left too much stuff in the formula but I also think I told you to do
that. Change that as follows:
=IF(INDIRECT("One!F32") <>"", INDIRECT("One!F32"),IF(INDIRECT("Two!F32")
<>"",INDIRECT("Two!F32"),""))
Otto

 

[Tia]

Hey Otto,

The formula works when now rows are deleted. However, as soon as you delete
a row above it doesn't work.

 

[Otto Moehrbach]

Tia
Clarify what you said. The 2 sentences appear to contradict each other.
It might help if you send me an example of your file and how you are using
the formula. HTH Otto