asked on

MS office Excel

I want to build a formula where I can get the last cell data in a row.
For Example
Columns heading
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

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

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

Open in new window

Brad