row formula

UMBERTO BOLDRIN
UMBERTO BOLDRIN ✭✭✭
edited 01/29/24 in Formulas and Functions

Hi

i need to select a range of cells in a column starting from 5 to last row in the sheet.

The numbers of rows increase every day i need something dyinamic:

[Nome dell’attività]5:[Nome dell’attività]?

Is it possible?

Thanks Umberto

Answers

  • KPH
    KPH ✭✭✭✭✭✭

    Hi @UMBERTO BOLDRIN

    Can you tell us more about what you want to do with this range?

    Could you achieve what you want by adding an auto number column and use the MAX of that?

    Could you select the whole column and then remove rows 1, 2, 3, and 4?

  • Good mornig.

    I want to consider all the cells in the column avoiding the first 4.

    It works if I use this formula [Nome dell’attività]5:[Nome dell’attività]100 but when I add a new row (101) I have to manually change the the formula into [Nome dell’attività]5:[Nome dell’attività]101.

    I'm asking if is possibile in a way or another to avoid the manual activity.

    Umberto

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You will need to insert at least one and potentially two helper columns and then incorporate a COLLECT function into your existing formula or use an "IFS" version depending on your exact usage. We would need more details from you if you need further information.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • KPH
    KPH ✭✭✭✭✭✭

    Hi @UMBERTO BOLDRIN

    We understand that you are looking to reference a column starting at row 5 and ending on the last row (and this row number changes) There isn't a way to do that like: [Nome dell’attività]5:[Nome dell’attività]something to mean end

    But there is probably a way to achieve your ultimate aim. But we don't know what that is. Can you explain what you are going to do with these cells once you have them?

    For example, if you are doing a sum you could do a sum of the entire column using =SUM([Nome dell’attività]:[Nome dell’attività]) and then subtract the sum of rows 1-4.

    If you are still stuck, can you tell us any more?

  • Hi KPH,

    thank you for your answer, I'm working in a column named "€" in which I'm using thi formula.

    =IF(STELLA@row = 1; "Sì"; IF(STELLA@row = 2; "In attesa"; IF(STELLA@row = 3; "No"; "")))

    The column "STELLA" will be filled automatically by a workflow when the column "stato" change in determinated values (an example below)

    I must do this workaround cause is not possible to select a column symbols from a picklist in the workflow.

    My aim is to easily filter the situation about the commercial negotiations (threee different states)

    everything works but i have to copy manually the formula to every single cells

    Thanks

    Umberto

  • KPH
    KPH ✭✭✭✭✭✭

    Have I understood correctly...

    1. you have a column called stato that is manually populated in your sheet. (to help English speakers follow the process - this is a Status)
    2. when stato changes to a certain value an automation runs to populate your STELLA column with 1, 2, or 3 stars. For example when the offer is accepted it changes to 1 star.
    3. you have a formula in the € column that is populated based on the number of stars in STELLA. 1=Si, 2=In attesa, 3=no. (yes, pending, no)
    4. you have a column called [Nome dell’attività] (your task list).
    5. you want to do something with rows 5 onwards in your task list.

    Did I understand correctly?

    What is it that you want to do with the Nome dell’attività?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!