I need to always select the last populated cell of a column.

megslow · Mar 11th, 2002, 02:54 PM

ok, now that i'm registered, i'll try the question again...

My question:

I need to have a cell at the top of my sheet always show me what the value is in the last populated cell of one particular column. The value of the cell is numeric... actually a formula accruing vacation and sick time for employees of my company.

Can this be done with a formula? I have tried everything I can think of and looked everywhere I know. Please help!

Thanks,
Meg.

[ This Message was edited by: megslow on 2002-03-11 14:01 ]

Aladin Akyurek · Mar 11th, 2002, 02:57 PM

Quote:

On 2002-03-11 13:54, megslow wrote:
ok, now that i'm registered, i'll try the question again...

My question:

I need to have a cell at the top of my sheet always show me what the value is in the last populated cell of one particular column. The value of the cell is numeric... actually a formula accruing vacation and sick time for employees of my company.

Can this be done with a formula? I have tried everything I can think of and looked everywhere I know. Please help!

Thanks,
Meg.

Meg,

See my reply to your anonymous post.

Aladin

megslow · Mar 11th, 2002, 03:01 PM

If the column in question is "F", do I replace the "B" in the following formula with "F"?
=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))
(if so, i tried and it didn't work)

Aladin Akyurek · Mar 11th, 2002, 03:03 PM

Quote:

On 2002-03-11 14:01, megslow wrote:
If the column in question is "F", do I replace the "B" in the following formula with "F"?
=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))
(if so, i tried and it didn't work)

Yes, Meg. B:B becomes F:F. You need to enter the formula in F1.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-03-11 14:04 ]

megslow · Mar 12th, 2002, 06:42 AM

Ok, I tried the formula with the "F"s in cell F1 at home with sample data and it worked. However I cannot get it to work here at work where I need it. I am using Excel 2000 (home is XP). Will that make a difference? Also, there is some text in some of the cells in column F. Will that mess up the formula?

Thanks so much for your help so far...
Meg.

megslow · Mar 12th, 2002, 08:43 AM

new issue: the formula works in some workbooks but not others... weird. but the point is that i DID get it to work.
thank you, thank you, thank you!

(and any tips on why it may not work in some workbooks - like settings? - would be appreciated!!!)

Aladin Akyurek · Mar 12th, 2002, 09:23 AM

> Ok, I tried the formula with the "F"s in cell F1 at home with sample data and it worked.

> However I cannot get it to work here at work where I need it. I am using Excel 2000 (home is XP). Will that make a difference?

It works in Excel 2000. Glad to hear that it does work in XP.

> Also, there is some text in some of the cells in column F. Will that mess up the formula?

Match ignores text and blanks, so don't worry.

> Thanks so much for your help so far...

You're welcome.

Aladin

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 02:54 PM	#1
megslow New Member Join Date: Mar 2002 Location: Chattanooga, TN Posts: 35	ok, now that i'm registered, i'll try the question again... My question: I need to have a cell at the top of my sheet always show me what the value is in the last populated cell of one particular column. The value of the cell is numeric... actually a formula accruing vacation and sick time for employees of my company. Can this be done with a formula? I have tried everything I can think of and looked everywhere I know. Please help! Thanks, Meg. [ This Message was edited by: megslow on 2002-03-11 14:01 ]

Mar 11th, 2002, 03:01 PM	#3
megslow New Member Join Date: Mar 2002 Location: Chattanooga, TN Posts: 35	If the column in question is "F", do I replace the "B" in the following formula with "F"? =INDEX(B:B,MATCH(9.99999999999999E+307,B:B)) (if so, i tried and it didn't work)

Mar 12th, 2002, 06:42 AM	#5
megslow New Member Join Date: Mar 2002 Location: Chattanooga, TN Posts: 35	Ok, I tried the formula with the "F"s in cell F1 at home with sample data and it worked. However I cannot get it to work here at work where I need it. I am using Excel 2000 (home is XP). Will that make a difference? Also, there is some text in some of the cells in column F. Will that mess up the formula? Thanks so much for your help so far... Meg.

Mar 12th, 2002, 08:43 AM	#6
megslow New Member Join Date: Mar 2002 Location: Chattanooga, TN Posts: 35	new issue: the formula works in some workbooks but not others... weird. but the point is that i DID get it to work. thank you, thank you, thank you! (and any tips on why it may not work in some workbooks - like settings? - would be appreciated!!!)

Mar 12th, 2002, 09:23 AM	#7
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	> Ok, I tried the formula with the "F"s in cell F1 at home with sample data and it worked. > However I cannot get it to work here at work where I need it. I am using Excel 2000 (home is XP). Will that make a difference? It works in Excel 2000. Glad to hear that it does work in XP. > Also, there is some text in some of the cells in column F. Will that mess up the formula? Match ignores text and blanks, so don't worry. > Thanks so much for your help so far... You're welcome. Aladin