Show other cell if blank


B

bojan0810

Hi all...

I have little problem. And this time I dont wanna include VBA, just formulas.

For example...

I have data in A1:A10, And B1:B10...

This is just example.

So i need formula in C1:C20(again this is just example, I have different type)

So in C1 something like if A1 is blank show B1...

Actually data needs to be one under another.

So for example column A has data
1
2
3
4
5
6
7
8
9
10
Column B same thing...

And I need C to look like this
1
2
3
4
5
6
7
8
9
10
1(from B column)
2
3...

So I need if column A cell is blank, to look first value in Column B, and under that second value in column B and so on.

I hope this makes sense

Thanks
 
Ad

Advertisements

C

Claus Busch

Hi Bojan,

Am Sat, 30 Aug 2014 01:50:06 -0700 (PDT) schrieb (e-mail address removed):
So I need if column A cell is blank, to look first value in Column B, and under that second value in column B and so on.

if your data in column B is sorted ascending then try
in C1: =IF(A1="",B1,A1)
in C2: =IF(A2="",SMALL(B1:B10,COUNTIF(B1:B10,"<="&C1)),A2)
and copy down


Regards
Claus B.
 
C

Claus Busch

Hi again,

Am Sat, 30 Aug 2014 11:11:57 +0200 schrieb Claus Busch:
in C2: =IF(A2="",SMALL(B1:B10,COUNTIF(B1:B10,"<="&C1)),A2)

sorry, the ranges in the formula above has to be absolute:
=IF(A2="",SMALL($B$1:$B$10,COUNTIF($B$1:$B$10,"<="&C1)),A2)


Regards
Claus B.
 
C

Claus Busch

Hi Bojan,

Am Sat, 30 Aug 2014 01:50:06 -0700 (PDT) schrieb (e-mail address removed):
So I need if column A cell is blank, to look first value in Column B, and under that second value in column B and so on.

ignore my other answers.
You have to use VBA.


Regards
Claus B.
 
C

Claus Busch

Hi Bojan,

Am Sat, 30 Aug 2014 01:50:06 -0700 (PDT) schrieb (e-mail address removed):
So I need if column A cell is blank, to look first value in Column B, and under that second value in column B and so on.

try
in C1: =IF(A1="",B1,A1)
in C2: =IF(A2="",INDEX($B$1:$B$10,COUNTBLANK($A$2:A2)),A2)


Regards
Claus B.
 
Ad

Advertisements

B

bojan0810

Dana subota, 30. kolovoza 2014. 11:34:44 UTC+2, korisnik Claus Busch napisao je:
Hi Bojan,



Am Sat, 30 Aug 2014 01:50:06 -0700 (PDT) schrieb (e-mail address removed):






try

in C1: =IF(A1="",B1,A1)

in C2: =IF(A2="",INDEX($B$1:$B$10,COUNTBLANK($A$2:A2)),A2)





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

I took time when I read all what you wrote lol...

Anyway, this last one worked great. I didnt even thought to write 2 formulas instead of 1.

I tried this formula with my data and it worked perfectly. This is exactly what I needed.

Thank you very much.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top