Lookup text or number in last cell

craigPowerlog

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

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.