Lookup text or number in last cell

Hello,

I know how to do it on Excel, but cant seem to get it to work on Smartsheet.

I have sheet that gets updated by a form filled out by other people. I would like a formula that would get the last piece of information added to particular cells in this sheet (add to bottom row) and display this in an overview cell.

Lookup was my idea in Excel, but I cant find anything that would do this in Smartsheet.

as a very crude and basic example:

Overview information: 665 hours

row 1: 223 hours

row 2: 436 hours

row 3: 566 hours

row 4: 665 hours

Thank you in advance

Find more posts tagged with

Formulas

Product Development

Project Management

Media & Entertainment

Comments

Andrée Starå

Hi Craig,

Try something like this.

=INDEX(Hours:Hours; COUNT(Hours:Hours))

The same version but with the below changes for your and others convenience.

=INDEX(Hours:Hours, COUNT(Hours:Hours))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

craigPowerlog

Hello Andree,

Thank you for your help but that didn't work unfortunately, but I may not have explained it well enough.

What I would like to do is show the text contents of the last cell in a particular column.

So when staff update the location of a piece of equipment, I would like to know the last location this piece of equipment was used.

e.g. Last Location Used: Birmingham

Row ID Location

1 Bristol

2 London

3 Bristol

4 Kent

5 Birmingham

I've tried quite a few different formulas but cant get it to work?

Andrée Starå

Happy to help!

It should work! I just tested it again!

I'd be happy to look and see what the error might be.

Can you maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Best,

Andrée

craigPowerlog

Well I have just tried it again and it worked! haha. Thank you very much!!

I have no idea what I did wrong the first time around (I would say lack of sleep would possibly be the case!)

One final question which finishes this particular function. How would I modify this to ignore any blank cells?

I've been looking into ISBLANK....but I just keep getting circular reference.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…