Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Request for help from Otto | Excel Worksheet Functions | |||
Otto M. - Data Matching | Excel Worksheet Functions | |||
vbs to macro > Otto | New Users to Excel |