MS office Excel

on
I want to build a formula where I can get the last cell data in a row.
For Example
A       B    C      D    E
John  15   20    33
Lee    12   38          49

So I want build a formula in Column H where I can get the last cell amount for the row.
How I can do it and one more thing is there anyway where I can copy 20 sheets data and paste in one sheet without copying individually.
thanks
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

the formula for column H, starting in H2

=INDEX(B2:G2,MATCH(99^99,B2:G2,1))

copy down.

The "one more thing" would need a bit more explanation. Please don't ask more than one question in one question. Start a new question for the copying topic.

cheers, teylyn
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you are looking for numeric data, you could also use:
=LOOKUP(1E+40,B2:G2)
The 1E+40 is a very large number, like teylyn's 99^99. Given a number larger than anything in your list, LOOKUP returns the last numeric value it finds. LOOKUP ignores blanks, text and error values.

If you are looking for text, the comparable formula is:
=LOOKUP("zzzzz",B2:G2)
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
You might try the following macro to copy data from worksheets 2 through 21 onto the first worksheet. It assumes you run the macro when the destination (first) worksheet is active.
``````Sub TransferData()
Dim i As Long, j As Long, k As Long
Dim rg As Range
On Error Resume Next
For k = 2 To 21
i = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row        'Starting row to paste next worksheet's data
With Worksheets(k)
If .Name <> ActiveSheet.Name Then
Set rg = Nothing
Set rg = .Range("A2")   'Top left cell of data
Set rg = .Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))   'All the rows of data in that column
Set rg = rg.Resize(, .Range("A:Q").Columns.Count)               'All the rows and columns of data
ActiveSheet.Cells(i, 1).Resize(rg.Rows.Count, rg.Columns.Count).Value = rg.Value    'Copy the values in rg
End If
End With
Next
On Error GoTo 0
End Sub
``````