MAX-COLLECT for text
HI
I am using this formula "=MAX(COLLECT({WTG1}, {T#}, @cell = [Turbine No.]@row)) "
but its work just if cell contain a number , i need to return the text what cell has , how ?
Best Answer
-
You could insert an auto-number column with no special formatting into the sheet you are pulling from then use something like this on the final sheet:
=INDEX({Column To Pull From}, MATCH(MAX(COLLECT({Auto Number Column}, {Turbine ID}, @cell = [Turbine No.]@row)), {Auto Number Column}, 0))
Answers
-
Hi !
MAX only return the maximum number or the latest date.
What is the information you are trying to max ?
What is the output expected from that formula?
-
thank u
i need to return the last text updated in cell ,
the data in cell will change many time , i need to take the last text was filled .
-
Try an INDEX/COLLECT instead if you are wanting to capture whatever data is currently in the cell.
-
Hi Paul ,
The idea is :
i make sheet 1 and i will change cell data on future , i need to make another copy of sheet 1 name sheet 2 that will update data in cell depend on data change in sheet 1 , same data i need ,
but if i delete row from sheet 1 i didn't need to delete from sheet 2 or give me #ref. error ,
what i do :
1- make 3 sheet ( * sheet 1 *sheet 2 *sheet 3 )
2-when change rows in sheet 1 ---> send copy of row to sheet 2 ((may will have more than copy of row bcz many change ))
3- sheet 3 same rows & columns in sheet 1 , it will take the last updated cell from sheet 2 and save it
i use "=MAX(COLLECT({WTG1}, {T#}, @cell = [Turbine No.]@row)) " -----> its work just for number and its perfect , but i need to use for text ( same text in cell )
is it clear for u ?
-
To get the last updated row from Sheet2 (Coped rows) on Sheet 3 (Copy of Items Tracker), You need an Unique Identifier(UI) in sheet1 (Items tracker).
In sheet3, use a INDEX (MATCH) on a MAX Modify date, using the UI as a key to collect the right lines
(that last sentence reads like giberish)
=index(collect({Turbine No.},UI:UI,UI@row),match(max(collect({Modify date},UI:UI,UI@row)),collect({Modify date},UI:UI,UI@row)))
This "collect({Modify date},UI:UI,UI@row)" reduce the lookup table to the line with the same Unique Identifier.
The match(max()) return the position of the latest modified line with that UI
The index return the item a the position given by the match from the list culled down by the collect.
You will need one for every column.
I guess that is one way to build it.
-
You could insert an auto-number column with no special formatting into the sheet you are pulling from then use something like this on the final sheet:
=INDEX({Column To Pull From}, MATCH(MAX(COLLECT({Auto Number Column}, {Turbine ID}, @cell = [Turbine No.]@row)), {Auto Number Column}, 0))
-
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives