Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Reference Last Data Cells in Columns in another Workbook

    I have a monthly Excel report I'll call "Report" that I want to reference cells in another workbook "ExternalWB". ExternalWB will have data added to it each month and the current month row is the last data row of Sheet1. There are several cells in Report, Sheet1 tab, that I want to reference the very last data row for columns C, D, E, F in ExternalWB.

    I need help with a formula and/or technique to do this.
    Jerry

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, Jerry. I was going to answer this, but work has been almost non-stop. Looks like my earliest time to setup and test is next Monday.

    Hopefully someone else can provide you with a solution before then.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 8
    First we need to count the number of Rows

    =countif(C:C,">0") will give a number of cell that are not blank

    then we can do a =Index(C:C,countif(C:C,">0"),0)

    ill also used the Offset() but that a bit hard to explan
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Dec 2007
    Posts
    37

    Using offset

    Sub test()
    ActiveWorkbook.Sheets("Sheet1").Activate
    Range("A1", "A6335").Select


    Do Until IsEmpty(ActiveCell) = True

    ActiveCell.Offset(1, 0).Select

    Loop
    ActiveCell.Offset(-1, 0).Select
    MsgBox (ActiveCell.Value)


    End Sub
    Meyyappan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •