Convert Excel formula to work in smartsheet

CTops

Hi lovely community people!

I have imported an excel spreadsheet that has a column for most recent issue. it has the formula =LOOKUP(2, 1/(F4:XFD4<>""), F4:XFD4) which returns the latest input from the columns to keep a track of what has been issued to clients. Is there an easy way to convert this so it works in smartsheet?

CTops

Thank you - after a little alterations here and there I managed it - much appreciate your help.

L_123

If you add a created column you can reference that with an index match max.

If you are adding information with a form and just want the bottom row, you can just index count based off a column you know will always be populated (or add an autopopulated column to ensure it).

If you are adding information to the top with a form and want to dynamically reference the first cell use =index([entire column reference]:[entire column reference],1)

If you are modifying the information and want the most recently edited, you can use an index match max on a modified column

CTops

Thank you @L_123 I tried Index match but the issue is the value I need could be across a choice of multiple date as each time we invoice a new column is added and every invoice sent that week is added into that column. I need an easy way to see what the latest invoice is. So here is the first few columns where I want to see the most recent invoice (all called P01,P02 etc and C01,C02 etc) Top row needs to show C01 next row P03 and so on.

L_123

@Paul Newcome @Genevieve P.

Hey Paul or Genevieve,

Can you take a look at this one? I don't have the time today or most of tomorrow.

I think I would use an index collect count collect but not sure i'm following the data correctly

@CTops can you describe how the data is added to the sheet? How is it updated, how are new dates added etc because it's really not clear to me at the moment.

CTops

New dates are added manually by the user when new invoices are sent out (there are several invoices during the course of a project P01 being the first when in planning stage and C01 when in construction)

We are checking for the latest invoice sent each row (an individual address) should have C and not P invoice at the end of the project and during we need to ensure there are no duplications or missed. The number P or C are changed for each alteration throughout and we need to keep a track of where we are up to on hundreds of houses (rows).

All I want is an easy way to see at the left of the sheet a column to have the latest entry as it can be hundreds of weeks for each project across hundreds of addresses so looking close to the address detail is preferable to scrolling through all the data.

Thank you

Genevieve P.

Hi @CTops

Will the value always be 3 characters? P01, C01, etc?

If you're looking in the same sheet, you can return the cell with data to the right with a formula like this:

=RIGHT(JOIN([Date1]@row:DateEnd@row, ""), 3)

You would want the Range in the JOIN function to be from your first date column to your end date column. Then the RIGHT function grabs only the last value. I've highlighted the cell contents this formula is bringing back.

If you need to filter by only C values you can use a COLLECT function inside the JOIN to do that:

=RIGHT(JOIN(COLLECT([Date1]@row:DateEnd@row, [Date1]@row:DateEnd@row, LEFT(@cell, 1) = "C"), ""), 3)

Let us know if this makes sense and works for you!

Cheers,

Genevieve

CTops

Thank you - after a little alterations here and there I managed it - much appreciate your help.