row formula

UMBERTO BOLDRIN

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

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?

UMBERTO BOLDRIN

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

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.

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?

UMBERTO BOLDRIN

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

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à?