+ Reply to Thread
Results 1 to 39 of 39

Dynamically get last row of another sheet?

  1. #1
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Dynamically get last row of another sheet?

    Hi Folks -

    I have a workbook that I"m trying to determine the last row of another sheet (Results!). Right now, I have the formula using 30000 to buy me some time but I'd really love if I could make the formula dynamic to be able to tell me the last row.

    Here is my formula:
    Please Login or Register  to view this content.
    I tried to use CountA but just returns the column on the current sheet. Is this possible?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,509

    Re: Dynamically get last row of another sheet?

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    @Aligw -

    thank you!

    Attached is my workbook, please see Tab P12 that have my notes and explanation.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,509

    Re: Dynamically get last row of another sheet?

    And your Office version is ... ?

  5. #5
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    @AliGW, 2013. Thank you!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,509

    Re: Dynamically get last row of another sheet?

    Thanks - please update your forum profile as requested. It says 16 at the moment.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    For the formulae in C to G... you can safely use WHOLE COLUMN references, instead of exact ranges, with no adverse effects on performance.

    However, the formula in B6 looks hugely complicated. Can you explain what you WANT IT to do.... don't explain the mechanics of the formula, I can see that myself... but it looks like you want a formula that returns one instance of each name from column H of results, with the (possible) added complication that it should also have some values in column I. That's what I mean by explain what you want it to do.


    I'm sure it can be hugely simplified!!
    Glenn



  8. #8
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    HI Glenn!

    So, basically.

    The formula in B3 uses the value in C6 of the Cover Page as a reference to the Results! tab. Using C6 from the Cover Page, it populates that cell with the Name of the person that it pulls from the Results! tab with that same value in Column M for the applicable Week Ending Date (Column F).

    THe Week Ending Date in Results are also populated in C5:G5 of the applicable Period Tab (i.e. P1)

    All of the following 3 sections do the same thing, the only difference is they use a different value on the Cover Page.

    Let me know if that makes sense, thank you!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,509

    Re: Dynamically get last row of another sheet?

    Please will you update your profile with your current Office version? It needs to show Excel 2013.

  10. #10
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    Hi Ali, it's now actually 0365. I got a new machine on Friday.

    I'll update now.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,509

    Re: Dynamically get last row of another sheet?

    Oh, fab. Thanks!

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    I think I 'll have to bow out. The VBA won't work on a 64 bit system... I know nothing about VBA and while the formula in B6 is easily simplified, it knocks out the values in the headers of the columns to the right.

    So I can't fiddle with Named Ranges to auto adjust formula ranges to your data... or do anything... without the whole thing falling over.

  13. #13
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    Glenn -

    Not sure what you mean about VBA? FOr now, we don't need to run VBA> WOuld you mind sharing the simplied formula so I can play around with it?

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    Let me go back again. I had about 15 files open at once!! Who knows what happened!!

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    As soon as I turn calculations from manual to automatic, everything on P12 errors out...

  16. #16
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    Let me attach a new version. On Friday I got a new machine and upgrade to 0365. Give me a few minutes and I'll attached the 0365 version. WOuld that help?

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    Who can tell?? This is the Excel equivalent of the Dark Side of the Farce...

  18. #18
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    HI Glenn -

    Attached is the new template. I also updated the formulas in C:G with whole column references per your suggestion. Let me know is this is better.

    Thanks!
    Attached Files Attached Files

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    This is what I see when I set calcs to automatic!
    Attached Images Attached Images

  20. #20
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    That's odd. My works fine when toggling between Automatic and Manual.

    Can you share the formula you tried? I'll poke around with it and see where it may be breaking.

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    OK. So, back to basics. This looks like your original, but stripped of all the bat's wings, cat's entrails, etc, etc, that go to make up VBA.

    I set up 2 named ranges (CTRL-F3 to view edit) called Employee and Project... that look like this:

    =Results!$H$2:INDEX(Results!$H:$H,MATCH("Zzzz",Results!$H:$H))

    They will auto-adjust to the correct range to suit your data.

    Then I used this formula to return 1 instance of each name in Results that matches the date criterion and the Project name criterion:

    =IF(AND($A$1<>"",TODAY()>$A$2),IFERROR(INDEX(Results!H:H,AGGREGATE(15,6,ROW(Employee)/((Project=Sheet2!$A$1)*(COUNTIF(Sheet2!B$5:B5,Employee)=0)),1)),""),"")
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    Hello Glenn -

    Thank you very much! I took a peek and that certainly works, but there seems to be a flaw.

    If I take your workbook and add another row with a new person (for Majestic Wines for instance) and then put a Week Ending Date that is not on Sheet2, it still includes that person in the list which shouldn't be the case. Is there a way to correct? I"ve attached your workbook with the example I'm talking about. thank you!
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    Hi Glenn -

    Additionally, is there a way to use a formula that doesn't use INDEX? The only reason I ask is what happens when someone opens the file on a lower excel version? I would anticipate they wouldn't be able to refresh infact it would error out.

    What is best practice around that?

  24. #24
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    Will be back tomorrow. In Spain (currently) and it's dinner and wine o'clock.

    INDEX has been around for ever. No issues there.

    Define, in words, date criteria for inclusion in sheet 2.

    G'nite.

  25. #25
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    HI Glenn -

    Enjoy!

    So, another "criteria" needs to be added to your logic when deriving the Employee Name. For instance, we also need to factor in the Date in C5:G5 on Sheet2 when checking the Results tab.

    The values in C5:G5 are also values found in the Week Ending Data column of the Results tab. The Employee returned must have a "Week Ending Data" values that matches 1 or more values in C5:G5 of Sheet 2.

    Let me know if you need additional detail.

  26. #26
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    Another Named Range (Date):

    =$F$2:INDEX($F:$F,MATCH(10^100,$F:$F))

    =IF(AND($A$1<>"",TODAY()>$A$2),IFERROR(INDEX(Results!H:H,AGGREGATE(15,6,ROW(Employee)/((Project=Sheet2!$A$1)*(ISNUMBER(MATCH(Date,$C$5:$G$5,0)))*(COUNTIF(Sheet2!B$5:B5,Employee)=0)),1)),""),"")
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    HI Glenn -

    That worked like a charm!! Thank you!

    Back to my comment above about INDEX. I just ran a test and when I open the workbook on a non-0365 version of excel, I see the following associated to the INDEX function:
    Please Login or Register  to view this content.
    And when I refresh, I get the !NAME error. I suppose there is no work around to that other than ensuring everyone in the organization is updated to 0365?

  28. #28
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    What version of Excel is on that machine? It must be Excel 2007 or earlier??? INDEX was introduced a LONG LONG time ago. AGGREGATE came along in 2010.

  29. #29
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    =_xlfn.SINGLE appears at the start of the formula if ANY function called is not available... so I can assure you, it's not iNDEX. Are any users still on Excel 2007 or earlier?

  30. #30
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    I'm getting the error with excel 2016.

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,509

    Re: Dynamically get last row of another sheet?

    There's nothing in either formula that is not available in Excel 2016, as far as I can see ...

  32. #32
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    ??? I'd say that that's impossible!! But clearly it is possible.

    1. Post a screenshot.

    2. Does this work in Excel 2016 (it will not give the correct result..., but that is not the point)

    =IF(AND($A$1<>"",TODAY()>$A$2),IFERROR(INDEX(Results!H:H,AGGREGATE(15,6,ROW(Employee)/((Project=Sheet2!$A$1)*(COUNTIF(Sheet2!B$5:B5,Employee)=0)),1)),""),"")

  33. #33
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    Are all 3 named ranges present on the file you're opening in Excel 2016???

  34. #34
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    HI Glenn and ALi -

    Just to be clear, the issue is not with Glenn's formulas. They are working fine.

    If you open up my attachment, the issue is coming from the following cells:
    J8,J9,I10,I11

    If you look at other formulas using the INDEX throughout the workbook, I see it enclosed in {} which is working fine. But for some reason, the formulas in J8,J9, I10 and I11 have the _xlfn.SINGLE single in them. Why would this be?

    *Note, my attachment does not have Glenn's updates, using this one for example purposes.
    Attached Files Attached Files

  35. #35
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,509

    Re: Dynamically get last row of another sheet?

    I think I know what it is:

    =@INDEX(Contract!$C:$C,AGGREGATE(15,6,(ROW($A3:$A1000)-ROW($A$2))/(Contract!$A:$A=YEAR($C$2))/(Contract!$B:$B=TEXT($C$2,"mmmm")),1))

    This sometimes appears when workning between newer and older versions. Delete it.

  36. #36
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    @Ali - that worked!!! Thank you so much! DO you know what causes the A to show up? I'm nervous if I open an excel file via 0365 it will add it automatically until I remove it again? I did some testing opening and closing and I don't see @ show up anymore so I'm just trying to understand how it happens so I can prevent in the future.

  37. #37
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    Google implicit intersection operator

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

  38. #38
    Registered User
    Join Date
    12-01-2018
    Location
    NJ
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Dynamically get last row of another sheet?

    Thank you again to you both for your help, much appreciated it!

    Glenn, taking a looking but don't see how to prevent it from showing up infortunately. I know why it's happening but unsure how to prevent @ from showing up again when opening the files with older excel versions. I'll keep digging. Thanks!

  39. #39
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Dynamically get last row of another sheet?

    I'm not sure that it is preventable.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 04-16-2020, 07:43 PM
  2. [SOLVED] Dynamically color cell on Summary sheet based on column values on data sheet
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-08-2016, 12:59 PM
  3. Replies: 1
    Last Post: 08-20-2015, 01:12 AM
  4. Replies: 0
    Last Post: 06-22-2014, 04:18 PM
  5. [SOLVED] Link cell from a dynamically generated sheet to a master sheet
    By stockfeed in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-04-2012, 07:26 PM
  6. Replies: 4
    Last Post: 03-09-2011, 05:25 PM
  7. Can Sheets be Added/Renamed via VBA dynamically thru the Main Sheet- Sheet 1?
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-27-2009, 08:47 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1