How to get the last value in a data base

AnaV

Hi dear community,

I have a database where I can see the records of time spent in an area.

Each employee needs to request an authorization and not to exceed a maximum time of stay in a period of time in that area (for example each month 10 hr are added although this varies depending on the type of employee).

I have a sheet called PROCESS that does the automation of requests and authorizations; once authorized they go to another database ACCEPTED.

When there is a request in my PROCESS sheet I need to “import” the time that the employee has left, since it is a condition for authorization.

This is: Initial Hours/Horas initiales - Hours requested/Horas solicitadas = Hours remaining/Horas remanentes.

If it were a single person, this would look something like this:

The problem is when there are thousands of data; since in the next request I should take the “Remaining Hours” from the last record in my database ACCEPTED, I don't know how to pull this last record depending on the number of employee.

Please, help me… what formula should I use to take in the example information?

I tried with INDEX but when there is only one “# Nomina” record it works, but for other cases I get #NO MATCH.

This is the formula I used:

=INDEX({ACCEPTED Rank 1}, MATCH([# Nomina]@row, {ACCEPTED Rank Remaining Hours})).

Thanks for your help

Ana

TVang

Hi, @AnaV.

Create 3 cross-sheet references in your PROCESS sheet to the ACCEPTED sheet and then use the formula below.

On the PROCESS sheet…
{ACCEPTED # Nomina}
{ACCEPTED FINICIO}
{ACCEPTED HorasRemanentes}

Horas iniciales = MAX(COLLECT({ACCEPTED HorasRemanentes}, {ACCEPTED # Nomina}, [# Nomina]@row, {ACCEPTED FINICIO}, MAX(COLLECT({ACCEPTED FINICIO}, {ACCEPTED FINICIO}, < [FINICIO]@row,{ACCEPTED # Nomina}, [# Nomina]@row))))

On the ACCEPTED sheet…
The column formula in the Horas remamentes is not required. (I'm assuming that you're copying the row over from the PROCESS sheet.)

Cheers!

AnaV

Dear @TVang,

Thank you very much for your assistance. The suggestions of references help me a lot, but It was not solved at all. Due it takes the first one… again! I was thinking abou the date was the problem… so I found a solution adding another column with a consecutive and taking the MAX of this as a third reference!!

So your idea of the MAX formula helps a lot!! Thank you

😀