Last cell of a column...

[HRSylver]

Hey all. I have a workbook with a sheet for each month of the year so far in 2008. At the end I have a summary sheet that gives me some specific information with what's on each month's pages... is there a way to write a formula on my summary page that will return the last cell of Column A in any given sheet? The last cell at the bottom of each Column is a distinct count. Under the summary for each month i want to return that number. It's easy enough to just say "='Jan2008'!A4150" and then "='Feb2008'!A4193" but I was hoping there was one formula i could write in both cells to get me what i want... Does this make sense?

[VoG]

To return the last numeric value in column A of Sheet1

=VLOOKUP(9.99999999999999E+307,Sheet1!A:A,1)

[stanleydgromjr]

HRSylver,

Jan2008

	A
1
2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	63.00
13

Spreadsheet Formulas

Cell Formula A12 =SUM(A3:A11)

Excel tables to the web >> Excel Jeanie HTML 4


Feb2008

	A
1
2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	228.00
23

Spreadsheet Formulas

Cell Formula A22 =SUM(A3:A21)

Excel tables to the web >> Excel Jeanie HTML 4


Summary

	A	B	C
1		Jan2008	Feb2008
2
3
4
5
6
7
8	Month Total	63.00	228.00
9
10
11

Spreadsheet Formulas

Cell Formula B8 =VLOOKUP(9.99999999999999E+307,Jan2008!A:A,1) C8 =VLOOKUP(9.99999999999999E+307,Feb2008!A:A,1)

Excel tables to the web >> Excel Jeanie HTML 4



Have a great day,
Stan

[HRSylver]

You guys are AWESOME

[mgirvin]

Dear HRSylver,

=LOOKUP(9.99999999999999E+307,Items!A:A)

will work also.

[HalfAce]

To take this a step further...
Say your sheets are named "Jan", "Feb", etc.
And on your summary sheet you have the columns for each month with headers of "Jan" (In A1) and ,"Feb" (in B1), etc.
You can use the lookup formula that VoG supplied along with the indirect function to use the header of each column to specify the sheet that column should be referencing.
Try something like this.
(For the Jan value)...

=VLOOKUP(9.99999999999999E+307,INDIRECT(A1&"!A:A"),1)

(For the Feb value)...

=VLOOKUP(9.99999999999999E+307,INDIRECT(B1&"!A:A"),1)

Same formula for all columns except for the column reference in the indirect function.

Hope it helps.

[HRSylver]

wow..... cool... Thanks!

[HalfAce]

HRSylver,
You might want to check your thread again. You may have been replying to mgirvin as I was posting to your statement "one formula i could write in both cells to get me what i want"

[HRSylver]

i did see yours my friend. I believe my wording was ill chosen. Everyone's formula worked fine. What i was looking for was a formula that I could put in so I wouldn't have to go check what the number was each time. (basically being lazy and not wanting to shift between 13 sheets...) you formula makes it all the more intense because I can just copy paste through the cells. Very nice...

[HalfAce]

Originally Posted by HRSylver

...just copy paste through the cells...basically being lazy

Yep, that's what I'm all about.