Find last cell in range (formula)

[AMAS]

Hi,

I have been fiddling around with this but am missing something in my excel logic... Maybe someone can spot what is missing.

This formula works to find the row number of the last cell with text in column A:

=MATCH("*",A:A,-1)

This formula counts the number of cells in column B that are nonblank between B2 and B20:

=COUNTA(B2:B20)

Here's my attempt at combining them to create a range based on the last cell with text in column A:

=COUNTA(B2:B&(MATCH("*",A:A,-1)))

Any thoughts?

AMAS

 

[barry houdini]

Try INDEX, i.e.

=COUNTA(B2:INDEX(B:B,MATCH("*",A:A,-1)))

 

[AMAS]

Thanks Barry. Works perfectly.

AMAS

 

[AMAS]

Hi Barry,

In my actual formula the forumla is put in the same column as the data (e.g. Column A) and so I am getting a circular error.

So I am trying to put your formula in A1, but when I replace A:A with A1:A in the Match portion of the formula I get another error.

What should I do to circuvent this problem?

Thanks.

AMAS

 

[Aladin Akyurek]

Hi Barry,

In my actual formula the forumla is put in the same column as the data (e.g. Column A) and so I am getting a circular error.

So I am trying to put your formula in A1, but when I replace A:A with A1:A in the Match portion of the formula I get another error.

What should I do to circuvent this problem?

Thanks.

AMAS

In such cases one would avoid using a cell in a reference one's formula uses.

By the way,

MATCH("*",A:A,-1)

is not reliable.

MATCH(REPT("z",255),A:A)

would be appropriate.

But, if you insist on having the formula in A1, try:

A1:

=COUNTIF(B2:INDEX(B:B,MATCH(REPT("z",255),A2:INDEX(A:A,ROWS(A:A)))),"?*")

COUNTA would count everything in the calculated B-range. If that's intended:

=COUNTA(B2:INDEX(B:B,MATCH(REPT("z",255),A2:INDEX(A:A,ROWS(A:A)))))

 

[AMAS]

Hi Aladin,

Thanks for posting this. It has been very helpful. Final question, but not a big issue. I have added your modifications to my original formula. This allows the start of the range to be hard coded while the end of the range to be determined by the last entry. This way I don't have to change the range every time new data is added. The problem I have found is that when no data is added yet (just a sheet with the row and column headers and formulas), I get a circular error. Once I add any data, the error goes away. I have tried to wrap the formula with IFERROR(myFormula,"") with no resolution to the problem. Do you know any other tricks I can employ?

Thanks in advance.

AMAS

 

[Aladin Akyurek]

Hi Aladin,

Thanks for posting this. It has been very helpful. Final question, but not a big issue. I have added your modifications to my original formula. This allows the start of the range to be hard coded while the end of the range to be determined by the last entry. This way I don't have to change the range every time new data is added. The problem I have found is that when no data is added yet (just a sheet with the row and column headers and formulas), I get a circular error. Once I add any data, the error goes away. I have tried to wrap the formula with IFERROR(myFormula,"") with no resolution to the problem. Do you know any other tricks I can employ?

Thanks in advance.

AMAS

You don't need to hard code anything.

=COUNTIF(B2:INDEX(B:B,MATCH(REPT("z",255),A2:INDEX(A:A,ROWS(A:A)))),"?*")

will return #N/A if there is no data from A2 downwards.

=COUNTA(B2:INDEX(B:B,MATCH(REPT("z",255),A2:INDEX(A:A,ROWS(A:A)))))

would return 1 for COUNTA counts everything including an #N/A result.

What did you do exactly?

 

[AMAS]

Hi Aladin,

I think my post was not very clear. Let me post my full formula which I adapted using your described technique. My computer is unhappy with Windows 7 that I installed over the weekend and I am still troubleshooting. Once I get it up and running I will repost.

AMAS

 

[AMAS]

Hi Aladin,

In F7 I put this formula:

=IF(ISBLANK(G7),IF(SUM(COUNTIF(H7:(INDEX(7:7,MATCH(REPT("z",255),F7:INDEX(7:7,COLUMNS(7:7)))+6)),{"Exclude","Include","Unsure"}))<2,"",IF(SUM(COUNTIF(H7:(INDEX(7:7,MATCH(REPT("z",255),F7:INDEX(7:7,COLUMNS(7:7)))+6)),{"Exclude","Include","Unsure"}))>2,"Incorrect number of responses. Please check.",LOOKUP(SUM(COUNTIF(H7:(INDEX(7:7,MATCH(REPT("z",255),F7:INDEX(7:7,COLUMNS(7:7)))+6)),{"Exclude","Include","Unsure"})*{100,10,1}),{2,11,101,200},{"Unsure","Include","Conflict","Exclude"}))),G7)

It does the following tasks:

1. Check if G7 is Blank and if not then the decision in G7 over-rights all other decisions.
2. Check from H7 to the last entry (e.g. decision) in the same row on the right. This I used to change depending on where my final entry was. The H7 is what I meant by as hard-coding. I don't care about what is to the left of F7, I care about what is on its right.
3. Check if there were only two decision. If only one then keep F7 blank. If more than two then give a message "Incorrect number of responses. Please check." If only two decisions then use the formula to decide whether the final decision should be Unsure, Include, Exclude or if there is a conflict that needs to be resolved.

This formula has been very beneficial. My only problem with it that I had to keep changing the right limit of the formula when I added new data to right of the current range.

The formula (modified with your code) works fine as long as I have some text to the right of F7. If not, then it gives me a circular error and a zero in F7.

I hope that my explanation was clear, but if not, I can explain further.

AMAS

 

[AMAS]

Additionally, I want to make my workbook a little lighter. Should I put this formula as a worksheet change event and only print the value to the cell? Will this make it faster or just keep it as a formula?

AMAS