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]

1. Looks to me C3 should check both A2 and A3, not just A3 alone.
2. You did not say what you want in C3.

 

[PaddyD]

"I want the formula to not only check if its blank but check if the cell above is already being used.."

And if it is, what next?

 

[Robmeister89]

"I want the formula to not only check if its blank but check if the cell above is already being used.."

And if it is, what next?

If c2 = b4 it should be skipped when c3 checks it and the formula should continue checking for the next non-empty cell. For instance, maybe a5 is not blank therefore c3 would equal b5.

 

[petertenthije]

Assuming your data range is A1:A4, you can expand the formula as needed.

=IFERROR(INDEX($A$1:$A$4,COUNTBLANK($A$1:$A1)+ROW(),1),"")

 

[Robmeister89]

Assuming your data range is A1:A4, you can expand the formula as needed.

=IFERROR(INDEX($A$1:$A$4,COUNTBLANK($A$1:$A1)+ROW(),1),"")

Unfortunately, that's not exactly what I'm looking to do.

So now that I've got the spreadsheet open let me show you what I have currently...

=IF(NOT(ISBLANK(E2)),A2,IF(NOT(ISBLANK(E3)),A3,IF(NOT(ISBLANK(E4)),A4...))) [what I'm doing is copying this cell down a column]

So the first if checks if the first box is empty. If it is not it gives me the respective cell in the A column (E2 > A2). If it is blank it checks the next row down.

The problem I'm running into is when I actually reach the blank cells the next two, three, four, etc. answers will be the same because its checking them in order and giving me the first one that is not blank. I need a way to skip that cell once its been used.

I'm not sure its possible but that's why I'm asking.. I tried to check the above cell to see if the number is the same but I kept getting a false in the if statement.

 

[petertenthije]

Would you mind to include a calculation column?

Say you add a column in front of column A. In the (new) column A you could add this formula =IF(B2="",A1,A1+1). Cell A1 has to be value 1. Then you could use a simple VLOOKUP or MATCH formula to refer to the values in column A.

 

[Robmeister89]

Would you mind to include a calculation column?

Say you add a column in front of column A. In the (new) column A you could add this formula =IF(B2="",A1,A1+1). Cell A1 has to be value 1. Then you could use a simple VLOOKUP or MATCH formula to refer to the values in column A.

I'm making a schedule for a football league... What I'm doing is trying to get an the non-blank cells and copy column A. From there I'm going to use a vlookup to find out what was in column E. I'm thinking that writing a program to output a csv file will actually be easier than trying to use excel LOL.. I've got a block schedule with home/away games but it lists every game twice on the block schedule because it has the home team and the away team listed. The csv import file for the game must only have the game listed once.

 

[PaddyD]

Why don't you post up a representative sample of your data and take us through a worked example of what you're trying to achieve - there is always a way

 

[Robmeister89]

So I've found that my way works but it takes a little more effort.. I have to copy the values to a new line, delete the duplicates and then copy the final numbers..

I would upload an image of my spreadsheet or attach my spreadsheet but I don't see an option to attach anything?

So Column A has in the first four:
5
30
32
25

Column E has:
30
(blank)
(blank)
32

This means Team 5 is home against team 30 and 25 is home against 32.

My formula reads: =IF(NOT(ISBLANK(E2)),$A2,IF(NOT(ISBLANK(E3)),$A3,IF(NOT(ISBLANK(E4)),$A4)))

Since E2 is not blank it gives me team 5 (from A2). Then I use a vlookup for their opponent (but this is besides the point and not where the issue lies).

So then I copy it down a row... =IF(NOT(ISBLANK(E3)),$A3,IF(NOT(ISBLANK(E4)),$A4,IF(NOT(ISBLANK(E5)),$A5)))

This gives me 25 (in A5) because E3 and E4 are blank cells. This is fine...

The problem now becomes the next line... =IF(NOT(ISBLANK(E4)),$A4,IF(NOT(ISBLANK(E5)),$A5,IF(NOT(ISBLANK(E6)),$A6)))

This one checks E4 again.. blank.. then checks E5 again.. and it gives me 25 (E5 is not blank, so it gives me A5). I want to eliminate this in my formula. I want it to also check to see if the above row is already using 25 (in this instance) so it skips it and moves down a row and checks again for a blank cell.

Does this make sense? I can load my sheet into a Google Sheet so you can see it first hand. Again, I don't see an option to attach a file here on this forum so I cannot upload my spreadsheet here.