We help IT Professionals succeed at work.

MS office Excel

snhandle
snhandle used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
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)
byundtMechanical 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

Open in new window


Brad