Copy last cell with data in column E on one sheet to cell on anoth

[Seahawk]

From a cell on my "Summary" worksheet, I need to find the last cell that has
data in it in Column E on the "Account" worksheet and copy the data from that
cell to the cell on my Summary worksheet. There are actually 10 or more
"Account" sheets (each separately named) so I really don't want to do this
manually. I've tried several suggestions from similar threads, using both
the LOOKUP and IF functions but nothing has worked.
Any help will be much appreciated. Thanks.

 

[Gary''s Student]

1. Where does the data start in column E?
2. Is the data numbers or text?
3. Are there any blanks in the midst of the data?

 

[Seahawk]

1. Data starts with cell E2 -- the heading (cell E1) is "Ending Balance," if
that helps.
2. Always numbers (currency).
3. One of the Account sheets does have a blank cell in the midst, so I
imagine it could happen again on that same sheet or another.

 

[Gary''s Student]

=LOOKUP(2,1/(E2:E1000>0),E2:E1000) should work even with blanks in the range

 

[Ashish Mathur]

Hi,

You can use this =lookup(99^99,range)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[bosco_yip]

A] You have "10 or more Account sheets" with different name

B] You want to find the last cell data in Column E of all "10 or more
Account sheets"

Then, please follow the below :

1] Define (Insert > Define > Name) the following...

Name, enter : SheetList

Refers to, enter :

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,30)

Click OK

2] Summary sheet, B1 enter : Account name

3] Summary sheet, B2 enter the formula and copied down :

=IF(ROW(2:2)>COUNTA(SheetList),"",INDEX(SheetList,ROW(2:2)))

4] Summary sheet, C1 enter : Last data

5] Summary sheet, C2 enter the formula and copied down :

=IF(ROW(2:2)>COUNTA(SheetList),"",LOOKUP(9.99E+307,INDIRECT(INDEX(SheetList,ROW(2:2))&"!E:E")))


Regards
Bosco

 

[Seahawk]

My thanks to Gary"s Student, Ashish Mathur, and bosco_yip for your helpful
information. When all was said and done, Ashish's response was the right one
-- it worked perfectly!

Thank you all for taking the time to respond to my question.

Ashish, if you would be so kind, would you tell me what the "99^99"
means/does in the formula? Obviously it works but I'm curious what it is
telling Excel to do.

Seahawk

A] You have "10 or more Account sheets" with different name

B] You want to find the last cell data in Column E of all "10 or more
Account sheets"

Then, please follow the below :

1] Define (Insert > Define > Name) the following...

Name, enter : SheetList

Refers to, enter :

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,30)

Click OK

2] Summary sheet, B1 enter : Account name

3] Summary sheet, B2 enter the formula and copied down :

=IF(ROW(2:2)>COUNTA(SheetList),"",INDEX(SheetList,ROW(2:2)))

4] Summary sheet, C1 enter : Last data

5] Summary sheet, C2 enter the formula and copied down :

=IF(ROW(2:2)>COUNTA(SheetList),"",LOOKUP(9.99E+307,INDIRECT(INDEX(SheetList,ROW(2:2))&"!E:E")))


Regards
Bosco

From a cell on my "Summary" worksheet, I need to find the last cell that has
data in it in Column E on the "Account" worksheet and copy the data from that
cell to the cell on my Summary worksheet. There are actually 10 or more
"Account" sheets (each separately named) so I really don't want to do this
manually. I've tried several suggestions from similar threads, using both
the LOOKUP and IF functions but nothing has worked.
Any help will be much appreciated. Thanks.

 

[Ashish Mathur]

Hi,

Thank you for the feedback. it is just a very large number - you can input
any other large number

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

My thanks to Gary"s Student, Ashish Mathur, and bosco_yip for your helpful
information. When all was said and done, Ashish's response was the right
one
-- it worked perfectly!

Thank you all for taking the time to respond to my question.

Ashish, if you would be so kind, would you tell me what the "99^99"
means/does in the formula? Obviously it works but I'm curious what it is
telling Excel to do.

Seahawk

A] You have "10 or more Account sheets" with different name

B] You want to find the last cell data in Column E of all "10 or more
Account sheets"

Then, please follow the below :

1] Define (Insert > Define > Name) the following...

Name, enter : SheetList

Refers to, enter :

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,30)

Click OK

2] Summary sheet, B1 enter : Account name

3] Summary sheet, B2 enter the formula and copied down :

=IF(ROW(2:2)>COUNTA(SheetList),"",INDEX(SheetList,ROW(2:2)))

4] Summary sheet, C1 enter : Last data

5] Summary sheet, C2 enter the formula and copied down :

=IF(ROW(2:2)>COUNTA(SheetList),"",LOOKUP(9.99E+307,INDIRECT(INDEX(SheetList,ROW(2:2))&"!E:E")))


Regards
Bosco

From a cell on my "Summary" worksheet, I need to find the last cell
that has
data in it in Column E on the "Account" worksheet and copy the data
from that
cell to the cell on my Summary worksheet. There are actually 10 or
more
"Account" sheets (each separately named) so I really don't want to do
this
manually. I've tried several suggestions from similar threads, using
both
the LOOKUP and IF functions but nothing has worked.
Any help will be much appreciated. Thanks.