Google Spreadsheet Post #1280
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jul 13, 2013
user Alexandra Kahler (http://productforums.google.com/forum/?zx=oposogcnls8p#!mydiscussions/docs/HZCFdvmZ_3Y)
Setting up a spreadsheet system. Quesitons re: VMERGE, Color coding, Elapsed Day formula & next steps
Hello :)
I have merged the data from tabs 1-10 in my spreadsheet EINYA into a tab called MASTER using the formula =query(vmerge(TGG!A3:W,KLG!A3:
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jul 13, 2013
user Alexandra Kahler (http://productforums.google.com/forum/?zx=oposogcnls8p#!mydiscussions/docs/HZCFdvmZ_3Y)
Setting up a spreadsheet system. Quesitons re: VMERGE, Color coding, Elapsed Day formula & next steps
Hello :)
I have merged the data from tabs 1-10 in my spreadsheet EINYA into a tab called MASTER using the formula =query(vmerge(TGG!A3:W,KLG!A3:
W,KCH!A3:W,TWL!A3:W,SW!A3:W,
I am seeking help with the following questions.
1) Will the query above auto-update itself over time?
2) What formula do I use to bring selected records from EINYA tab MASTER into 10 tabs of a separate spreadsheet called Research? The criteria is that the contents of column E ("Area") in EINYA tab MASTER are a specific 4 letter string (i.e. PCOL, ECOL, ONCO, etc), which is equal to the name of the target tab in Research. (Data currently displayed in Research was not queried in, I cut/pasted it)
3)The data populated in EINYA tab MASTER does not retain the color coding that we use in tabs 1-10. Is there a way to retain the color coding by altering the function, or do I need to implement a script that will apply the color scheme based on criteria?
4) How do I get the elapsed days function =DAYS360(N3,W3,0) to return a blank result to column O when the date column N is left blank (it currently displays 40851)? Column W contains today's date.
5)Can I prevent the blank rows from tabs 1-10 in EINYA from being copied into the EINYA tab MASTER by altering the vmerge query?
6) How do I make records disappear from EINYA and appear in a separate spreadsheet called Log when the string "Yes" is typed into column V ("Off List?").
Thanks so much for your time and consideration!
Best wishes,
Alex
1) Will the query above auto-update itself over time?
2) What formula do I use to bring selected records from EINYA tab MASTER into 10 tabs of a separate spreadsheet called Research? The criteria is that the contents of column E ("Area") in EINYA tab MASTER are a specific 4 letter string (i.e. PCOL, ECOL, ONCO, etc), which is equal to the name of the target tab in Research. (Data currently displayed in Research was not queried in, I cut/pasted it)
3)The data populated in EINYA tab MASTER does not retain the color coding that we use in tabs 1-10. Is there a way to retain the color coding by altering the function, or do I need to implement a script that will apply the color scheme based on criteria?
4) How do I get the elapsed days function =DAYS360(N3,W3,0) to return a blank result to column O when the date column N is left blank (it currently displays 40851)? Column W contains today's date.
5)Can I prevent the blank rows from tabs 1-10 in EINYA from being copied into the EINYA tab MASTER by altering the vmerge query?
6) How do I make records disappear from EINYA and appear in a separate spreadsheet called Log when the string "Yes" is typed into column V ("Off List?").
Thanks so much for your time and consideration!
Best wishes,
Alex
---
Hi Again Yogi,
I wanted to follow up on my prior post and see if I could provide any more information. I no longer think that question 1 from my previous post is important, but am still seeking solutions for the following three issues. Did I provide the information you requested, or am I still not presenting my questions in the right format?
2. Constraining cell contents
In EINYA TGG tab Col I ("Last Letters"), can the user be limited to the following text options: 1st Q, 2nd Q, AC, R1, R2, R3, R4, R5, R6, R7?
In EINYA TGG tab Col L ("Action Needed"), can the user be limited to the following text options: Research, Letters, AC, 2nd Q?
Expected Result: Text entries which differ from the list choices EINYA TGG tab Col I will not be saved written into the cell. If possible, the cell would highlight when the user attempted to enter non-approved text.
3. Highlighting Duplicates
There are duplicate entries in EINYA TGG tab Row 54, Column A (MS #) and Row 59, Column A. Users copy a manuscript number into Column A from the source (client) database. If a user attempts to paste value into Column A which already exists elsewhere in the sheet, can it be highlighted so the user immediately knows?
Expected result: EINYA TGG tab Row 54, Column A and Row 59, Column A will appear highlighted once identical data is pasted into Row 59, Column A.
4. Removing Rows from Sheet
In EINYA TGG tab Row 3, Col U ("Remove from List"), the text "yes" is typed in. Upon entering the word yes (pr some other marker), can the full contents of Row 3 be deleted from EINYA TGG tab, and simultaneously appear in EINYA Log tab?
Expected Result: Upon pressing enter after typing "yes" in column EINYA TGG tab Row 3, Col U, the contents of Row 3 with MS # PONE-D-13-16500 ill disappear from the tab, and will appear in the EINYALog tab (in bottom available row).
Thanks again so much for your time and consideration!
Warm regards,
Alexandra
I wanted to follow up on my prior post and see if I could provide any more information. I no longer think that question 1 from my previous post is important, but am still seeking solutions for the following three issues. Did I provide the information you requested, or am I still not presenting my questions in the right format?
2. Constraining cell contents
In EINYA TGG tab Col I ("Last Letters"), can the user be limited to the following text options: 1st Q, 2nd Q, AC, R1, R2, R3, R4, R5, R6, R7?
In EINYA TGG tab Col L ("Action Needed"), can the user be limited to the following text options: Research, Letters, AC, 2nd Q?
Expected Result: Text entries which differ from the list choices EINYA TGG tab Col I will not be saved written into the cell. If possible, the cell would highlight when the user attempted to enter non-approved text.
3. Highlighting Duplicates
There are duplicate entries in EINYA TGG tab Row 54, Column A (MS #) and Row 59, Column A. Users copy a manuscript number into Column A from the source (client) database. If a user attempts to paste value into Column A which already exists elsewhere in the sheet, can it be highlighted so the user immediately knows?
Expected result: EINYA TGG tab Row 54, Column A and Row 59, Column A will appear highlighted once identical data is pasted into Row 59, Column A.
4. Removing Rows from Sheet
In EINYA TGG tab Row 3, Col U ("Remove from List"), the text "yes" is typed in. Upon entering the word yes (pr some other marker), can the full contents of Row 3 be deleted from EINYA TGG tab, and simultaneously appear in EINYA Log tab?
Expected Result: Upon pressing enter after typing "yes" in column EINYA TGG tab Row 3, Col U, the contents of Row 3 with MS # PONE-D-13-16500 ill disappear from the tab, and will appear in the EINYALog tab (in bottom available row).
Warm regards,
Alexandra
-----------------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment