Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Last data in a column
I am trying to get Excel to sum the last entry in a column in one worksheet
with the last in another worksheet. The last entries may not be either Max or Min values, but the last entered, by the way, these values are the result of a calculation in the sheets. Can anyone help? -- Steve H |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Last data in a column
If there are NO blank entries in the columns then
=INDEX(A:A,COUNTA(A:A)) will give las entry in column A If there are blanks then: =LOOKUP(99^99,A:A) this will sum last entries in columns A & D =SUM(LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D)) or simply =LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D) HTH "Steve" wrote: I am trying to get Excel to sum the last entry in a column in one worksheet with the last in another worksheet. The last entries may not be either Max or Min values, but the last entered, by the way, these values are the result of a calculation in the sheets. Can anyone help? -- Steve H |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Last data in a column
=LOOKUP(2,1/(D1:D65535<""),D:D)+LOOKUP(2,1/(Sheet2!E1:E65535<""),Sheet2!E:E) In XL2007 =LOOKUP(2,1/(D:D<""),D:D)+LOOKUP(2,1/(Sheet2!E:E<""),Sheet2!E:E) "Steve" wrote: I am trying to get Excel to sum the last entry in a column in one worksheet with the last in another worksheet. The last entries may not be either Max or Min values, but the last entered, by the way, these values are the result of a calculation in the sheets. Can anyone help? -- Steve H |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Last data in a column
Teethless mama wrote...
=LOOKUP(2,1/(D1:D65535<""),D:D) +LOOKUP(2,1/(Sheet2!E1:E65535<""),Sheet2!E:E) .... What happens when the last entry in a column is nonnumeric text or an error value? Since the OP wants to sum the results, reasonably safe to assume OP wants to find the last number in the column, in which case shorter, more efficient and more robust to use =LOOKUP(1E+307,D:D)+LOOKUP(1E+307,SheetX!E:E) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Last data in a column
Thanks Toppers, the LOOKUP function works very well, but the INDEX function
gave quite varying results, sometimes the 3rd last or the 5th last in an date column (A), and nothing in columns with numbers , currency (F) or percentages. I came up with my own, slightly more complicated function, LOOKUP(MAXA($A$4:$A$53),$A$4:$A$53,F4:F53), which not only finds the last date, but uses that to return the value on that date. But anyway, Thanks for your reply, -- Steve H "Toppers" wrote: If there are NO blank entries in the columns then =INDEX(A:A,COUNTA(A:A)) will give las entry in column A If there are blanks then: =LOOKUP(99^99,A:A) this will sum last entries in columns A & D =SUM(LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D)) or simply =LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D) HTH "Steve" wrote: I am trying to get Excel to sum the last entry in a column in one worksheet with the last in another worksheet. The last entries may not be either Max or Min values, but the last entered, by the way, these values are the result of a calculation in the sheets. Can anyone help? -- Steve H |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Last data in a column
But you didn't say anything about dates or other columns in your original post?
"Steve" wrote: Thanks Toppers, the LOOKUP function works very well, but the INDEX function gave quite varying results, sometimes the 3rd last or the 5th last in an date column (A), and nothing in columns with numbers , currency (F) or percentages. I came up with my own, slightly more complicated function, LOOKUP(MAXA($A$4:$A$53),$A$4:$A$53,F4:F53), which not only finds the last date, but uses that to return the value on that date. But anyway, Thanks for your reply, -- Steve H "Toppers" wrote: If there are NO blank entries in the columns then =INDEX(A:A,COUNTA(A:A)) will give las entry in column A If there are blanks then: =LOOKUP(99^99,A:A) this will sum last entries in columns A & D =SUM(LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D)) or simply =LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D) HTH "Steve" wrote: I am trying to get Excel to sum the last entry in a column in one worksheet with the last in another worksheet. The last entries may not be either Max or Min values, but the last entered, by the way, these values are the result of a calculation in the sheets. Can anyone help? -- Steve H |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Last data in a column
Yes, I am sorry, but I did'nt see that that would be a problem. I am a
"virgin" in dealing with people that can figure out a problem like this without turning a hair. I was unable to find a function that I could see being able to do the job, so I thought I'd ask the experts. I just wanted to find a "simple or elegant" way to find the row of last data in a column, and use the row reference to point to other data. Next time I'll be precise, I promise! -- Steve H "JLatham" wrote: But you didn't say anything about dates or other columns in your original post? "Steve" wrote: Thanks Toppers, the LOOKUP function works very well, but the INDEX function gave quite varying results, sometimes the 3rd last or the 5th last in an date column (A), and nothing in columns with numbers , currency (F) or percentages. I came up with my own, slightly more complicated function, LOOKUP(MAXA($A$4:$A$53),$A$4:$A$53,F4:F53), which not only finds the last date, but uses that to return the value on that date. But anyway, Thanks for your reply, -- Steve H "Toppers" wrote: If there are NO blank entries in the columns then =INDEX(A:A,COUNTA(A:A)) will give las entry in column A If there are blanks then: =LOOKUP(99^99,A:A) this will sum last entries in columns A & D =SUM(LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D)) or simply =LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D) HTH "Steve" wrote: I am trying to get Excel to sum the last entry in a column in one worksheet with the last in another worksheet. The last entries may not be either Max or Min values, but the last entered, by the way, these values are the result of a calculation in the sheets. Can anyone help? -- Steve H |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Last data in a column
Quite often the type of information being dealt with has an impact on finding
a good solution. Numbers, words and dates are all different animals and have to be dealt with accordingly. I'm sure Toppers will be pleased to know that his suggestion helped you come up with a 'spot-on' solution in the end. And that is what it's all about. Sometimes it's better to provide a little too much information - the person reading your request for help can just ignore that part. But not giving enough can not only be a bit frustrating to all concerned, but can cause 'downstream' problems. Example: someone asked how to move certain rows from one sheet onto another. I provided code to do that. Then they asked how to change all sheet references in formulas to a different sheet reference. I did that. THEN they complained that suddenly their workbook is acting all screwy and bogging down. The two requests were simple enough when looked at separately, but I've come to realize that the two of them together have probably almost hopelessly left Excel with a bunch of formulas that either don't reference the proper information or don't even reference cells with values in them at all. But I really wasn't getting on your case so much as just trying to defend Toppers a little - did a good job with the info provided. Don't feel bad, you're not the first to get into this situation, and I am absolutely certain that you won't be the last <g. On the plus side, what you did ask for, you did so clearly and succinctly - sometimes that is a rare animal to find in this zoo also. "Steve" wrote: Yes, I am sorry, but I did'nt see that that would be a problem. I am a "virgin" in dealing with people that can figure out a problem like this without turning a hair. I was unable to find a function that I could see being able to do the job, so I thought I'd ask the experts. I just wanted to find a "simple or elegant" way to find the row of last data in a column, and use the row reference to point to other data. Next time I'll be precise, I promise! -- Steve H "JLatham" wrote: But you didn't say anything about dates or other columns in your original post? "Steve" wrote: Thanks Toppers, the LOOKUP function works very well, but the INDEX function gave quite varying results, sometimes the 3rd last or the 5th last in an date column (A), and nothing in columns with numbers , currency (F) or percentages. I came up with my own, slightly more complicated function, LOOKUP(MAXA($A$4:$A$53),$A$4:$A$53,F4:F53), which not only finds the last date, but uses that to return the value on that date. But anyway, Thanks for your reply, -- Steve H "Toppers" wrote: If there are NO blank entries in the columns then =INDEX(A:A,COUNTA(A:A)) will give las entry in column A If there are blanks then: =LOOKUP(99^99,A:A) this will sum last entries in columns A & D =SUM(LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D)) or simply =LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D) HTH "Steve" wrote: I am trying to get Excel to sum the last entry in a column in one worksheet with the last in another worksheet. The last entries may not be either Max or Min values, but the last entered, by the way, these values are the result of a calculation in the sheets. Can anyone help? -- Steve H |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find the last instance of data in a column | Excel Worksheet Functions | |||
Find first non-blank or non-zero in a column of data | Excel Discussion (Misc queries) | |||
how to find last cell in column with data | New Users to Excel | |||
HOW DO I FIND DATA IN A TABLE BY LOOKING UP BOTH THE COLUMN AND R. | Excel Worksheet Functions | |||
find rows for unique data in 1 column and different data in other. | Excel Discussion (Misc queries) |