Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
I believe there is a formula that has something about max(row, etc), but
cannot recall it. Col A would contain a list of numbers. I'm trying to find the first empty (blank) cell in col A. Even tho I could write a macro to do this, I'm needing the cell formula. Your help is greatly appreciated!! Don |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
=MATCH(TRUE,A1:A10="",0)
entered with ctrl + shift & enter will find the first and return the index number so using A1:A10 and the first empty is in A4 it will return 4 -- Regards, Peo Sjoblom "DW" wrote in message ... I believe there is a formula that has something about max(row, etc), but cannot recall it. Col A would contain a list of numbers. I'm trying to find the first empty (blank) cell in col A. Even tho I could write a macro to do this, I'm needing the cell formula. Your help is greatly appreciated!! Don |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
Thanks Peo, but, (and I probably didn't make myself clear, sorry),
let's say, A4 is first empty, how would I get the cell value of A3? Hope this clears it up. Again, sorry. Don "Peo Sjoblom" wrote in message ... =MATCH(TRUE,A1:A10="",0) entered with ctrl + shift & enter will find the first and return the index number so using A1:A10 and the first empty is in A4 it will return 4 -- Regards, Peo Sjoblom "DW" wrote in message ... I believe there is a formula that has something about max(row, etc), but cannot recall it. Col A would contain a list of numbers. I'm trying to find the first empty (blank) cell in col A. Even tho I could write a macro to do this, I'm needing the cell formula. Your help is greatly appreciated!! Don |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
=INDEX(A1:A10,MATCH(TRUE,A1:A10="",1)-1,0)
Don't forget to use the ctrl-shift enter combination to enter this formula -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "DW" wrote: Thanks Peo, but, (and I probably didn't make myself clear, sorry), let's say, A4 is first empty, how would I get the cell value of A3? Hope this clears it up. Again, sorry. Don "Peo Sjoblom" wrote in message ... =MATCH(TRUE,A1:A10="",0) entered with ctrl + shift & enter will find the first and return the index number so using A1:A10 and the first empty is in A4 it will return 4 -- Regards, Peo Sjoblom "DW" wrote in message ... I believe there is a formula that has something about max(row, etc), but cannot recall it. Col A would contain a list of numbers. I'm trying to find the first empty (blank) cell in col A. Even tho I could write a macro to do this, I'm needing the cell formula. Your help is greatly appreciated!! Don |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
My bad, =INDEX(A1:A10,MATCH(TRUE,A1:A10="",0)-1,0)
Note that in the match formula the last parameter must be zero. Sorry about that -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Michael" wrote: =INDEX(A1:A10,MATCH(TRUE,A1:A10="",1)-1,0) Don't forget to use the ctrl-shift enter combination to enter this formula -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "DW" wrote: Thanks Peo, but, (and I probably didn't make myself clear, sorry), let's say, A4 is first empty, how would I get the cell value of A3? Hope this clears it up. Again, sorry. Don "Peo Sjoblom" wrote in message ... =MATCH(TRUE,A1:A10="",0) entered with ctrl + shift & enter will find the first and return the index number so using A1:A10 and the first empty is in A4 it will return 4 -- Regards, Peo Sjoblom "DW" wrote in message ... I believe there is a formula that has something about max(row, etc), but cannot recall it. Col A would contain a list of numbers. I'm trying to find the first empty (blank) cell in col A. Even tho I could write a macro to do this, I'm needing the cell formula. Your help is greatly appreciated!! Don |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
Ctrl+Shift+Enter
=INDEX(A1:A10,MATCH(TRUE,A1:A10="",0)-1,0) DW wrote: Thanks Peo, but, (and I probably didn't make myself clear, sorry), let's say, A4 is first empty, how would I get the cell value of A3? Hope this clears it up. Again, sorry. Don "Peo Sjoblom" wrote in message ... =MATCH(TRUE,A1:A10="",0) entered with ctrl + shift & enter will find the first and return the index number so using A1:A10 and the first empty is in A4 it will return 4 -- Regards, Peo Sjoblom "DW" wrote in message ... I believe there is a formula that has something about max(row, etc), but cannot recall it. Col A would contain a list of numbers. I'm trying to find the first empty (blank) cell in col A. Even tho I could write a macro to do this, I'm needing the cell formula. Your help is greatly appreciated!! Don |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
And you don't need to use CSE to commit it.
Pete On Oct 8, 6:52 pm, Michael wrote: My bad, =INDEX(A1:A10,MATCH(TRUE,A1:A10="",0)-1,0) Note that in the match formula the last parameter must be zero. Sorry about that -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Michael" wrote: =INDEX(A1:A10,MATCH(TRUE,A1:A10="",1)-1,0) Don't forget to use the ctrl-shift enter combination to enter this formula -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "DW" wrote: Thanks Peo, but, (and I probably didn't make myself clear, sorry), let's say, A4 is first empty, how would I get the cell value of A3? Hope this clears it up. Again, sorry. Don "Peo Sjoblom" wrote in message ... =MATCH(TRUE,A1:A10="",0) entered with ctrl + shift & enter will find the first and return the index number so using A1:A10 and the first empty is in A4 it will return 4 -- Regards, Peo Sjoblom "DW" wrote in message ... I believe there is a formula that has something about max(row, etc), but cannot recall it. Col A would contain a list of numbers. I'm trying to find the first empty (blank) cell in col A. Even tho I could write a macro to do this, I'm needing the cell formula. Your help is greatly appreciated!! Don- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
You do need to enter it with ctrl + shift & enter
-- Regards, Peo Sjoblom "Pete_UK" wrote in message ups.com... And you don't need to use CSE to commit it. Pete On Oct 8, 6:52 pm, Michael wrote: My bad, =INDEX(A1:A10,MATCH(TRUE,A1:A10="",0)-1,0) Note that in the match formula the last parameter must be zero. Sorry about that -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Michael" wrote: =INDEX(A1:A10,MATCH(TRUE,A1:A10="",1)-1,0) Don't forget to use the ctrl-shift enter combination to enter this formula -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "DW" wrote: Thanks Peo, but, (and I probably didn't make myself clear, sorry), let's say, A4 is first empty, how would I get the cell value of A3? Hope this clears it up. Again, sorry. Don "Peo Sjoblom" wrote in message ... =MATCH(TRUE,A1:A10="",0) entered with ctrl + shift & enter will find the first and return the index number so using A1:A10 and the first empty is in A4 it will return 4 -- Regards, Peo Sjoblom "DW" wrote in message ... I believe there is a formula that has something about max(row, etc), but cannot recall it. Col A would contain a list of numbers. I'm trying to find the first empty (blank) cell in col A. Even tho I could write a macro to do this, I'm needing the cell formula. Your help is greatly appreciated!! Don- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
Thanks everyone....
Now, this might be a dumb question, but why the ctrl+shift + enter combo to commit the formula? Don |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
It's this part
A1:A10="" it's what is called an array formula, you can lookup array formula in help http://www.cpearson.com/excel/ArrayFormulas.aspx -- Regards, Peo Sjoblom "DW" wrote in message ... Thanks everyone.... Now, this might be a dumb question, but why the ctrl+shift + enter combo to commit the formula? Don |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
Well, Peo, there seems to be something strange here. I copied the
formula from the ng straight into a blank workbook in C1, and it returned 0. Then I started to enter numbers in A1 onwards - 1, 2, 3, 4, 5 etc and it seemed to work. Then I deleted the 3 and it returned 2, as expected. That's why I posted to say that you didn't need the CSE. Following your post, I edited C1 and committed with CSE - it made no difference, but when I edited it again and just used Enter it returned #N/A. I then copied the formula again into C1, C3 and C7 to compare them with & without CSE. The main difference seems to be if A1 is blank - the CSE formula returns 0 in all cases, whereas the non-CSE form returns the row number that the formula is on. Editting the formula and using Enter to commit also returns #N/A. I'll have to investigate a bit more ... Pete On Oct 8, 7:25 pm, "Peo Sjoblom" wrote: You do need to enter it with ctrl + shift & enter -- Regards, Peo Sjoblom "Pete_UK" wrote in message ups.com... And you don't need to use CSE to commit it. Pete On Oct 8, 6:52 pm, Michael wrote: My bad, =INDEX(A1:A10,MATCH(TRUE,A1:A10="",0)-1,0) Note that in the match formula the last parameter must be zero. Sorry about that -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Michael" wrote: =INDEX(A1:A10,MATCH(TRUE,A1:A10="",1)-1,0) Don't forget to use the ctrl-shift enter combination to enter this formula -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "DW" wrote: Thanks Peo, but, (and I probably didn't make myself clear, sorry), let's say, A4 is first empty, how would I get the cell value of A3? Hope this clears it up. Again, sorry. Don "Peo Sjoblom" wrote in message ... =MATCH(TRUE,A1:A10="",0) entered with ctrl + shift & enter will find the first and return the index number so using A1:A10 and the first empty is in A4 it will return 4 -- Regards, Peo Sjoblom "DW" wrote in message ... I believe there is a formula that has something about max(row, etc), but cannot recall it. Col A would contain a list of numbers. I'm trying to find the first empty (blank) cell in col A. Even tho I could write a macro to do this, I'm needing the cell formula. Your help is greatly appreciated!! Don- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
Ok, thanks........will this also work on a list that is not in any sort
order or does it have to be sorted? Don "Peo Sjoblom" wrote in message ... It's this part A1:A10="" it's what is called an array formula, you can lookup array formula in help http://www.cpearson.com/excel/ArrayFormulas.aspx -- Regards, Peo Sjoblom "DW" wrote in message ... Thanks everyone.... Now, this might be a dumb question, but why the ctrl+shift + enter combo to commit the formula? Don |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
Sorting is not necessary
-- Regards, Peo Sjoblom "DW" wrote in message ... Ok, thanks........will this also work on a list that is not in any sort order or does it have to be sorted? Don "Peo Sjoblom" wrote in message ... It's this part A1:A10="" it's what is called an array formula, you can lookup array formula in help http://www.cpearson.com/excel/ArrayFormulas.aspx -- Regards, Peo Sjoblom "DW" wrote in message ... Thanks everyone.... Now, this might be a dumb question, but why the ctrl+shift + enter combo to commit the formula? Don |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
Ok, did what you suggested.......but am getting a 0 value....below is my
current list (it of course grows every day), starting in A1. Now, with A19 being blank, the formula should return 8.50, but it's returning 0. 6.00 13.83 22.33 22.33 0.00 8.50 17.00 17.00 0.00 8.50 17.00 25.50 33.50 42.00 42.00 0.00 8.50 Don "Peo Sjoblom" wrote in message ... It's this part A1:A10="" it's what is called an array formula, you can lookup array formula in help http://www.cpearson.com/excel/ArrayFormulas.aspx -- Regards, Peo Sjoblom "DW" wrote in message ... Thanks everyone.... Now, this might be a dumb question, but why the ctrl+shift + enter combo to commit the formula? Don |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
You've snipped so much of the context that we can't see what formula you are
trying to use. -- David Biddulph "DW" wrote in message ... Ok, did what you suggested.......but am getting a 0 value....below is my current list (it of course grows every day), starting in A1. Now, with A19 being blank, the formula should return 8.50, but it's returning 0. 6.00 13.83 22.33 22.33 0.00 8.50 17.00 17.00 0.00 8.50 17.00 25.50 33.50 42.00 42.00 0.00 8.50 Don "Peo Sjoblom" wrote in message ... It's this part A1:A10="" it's what is called an array formula, you can lookup array formula in help http://www.cpearson.com/excel/ArrayFormulas.aspx -- Regards, Peo Sjoblom "DW" wrote in message ... Thanks everyone.... Now, this might be a dumb question, but why the ctrl+shift + enter combo to commit the formula? Don |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
ok.........sorry you weren't able to follow......here's the whole
thing.............. I'm trying to find the first blank cell in a column and then go up one cell to capture that cell value....I am then using that value in a simple subtraction. My calculation is thus: cell B1: =70-C1 cell C1: =INDEX(A1:A30,MATCH(TRUE,A1:A30="",0)-1,0) using separate cells just so I can see the formula results My list is: 6.00 13.83 22.33 22.33 0.00 8.50 17.00 17.00 0.00 8.50 17.00 25.50 33.50 42.00 42.00 0.00 8.50 The list, of course, grows daily. Don "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... You've snipped so much of the context that we can't see what formula you are trying to use. -- David Biddulph "DW" wrote in message ... Ok, did what you suggested.......but am getting a 0 value....below is my current list (it of course grows every day), starting in A1. Now, with A19 being blank, the formula should return 8.50, but it's returning 0. 6.00 13.83 22.33 22.33 0.00 8.50 17.00 17.00 0.00 8.50 17.00 25.50 33.50 42.00 42.00 0.00 8.50 Don "Peo Sjoblom" wrote in message ... It's this part A1:A10="" it's what is called an array formula, you can lookup array formula in help http://www.cpearson.com/excel/ArrayFormulas.aspx -- Regards, Peo Sjoblom "DW" wrote in message ... Thanks everyone.... Now, this might be a dumb question, but why the ctrl+shift + enter combo to commit the formula? Don |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
You said previously "the formula should return 8.50, but it's returning 0",
but with those data values & those formulae (remembering that the C1 formula needs to be array-entered), C1 returns 8.5 for me, so I don't know how you've got zero. -- David Biddulph "DW" wrote in message ... ok.........sorry you weren't able to follow......here's the whole thing.............. I'm trying to find the first blank cell in a column and then go up one cell to capture that cell value....I am then using that value in a simple subtraction. My calculation is thus: cell B1: =70-C1 cell C1: =INDEX(A1:A30,MATCH(TRUE,A1:A30="",0)-1,0) using separate cells just so I can see the formula results My list is: 6.00 13.83 22.33 22.33 0.00 8.50 17.00 17.00 0.00 8.50 17.00 25.50 33.50 42.00 42.00 0.00 8.50 The list, of course, grows daily. Don "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... You've snipped so much of the context that we can't see what formula you are trying to use. -- David Biddulph "DW" wrote in message ... Ok, did what you suggested.......but am getting a 0 value....below is my current list (it of course grows every day), starting in A1. Now, with A19 being blank, the formula should return 8.50, but it's returning 0. 6.00 13.83 22.33 22.33 0.00 8.50 17.00 17.00 0.00 8.50 17.00 25.50 33.50 42.00 42.00 0.00 8.50 Don "Peo Sjoblom" wrote in message ... It's this part A1:A10="" it's what is called an array formula, you can lookup array formula in help http://www.cpearson.com/excel/ArrayFormulas.aspx -- Regards, Peo Sjoblom "DW" wrote in message ... Thanks everyone.... Now, this might be a dumb question, but why the ctrl+shift + enter combo to commit the formula? Don |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
If the list grows daily then make sure the range covers it, otherwise
you'll soon use up the 30 cells covered by the formula. As it is an array formula, you will need to commit it with CSE. Hope this helps. Pete On Oct 10, 10:08 am, "DW" wrote: ok.........sorry you weren't able to follow......here's the whole thing.............. I'm trying to find the first blank cell in a column and then go up one cell to capture that cell value....I am then using that value in a simple subtraction. My calculation is thus: cell B1: =70-C1 cell C1: =INDEX(A1:A30,MATCH(TRUE,A1:A30="",0)-1,0) using separate cells just so I can see the formula results My list is: 6.00 13.83 22.33 22.33 0.00 8.50 17.00 17.00 0.00 8.50 17.00 25.50 33.50 42.00 42.00 0.00 8.50 The list, of course, grows daily. Don "David Biddulph" <groups [at] biddulph.org.uk wrote in ... You've snipped so much of the context that we can't see what formula you are trying to use. -- David Biddulph "DW" wrote in message ... Ok, did what you suggested.......but am getting a 0 value....below is my current list (it of course grows every day), starting in A1. Now, with A19 being blank, the formula should return 8.50, but it's returning 0. 6.00 13.83 22.33 22.33 0.00 8.50 17.00 17.00 0.00 8.50 17.00 25.50 33.50 42.00 42.00 0.00 8.50 Don "Peo Sjoblom" wrote in message .. . It's this part A1:A10="" it's what is called an array formula, you can lookup array formula in help http://www.cpearson.com/excel/ArrayFormulas.aspx -- Regards, Peo Sjoblom "DW" wrote in message . .. Thanks everyone.... Now, this might be a dumb question, but why the ctrl+shift + enter combo to commit the formula? Don- Hide quoted text - - Show quoted text - |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Find first empty cell in column
Well, I understand that the index formula should have returned 8.50, but
I've reentered the formula and committed it (with CSE) and the value continues to be 0. Don p.s., how here is a thought, could the index formula be returning 0 because not all cells in the "array" are filled yet? If only, say, 25 cells have values, that should not cause the result to be 0, should it? "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... You said previously "the formula should return 8.50, but it's returning 0", but with those data values & those formulae (remembering that the C1 formula needs to be array-entered), C1 returns 8.5 for me, so I don't know how you've got zero. -- David Biddulph "DW" wrote in message ... ok.........sorry you weren't able to follow......here's the whole thing.............. I'm trying to find the first blank cell in a column and then go up one cell to capture that cell value....I am then using that value in a simple subtraction. My calculation is thus: cell B1: =70-C1 cell C1: =INDEX(A1:A30,MATCH(TRUE,A1:A30="",0)-1,0) using separate cells just so I can see the formula results My list is: 6.00 13.83 22.33 22.33 0.00 8.50 17.00 17.00 0.00 8.50 17.00 25.50 33.50 42.00 42.00 0.00 8.50 The list, of course, grows daily. Don "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... You've snipped so much of the context that we can't see what formula you are trying to use. -- David Biddulph "DW" wrote in message ... Ok, did what you suggested.......but am getting a 0 value....below is my current list (it of course grows every day), starting in A1. Now, with A19 being blank, the formula should return 8.50, but it's returning 0. 6.00 13.83 22.33 22.33 0.00 8.50 17.00 17.00 0.00 8.50 17.00 25.50 33.50 42.00 42.00 0.00 8.50 Don "Peo Sjoblom" wrote in message ... It's this part A1:A10="" it's what is called an array formula, you can lookup array formula in help http://www.cpearson.com/excel/ArrayFormulas.aspx -- Regards, Peo Sjoblom "DW" wrote in message ... Thanks everyone.... Now, this might be a dumb question, but why the ctrl+shift + enter combo to commit the formula? Don |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find empty cells, fill with text from cell above | Excel Worksheet Functions | |||
find first empty cell in column and start transpose next row in that cell | Excel Discussion (Misc queries) | |||
find last none empty cell | Excel Discussion (Misc queries) | |||
Find an empty cell and put a dash in it? | Excel Worksheet Functions | |||
Find a empty cell in next column | Excel Discussion (Misc queries) |