Question description:
This user has given permission to use the problem statement for this
blog.
Hi,
I have a google sheet shared by the team, I set the conditional formatting, the row will turn into certain colors as cell values are updated, the next thing I want to accomplish is to move all the rows that has changed to certain colors to another spreadsheet, automatically...Is it something achievable?
Solved by X. J. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
14/03/2017 - 04:20
Wow...so fast
Excelchat Expert
14/03/2017 - 04:21
Hello, Welcome to Excel chat.
User
14/03/2017 - 04:21
So far so good, I could share my document
Excelchat Expert
14/03/2017 - 04:21
yes please do so
User
14/03/2017 - 04:21
ok one second
Excelchat Expert
14/03/2017 - 04:22
ok
User
14/03/2017 - 04:23
https://docs.google.com/spreadsheets/d/1nycocN5wb8gMf4RdtzB3S-Cbu_5MC0cYY7XCWivR9qw/edit#gid=198999832
User
14/03/2017 - 04:24
So idea if you go on order form, I'm hoping all the orange rows could transfer automatically to "internal order history' as a record
Excelchat Expert
14/03/2017 - 04:24
can you share the link that is editable by anyone who has the link
User
14/03/2017 - 04:24
Ok, done
User
14/03/2017 - 04:25
If you are in "order form" and edit "K", that is a part of current conditioning formatt
Excelchat Expert
14/03/2017 - 04:26
ok, so if k contains Will call(Transferred) then it will turn to orange
Excelchat Expert
14/03/2017 - 04:26
correct?
User
14/03/2017 - 04:26
Yes,
User
14/03/2017 - 04:26
reference in current conditional formatting
Excelchat Expert
14/03/2017 - 04:26
ok, so if you want to move all the rows that contains Will call(Transferred) in col K to new sheet
User
14/03/2017 - 04:27
So the goal is to move orange cells to a new sheet
Excelchat Expert
14/03/2017 - 04:27
then this is possible via script only
User
14/03/2017 - 04:27
Doesnt have to be will call(transferred), as long as the cell turns orange
Excelchat Expert
14/03/2017 - 04:27
wait a minute
Excelchat Expert
14/03/2017 - 04:28
it can not recognize the color, you have to tell the condition on which it is turning orange
User
14/03/2017 - 04:28
Cool...The row will turn orange meet either "Will call(transferred)" or "Received"
Excelchat Expert
14/03/2017 - 04:28
ok
Excelchat Expert
14/03/2017 - 04:28
do you want me to add a new sheet
User
14/03/2017 - 04:28
Sure thing
User
14/03/2017 - 04:29
You guys are awesome
Excelchat Expert
14/03/2017 - 04:34
thanks :)
Excelchat Expert
14/03/2017 - 04:34
working on your problem now
User
14/03/2017 - 04:35
Thank you so much
Excelchat Expert
14/03/2017 - 04:39
done
Excelchat Expert
14/03/2017 - 04:40
look at sheet 29
User
14/03/2017 - 04:40
ok
Excelchat Expert
14/03/2017 - 04:41
so whenever a new row is enter in order form with col K containing either Received or Will call(transferred)
User
14/03/2017 - 04:41
awesome,
Excelchat Expert
14/03/2017 - 04:41
it will transfer the row into sheet 29
Excelchat Expert
14/03/2017 - 04:41
:)
User
14/03/2017 - 04:42
Is possible to delete the current rows at the same time keep the row color orange?
Excelchat Expert
14/03/2017 - 04:42
delete the data you mean? and keep the row empty with orange color
Excelchat Expert
14/03/2017 - 04:43
in order form sheet?
User
14/03/2017 - 04:43
Like now, once I delete the rows in Order form, the rows in sheet 29 are gone simultaneity
Excelchat Expert
14/03/2017 - 04:44
yes, you cannot delete them from original sheet
User
14/03/2017 - 04:44
I see
Excelchat Expert
14/03/2017 - 04:45
as I have used the filter formula in sheet29 and it is filtering and showing only rows that have those 2 values in col k of order form
User
14/03/2017 - 04:45
Is there anyway rows will automatically transfer from "order form" to sheet 29, like rows are gone in order form, but appear in sheet 29....haha it sounds like dark magic.,..,but is it possible?
Excelchat Expert
14/03/2017 - 04:45
but as you have applied the conditional formatting so, it indicates that this row has been moved to sheet 29
Excelchat Expert
14/03/2017 - 04:46
yes, it is possible but via script and unlike formula it will not automatically copy the data you need some kind of trigger to run the script
Excelchat Expert
14/03/2017 - 04:46
like pressing a button
Excelchat Expert
14/03/2017 - 04:47
also, current session is about to end, please repost if your query is not resolved
User
14/03/2017 - 04:47
I see....Thank you so much for the help...it's awesome!
Excelchat Expert
14/03/2017 - 04:47
It's my pleasure
Excelchat Expert
14/03/2017 - 04:47
Do you have any other questions today?
User
14/03/2017 - 04:48
No everything is perfect
User
14/03/2017 - 04:48
Ok, let me end the session!
