Returning last entry in a database column

@jonhiatt

I have created a database that adds a new line against a date each day. From this I am building some metrics and then a dashboard which will show Daily Performance but also week to date, annual etc.

I am struggling to reference the database and return the last entry in a column. It seems that Lookup, Vlookup or Index should work but can't quite get there.

 

I have added a picture of the formula which seems to work in excel?

 

Any help out there appreciated.

 

Jon

 

 

Paul Newcome

If all rows are populated in any column, you can use that to establish the last row to pull from.

 

=INDEX([Column to pull from]:[Column to pull from], COUNTIFS(Date:Date, NOT(ISBLANK(@cell))))

 

Count how many rows are not blank and use that number to populate the row number portion of the INDEX statement.

Adam Kinney

I have a similar issue. Using the formula

=VLOOKUP([Account No.]@row, {Stage Report Roll-Up Reference Sheet Range 7}, 3, 0)

I only get the top row of the reference sheet column. The reference sheet only adds new data to the bottom row each time. I need the formula result to be the very last row of the referenced column that matches the lowest lookup value in [Account No.] column. Any help would be appreciated!

Image from the referenced sheet:

Genevieve P.

Hi @Adam Kinney

Instead of using. a VLOOKUP, what about using a MAX(COLLECT formula to find the MAX date in your Initial Presentation column, but the criteria in the COLLECT function would be if it matches the Account No. column in your current sheet.

Try this:

=MAX(COLLECT({Date Column}, {Account No. Column}, [Account No.]@row))

Let me know if this works for you!

Cheers,

Genevieve

Adam Kinney

Tried this and it returns a value of "0" if I have the reference column for the date as a date format, and "0" if I change it to test/general format. I have to be missing something....

Genevieve P.

Hi @Adam Kinney

The "Initial Presentation" column would need to be a Date type of column, and so would the column that the formula is placed in.

Is it possible that the Account No. is not finding a match? I notice that your numbers are appearing on the left of your cell, instead of the right, which indicates that they're being read as string or as text. Try adding a ' in front of your Account No. numbers in the sheet with the formula.

If this doesn't work, it would be helpful to see a screen capture of the sheet with the formula as well so we can compare data/columns/column types etc.

Thanks!

Genevieve

Adam Kinney

I had to apply the formula to the column first, then changed the format to date and got it to function. My next question is can I add a second criteria for the formula to reference so that it returns only the max date in the column based on account number and a contact or created by column?

Assuming it would look something like:

=MAX(COLLECT({Date Column}, {Account No. Column}, [Account No.]@row, {Created By Column}, [Created by]@row))

Doesn't look quite right to me. Just need it to pull the maximum date by account number, stage, and who entered it (ideally)

Appreciate the help thus far!

Genevieve P.

Glad you've got part of it working, @Adam Kinney !

Yes, you can do exactly that, adding in a new range and a new criteria. The COLLECT function is great for this!

I would presume your  [Created by] column in this current sheet with the formula is a static Contact list (versus a System Created column). You note it doesn't look right - do you mean that the result is incorrect?

Adam Kinney

I was planning on using the system column for the Created by reference if possible.

Genevieve P.

Do you mean in both sheets, or just the source sheet?

If the formula isn't working, it would be helpful to see screen captures of both sheets so I can replicate what you're seeing and do some testing with your same column types and set-ups, etc.

Adam Kinney

I got it to work by adding an additional column to populate the sheet user name via =IF(AND(Opportunity@row <> " ", Opportunity@row <> 0), "Person's Name", " ") any time they added a new row to trigger the function I was seeking.

So now formula looks like =MAX(COLLECT({ROLL UP TEST Range 1}, {ROLL UP TEST Range 2}, [Account No.]@row, {COPY COPY ROLL UP TEST Range 1}, KAE@row))

Genevieve P.

Hi @Adam Kinney

I'm glad you got it working! Thanks for sharing your solution with the Community. 🙂

Cheers,

Genevieve

Adam Kinney

Thank you for your assistance! @Genevieve P 😊