Help Otto
 

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" wrote:

Tia
This is easier than I thought. Put the following formula in both
destination cells, B14 in the third sheet and B6 in the fourth sheet.
Note that I named your first 2 sheets One and Two. Change these names in
the formula as needed. Note that the double quotes ("") DO NOT have a space
between them.
=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,""))

Note that this formula will produce a blank cell in both B14 and B6 if B1 is
blank in both the first 2 sheets. Since both B14 & B6 cells appear to be
blank, you or someone may enter something in those cells by mistake. Doing
so will remove the formulas. You might want to have the formulas enter
something other than a blank cell when both B14 & B6 are blank. If you do,
then do this. Say that you want those cells to show "Tia" when B14 & B6 are
both blank. Go to the very last double quotes in the formula. Just the
last ones. Type Tia between the quotes. The formula will then be:

=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,"Tia"))

Come back if this doesn't do what you want. HTH Otto

Help Otto
 

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
"Tia" wrote in message
...
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" wrote:

Tia
This is easier than I thought. Put the following formula in both
destination cells, B14 in the third sheet and B6 in the fourth sheet.
Note that I named your first 2 sheets One and Two. Change these names in
the formula as needed. Note that the double quotes ("") DO NOT have a
space
between them.
=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,""))

Note that this formula will produce a blank cell in both B14 and B6 if B1
is
blank in both the first 2 sheets. Since both B14 & B6 cells appear to be
blank, you or someone may enter something in those cells by mistake.
Doing
so will remove the formulas. You might want to have the formulas enter
something other than a blank cell when both B14 & B6 are blank. If you
do,
then do this. Say that you want those cells to show "Tia" when B14 & B6
are
both blank. Go to the very last double quotes in the formula. Just the
last ones. Type Tia between the quotes. The formula will then be:

=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,"Tia"))

Come back if this doesn't do what you want. HTH Otto

Help Otto
 

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" wrote in message
...
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" wrote:

Tia
This is easier than I thought. Put the following formula in both
destination cells, B14 in the third sheet and B6 in the fourth sheet.
Note that I named your first 2 sheets One and Two. Change these names in
the formula as needed. Note that the double quotes ("") DO NOT have a
space
between them.
=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,""))

Note that this formula will produce a blank cell in both B14 and B6 if B1
is
blank in both the first 2 sheets. Since both B14 & B6 cells appear to be
blank, you or someone may enter something in those cells by mistake.
Doing
so will remove the formulas. You might want to have the formulas enter
something other than a blank cell when both B14 & B6 are blank. If you
do,
then do this. Say that you want those cells to show "Tia" when B14 & B6
are
both blank. Go to the very last double quotes in the formula. Just the
last ones. Type Tia between the quotes. The formula will then be:

=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,"Tia"))

Come back if this doesn't do what you want. HTH Otto

Help Otto
 

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" wrote:

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" wrote in message
...
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" wrote:

Tia
This is easier than I thought. Put the following formula in both
destination cells, B14 in the third sheet and B6 in the fourth sheet.
Note that I named your first 2 sheets One and Two. Change these names in
the formula as needed. Note that the double quotes ("") DO NOT have a
space
between them.
=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,""))

Note that this formula will produce a blank cell in both B14 and B6 if B1
is
blank in both the first 2 sheets. Since both B14 & B6 cells appear to be
blank, you or someone may enter something in those cells by mistake.
Doing
so will remove the formulas. You might want to have the formulas enter
something other than a blank cell when both B14 & B6 are blank. If you
do,
then do this. Say that you want those cells to show "Tia" when B14 & B6
are
both blank. Go to the very last double quotes in the formula. Just the
last ones. Type Tia between the quotes. The formula will then be:

=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,"Tia"))

Come back if this doesn't do what you want. HTH Otto

Help Otto
 

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" wrote in message
...

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" wrote:

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" wrote in message
...
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" wrote:

Tia
This is easier than I thought. Put the following formula in both
destination cells, B14 in the third sheet and B6 in the fourth sheet.
Note that I named your first 2 sheets One and Two. Change these names
in
the formula as needed. Note that the double quotes ("") DO NOT have a
space
between them.
=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,""))

Note that this formula will produce a blank cell in both B14 and B6 if
B1
is
blank in both the first 2 sheets. Since both B14 & B6 cells appear to
be
blank, you or someone may enter something in those cells by mistake.
Doing
so will remove the formulas. You might want to have the formulas
enter
something other than a blank cell when both B14 & B6 are blank. If
you
do,
then do this. Say that you want those cells to show "Tia" when B14 &
B6
are
both blank. Go to the very last double quotes in the formula. Just
the
last ones. Type Tia between the quotes. The formula will then be:

=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,"Tia"))

Come back if this doesn't do what you want. HTH Otto

Help Otto
 

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" wrote:

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" wrote in message
...

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" wrote:

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" wrote in message
...
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" wrote:

Tia
This is easier than I thought. Put the following formula in both
destination cells, B14 in the third sheet and B6 in the fourth sheet.
Note that I named your first 2 sheets One and Two. Change these names
in
the formula as needed. Note that the double quotes ("") DO NOT have a
space
between them.
=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,""))

Note that this formula will produce a blank cell in both B14 and B6 if
B1
is
blank in both the first 2 sheets. Since both B14 & B6 cells appear to
be
blank, you or someone may enter something in those cells by mistake.
Doing
so will remove the formulas. You might want to have the formulas
enter
something other than a blank cell when both B14 & B6 are blank. If
you
do,
then do this. Say that you want those cells to show "Tia" when B14 &
B6
are
both blank. Go to the very last double quotes in the formula. Just
the
last ones. Type Tia between the quotes. The formula will then be:

=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,"Tia"))

Come back if this doesn't do what you want. HTH Otto

Help Otto
 

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
"Tia" wrote in message
...
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" wrote:

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" wrote in message
...

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" wrote:

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" wrote in message
...
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" wrote:

Tia
This is easier than I thought. Put the following formula in
both
destination cells, B14 in the third sheet and B6 in the fourth
sheet.
Note that I named your first 2 sheets One and Two. Change these
names
in
the formula as needed. Note that the double quotes ("") DO NOT
have a
space
between them.
=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,""))

Note that this formula will produce a blank cell in both B14 and B6
if
B1
is
blank in both the first 2 sheets. Since both B14 & B6 cells appear
to
be
blank, you or someone may enter something in those cells by
mistake.
Doing
so will remove the formulas. You might want to have the formulas
enter
something other than a blank cell when both B14 & B6 are blank. If
you
do,
then do this. Say that you want those cells to show "Tia" when B14
&
B6
are
both blank. Go to the very last double quotes in the formula.
Just
the
last ones. Type Tia between the quotes. The formula will then be:

=IF(One!B1<"",One!B1,IF(Two!B1<"",Two!B1,"Tia"))

Come back if this doesn't do what you want. HTH Otto