A new day, a new problem... ;P

[bomberman411]

Hello everyone,

I have a worksheet that I have been using for almost 2 years. Over the years I have added a lot of little formulas here and there and they usually work great. Recently I have noticed that my formulas don't exactly work like I wanted, so it's time to try and fix them. Problem is, I had help creating them (from all the nice people on this website), and I don't really understand exactly what some of them do. That's why I still need help

Here below are the formulas that I have. They are in french, but I will try to post the translated formula as well, so please keep in mind that I'm working in french.

This cell formula is supposed to check out my whole spreadsheet and give me the number of the next empty row:

=INDEX($A$10:$A$5000;EQUIV(1;1/(ESTVIDE($B$10:$B$5000));0))

=INDEX($A$10:$A$5000;MATCH(1;1/(ISBLANK($B$10:$B$5000));0))

Right now, it "seems" to work, but I know for a fact that the number that it's returning is not the "next" empty row... From what I can gather, this formula doesn't start from the beginning of the spreadsheet. It just continues from where it left off.

Please help if you can.

 

[bomberman411]

From what I can figure out, this looks like a circular reference problem... but I can't figure out how to fix it.

 

[schielrn]

What cell are you putting this formula in?

If it is a circular reference problem at the bottom of the screen in the status bar, it might give you a cell reference of the circular reference?

 

[bomberman411]

What cell are you putting this formula in?

If it is a circular reference problem at the bottom of the screen in the status bar, it might give you a cell reference of the circular reference?

It's in the D4 cell, and it's giving me the number 4147 but it should be 0005.

It seems like the formula isn't starting off at the beginning of the column as it should...

If I go to my real first empty row (0005) and press delete in the empty cell (B14), it shows me "0005" in cell D4 (where the formula is) right before giving me a popup window which says there might be a circular reference problem. When I click ok, the 0005 number in D4 dissapears and returns to 4147.

I don't see anything at the bottom of the screen.

 

[bomberman411]

Ok I just tested something out:

In cell 4147 (the one that my formula says is empty), I wrote some text and hit enter. The formula now shows 4150 as the next empty row (which is normal) BUT if I delete the text in 4147, the formula still shows 4150.

It's seems like the formula isn't backwards compatible, it only goes in 1 direction, downwards on the page (upwards numerically).

Is there another formula that would do the same thing but without using an index? I'm not 100% sure on how this formula works but it seems to me that it has some kind of buffer (index) that increments itself, but doesn't decrement. Is that possible/logical?

 

[schielrn]

Sorry, but I am completely lost with what you are after? Are you just after the first blank cell in B10:B5000 and return the value in column A of that first blank, going down?

Are you sure none of your formulas are causing this circular reference, what cell is at teh bottom of the screen that supposedly has this circular reference?

It works fine for me with some test data, just putting the data in columns A and B, it is returning the value of A at the first blank in B?

Sorry I'm not sure what the problem is at this point?

 

[bomberman411]

It works fine for me with some test data, just putting the data in columns A and B, it is returning the value of A at the first blank in B?

That's exactly what it's supposed to do, return value in column A when cell next to it, in column B is blank. The problem is that it's not showing the right number.

 

[bomberman411]

For example, right now I have "4150" as the next empty cell (writen in D4) but the next empty cell is "0005", and that's what's supposed to be written in D4. In fact, between 0005 and 4150, there are over 50 other blank rows. So it's like the formula doesn't correct itself, it keeps incrementing the number, but doesn't decrement.

It "tries" to correct itself; for example if I press delete in cell B of row "0005", the formula in D4 briefly shows "0005" but then switches back to "4150".

 

[bomberman411]

I don't understand why you can test it out and it's working for you as it should and it doesn't want to work properly for me anymore.

 

[schielrn]

Are the cells maybe filled with spaces that just aren't visible?

Are you able to send me the file here? Remove any confidential data or anything and I can take a look at it?