Nested if help....

[craiglittleperth]

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

 

[Ron Coderre]

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)

 

[Guest]

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.)

 

[vezerid]

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

 

[Bernard Liengme]

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

 

[Gord Dibben]

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 F5>Special>Blanks>OK

Type an = sign in activecell then point or arrow up to cell above and hit CTRL
+ ENTER


Gord Dibben MS Excel MVP

 

[Guest]

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!

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)

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

 

[Guest]

I'm glad I could help.....thanks for letting me know.

***********
Regards,
Ron

XL2003, WinXP

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!

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)

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

 

[craiglittleperth]

Can we assume that in:>What I need to do is fill the blanks in column A when there is a value

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













- 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