Saturday, July 13, 2013

yogi_Restrict Data In Columns I And L To Specified Entries And Pull Data Into Another Sheet Per Spec

                                          Google Spreadsheet   Post  #1280
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jul 13, 2013
user Alexandra Kahler (!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:


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,

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,