Wondering if this is even possible...

[Robmeister89]

I've stumped myself with this one, and I'm not sure its even possible..

I want to create a formula that checks if a cell is blank, if it is not I want it to = a designated cell. If that cell is blank I want it to check a different cell if its blank. This formula will be copied down a column to paste numbers. There will be 16 cells not blank for sure.

So its basically like this...
A1 = not blank
A2 = blank
A3 = blank
A4 = not blank
...

I want the formula in C1 to check for A1 first and foremost. Since its not blank C1 = B1. C2 will check A2, since its blank it will check A3, and since that one is blank C2 should = A4. The problem I'm running into is when I reach C3. C3 will check A3 first and then A4 so it ends up equaling the same thing as C2 (A4). I want the formula to not only check if its blank but check if the cell above is already being used..

Is this possible? I'm sorry if this all sounds way to confusing!

 

[yky]

Is the following what you are after?

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]18[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63, align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]16[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]17[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Robmeister89]

It appears so, yes.

 

[yky]

This can be done quite easily with a filter or VBA. However, unlike formula, which is automatic, both involve human intervention every time the data is changed/updated (filter you have to set it up; VBA you have to run it).

To get the result I posted above, I set up a filter on Column A and uncheck "blanks". Then, I copied Column B to C. Not a lot of work but can be cumbersome if your data is updated often. I bet some genius can come up with a formula to get the same result.

 

[Robmeister89]

This can be done quite easily with a filter or VBA. However, unlike formula, which is automatic, both involve human intervention every time the data is changed/updated (filter you have to set it up; VBA you have to run it).

To get the result I posted above, I set up a filter on Column A and uncheck "blanks". Then, I copied Column B to C. Not a lot of work but can be cumbersome if your data is updated often. I bet some genius can come up with a formula to get the same result.

Can you explain how you did that using filters?

 

[yky]

Click on Column A to highlight the entire column. Then, on the menu bar, click "Data/Filter". This puts a triangle on the top of Column A. Click at it and a window will pop up. At the bottom of the windows you'll see some items are checked. Uncheck the one named "Blanks". This will hide blank rows. Now, copy Column B to C and click "Filter" one more time to get rid of the filter. This will unhide hidden rows.

 

[Robmeister89]

Click on Column A to highlight the entire column. Then, on the menu bar, click "Data/Filter". This puts a triangle on the top of Column A. Click at it and a window will pop up. At the bottom of the windows you'll see some items are checked. Uncheck the one named "Blanks". This will hide blank rows. Now, copy Column B to C and click "Filter" one more time to get rid of the filter. This will unhide hidden rows.

Okay, yeah, that isn't going to work in this situation. Thanks anyways!

 

[Peter_SSs]

Try this formula in C1, copied down.

Book1
	A	B	C
1	1	2	2
2		3	4
3	3	4	5
4	5	5	7
5		6	8
6	8	7	9
7	8	8	11
8	2	9	14
9	9	11	15
10		12	18
11		13	20
12	3	14
13	2	15
14		16
15		17
16	3	18
17		19
18	1	20
Sheet2 (2)

Cell Formulas
Range	Formula
C1	=IFERROR(INDEX(B$1:B$18,AGGREGATE(15,6,(ROW(B$1:B$18)-ROW(B$1)+1)/(A$1:A$18<>""),ROWS(C$1:C1))),"")

 

[yky]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=IFERROR(INDEX(B$1:B$18,AGGREGATE(15,6,(ROW(B$1:B$18)-ROW(B$1)+1)/(A$1:A$18<>""),ROWS(C$1:C1))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

As I said, some genius would come up with a formula to do it. Great job!

What are the 15 and 6 in the formula?

 

[Peter_SSs]

What are the 15 and 6 in the formula?

15 = "SMALL", 6 = "Ignore errors"
If you look up the Help for the AGGREGATE function it will list the possible values for those first 2 arguments in the function and what they mean.