| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested if help....
Hi All,
I have the following data. Column A Column B AAAAA BBBBBB AAAAA AAAAA AAAAA AAAAA CCCCCCCC AAAAA AAAAA AAAAA What i need to do is fill the blanks in column A when there is a value to return. i.e. every line cell in column B needs a value from above?? does that makes sense? i have been looking at this for 4 hours now and don't konw myself what day it is!!! :( thanks again. Craig |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested if help....
Try this:
Select the Col_B range to be impacted Press the [F5] key........a shortcut for <edit<go to Click [Special].......Select: Blanks Click [OK] While the blank cells are selected... Type: = then, Press the UP arrow Hold down the [Ctrl] key and press [Enter] Now every (formerly) blank cell references the cell above it. Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) wrote in message ps.com... Hi All, I have the following data. Column A Column B AAAAA BBBBBB AAAAA AAAAA AAAAA AAAAA CCCCCCCC AAAAA AAAAA AAAAA What i need to do is fill the blanks in column A when there is a value to return. i.e. every line cell in column B needs a value from above?? does that makes sense? i have been looking at this for 4 hours now and don't konw myself what day it is!!! :( thanks again. Craig |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested if help....
To clarify things, you want column be to be filled in correct? If column B
already has a value (such as the BBBBB and CCCCC in your example) you want that to remain in column B, if the cell is blank in Column be you want to fill it in with the AAAA from column A? (also are the cell contents text? or numbers? perhaps some real examples of what the data looks like would help.) " wrote: Hi All, I have the following data. Column A Column B AAAAA BBBBBB AAAAA AAAAA AAAAA AAAAA CCCCCCCC AAAAA AAAAA AAAAA What i need to do is fill the blanks in column A when there is a value to return. i.e. every line cell in column B needs a value from above?? does that makes sense? i have been looking at this for 4 hours now and don't konw myself what day it is!!! :( thanks again. Craig |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested if help....
Craig,
In your example column A does not appear to have blanks, B does. What do you want to fill column B cells with? Or is it A? A guess: Column A Column B BBBBBB BBBBBB BBBBBB BBBBBB BBBBBB CCCCCCCC CCCCCCCC CCCCCCCC CCCCCCCC CCCCCCCC Is this what you want? HTH Kostis Vezerides On Oct 16, 6:47 pm, wrote: Hi All, I have the following data. Column A Column B AAAAA BBBBBB AAAAA AAAAA AAAAA AAAAA CCCCCCCC AAAAA AAAAA AAAAA What i need to do is fill the blanks in column A when there is a value to return. i.e. every line cell in column B needs a value from above?? does that makes sense? i have been looking at this for 4 hours now and don't konw myself what day it is!!! :( thanks again. Craig |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested if help....
Can we assume that in:
What I need to do is fill the blanks in column A when there is a value to return you meant B not A? If so: 0) if column C is already used, insert a new column C 1) copy B1 to C1 (I will assume the first BBBBBB is in B1. If not just adjust the references to fit your situation) 2) In C2 enter =IF(ISBLANK(B2),C1,B2) 3) Copy this formula down the column by dragging the fill handle (small solid square in lower right corner of active cell) 4) Select all of the C entries and use Copy; now with C still selected use Edit | Paste Special and specify Values. Your formulas are now changed to actual values 5) Delete the column B since column C now has what is needed best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email wrote in message ps.com... Hi All, I have the following data. Column A Column B AAAAA BBBBBB AAAAA AAAAA AAAAA AAAAA CCCCCCCC AAAAA AAAAA AAAAA What i need to do is fill the blanks in column A when there is a value to return. i.e. every line cell in column B needs a value from above?? does that makes sense? i have been looking at this for 4 hours now and don't konw myself what day it is!!! :( thanks again. Craig |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested if help....
There are no blanks in column A.
Do you want to fill column B with the the values from the non-blank cells in column B? Select column B and F5SpecialBlanksOK Type an = sign in activecell then point or arrow up to cell above and hit CTRL + ENTER Gord Dibben MS Excel MVP On Tue, 16 Oct 2007 08:47:28 -0700, wrote: Hi All, I have the following data. Column A Column B AAAAA BBBBBB AAAAA AAAAA AAAAA AAAAA CCCCCCCC AAAAA AAAAA AAAAA What i need to do is fill the blanks in column A when there is a value to return. i.e. every line cell in column B needs a value from above?? does that makes sense? i have been looking at this for 4 hours now and don't konw myself what day it is!!! :( thanks again. Craig |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested if help....
Ron,
Not sure if this helped the OP, but thanks for such a great little trick...I can think of multiple ways I can apply variations of this...I wish I had known it years ago! You guys are great! "Ron Coderre" wrote: Try this: Select the Col_B range to be impacted Press the [F5] key........a shortcut for <edit<go to Click [Special].......Select: Blanks Click [OK] While the blank cells are selected... Type: = then, Press the UP arrow Hold down the [Ctrl] key and press [Enter] Now every (formerly) blank cell references the cell above it. Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) wrote in message ps.com... Hi All, I have the following data. Column A Column B AAAAA BBBBBB AAAAA AAAAA AAAAA AAAAA CCCCCCCC AAAAA AAAAA AAAAA What i need to do is fill the blanks in column A when there is a value to return. i.e. every line cell in column B needs a value from above?? does that makes sense? i have been looking at this for 4 hours now and don't konw myself what day it is!!! :( thanks again. Craig |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested if help....
I'm glad I could help.....thanks for letting me know.
*********** Regards, Ron XL2003, WinXP "diaare" wrote: Ron, Not sure if this helped the OP, but thanks for such a great little trick...I can think of multiple ways I can apply variations of this...I wish I had known it years ago! You guys are great! "Ron Coderre" wrote: Try this: Select the Col_B range to be impacted Press the [F5] key........a shortcut for <edit<go to Click [Special].......Select: Blanks Click [OK] While the blank cells are selected... Type: = then, Press the UP arrow Hold down the [Ctrl] key and press [Enter] Now every (formerly) blank cell references the cell above it. Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) wrote in message ps.com... Hi All, I have the following data. Column A Column B AAAAA BBBBBB AAAAA AAAAA AAAAA AAAAA CCCCCCCC AAAAA AAAAA AAAAA What i need to do is fill the blanks in column A when there is a value to return. i.e. every line cell in column B needs a value from above?? does that makes sense? i have been looking at this for 4 hours now and don't konw myself what day it is!!! :( thanks again. Craig |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested if help....
On 16 Oct, 17:23, "Bernard Liengme"
wrote: Can we assume that in:What I need to do is fill the blanks in column A when there is a value to return you meant B not A? If so: 0) if column C is already used, insert a new column C 1) copy B1 to C1 (I will assume the first BBBBBB is in B1. If not just adjust the references to fit your situation) 2) In C2 enter =IF(ISBLANK(B2),C1,B2) 3) Copy this formula down the column by dragging the fill handle (small solid square in lower right corner of active cell) 4) Select all of the C entries and use Copy; now with C still selected use Edit | Paste Special and specify Values. Your formulas are now changed to actual values 5) Delete the column B since column C now has what is needed best wishes -- Bernard V Liengme Microsoft Excel MVPwww.stfx.ca/people/bliengme remove caps from email wrote in message ps.com... Hi All, I have the following data. Column A Column B AAAAA BBBBBB AAAAA AAAAA AAAAA AAAAA CCCCCCCC AAAAA AAAAA AAAAA What i need to do is fill the blanks in column A when there is a value to return. i.e. every line cell in column B needs a value from above?? does that makes sense? i have been looking at this for 4 hours now and don't konw myself what day it is!!! :( thanks again. Craig- Hide quoted text - - Show quoted text - HI All, thank you all for your contrbutions. I didn't expect such a massive responose. Thank you, Bernards suggestions best fitted my needs. To clarify i needed a column B to have all blanks filled by the value above. Thank you all again. Craig |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested IF | Excel Worksheet Functions | |||
| nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
| More than 16 nested IFs!!!! | Excel Worksheet Functions | |||
| nested if | Excel Worksheet Functions | |||
| Nested IF | Excel Discussion (Misc queries) | |||
Linear Mode
