Categories: Sheets : Ask a "how-to" question :

Update corresponding column of master spreadsheet based on other spreadsheet cell data

Showing 1-45 of 45 messages
Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/3/12 12:01 PM
Hello All,

I'm kinda new to Google Docs and am trying to create a Google form app where a master spreadsheet's corresponding column/cell will be updated based on what is entered on a form, which other spreadsheets are linked to. I'm sharing what I have so far: https://docs.google.com/spreadsheet/ccc?key=0Ar5XCh4AvyBLdGNUUExQaTVxaHJ5QlpUcWlKSHdDZmc
 
Right now, it is only updating the R & D tab, but not the Master sheet. Also, when selecting a status (At Work On, At Work Off, Leave, etc.) for an IT employee, instead of updating the IT spreadsheet, it is incorrectly updating the R & D sheet. 

I would appreciate your guidance. Thanks! 
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/4/12 1:43 AM
Hi

To help us better understand what you're trying to do, I would like to suggest that you...
  • populate your sheet with some realistic sample data
  • the expected results in the master sheet
  • and tell us the logic behind
daniness 5/4/12 8:31 AM <This message has been deleted.>
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/4/12 8:47 AM
Hi MarinusP and everyone,


Regarding the master sheet, I need it to look at the appropriate department's tab and populate its corresponding column/cell based on what each employee's status is recorded as on the R&D or IT sheets. As of right now, I had to manually enter the formula, ='R & D'!B3, into the corresponding column/cell for R&D employee 101. I'd like the spreadsheet to autopopulate the correct status column based on what is entered in each department's sheet from the form. Please let me know if I need to further explain.

Thanks! 
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/4/12 9:29 AM
Hi

I added a copy-sheet to your GSheet, named: MP. A formula in cell with yellow background. I hope this helps.

Please let me know if you have any questions or suggestions.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/7/12 5:27 AM
Hello MarinusP and Everyone,

Thanks for your suggestion. The formula you provided seems to work, but could you please explain it as I've never used transpose, filter, etc.? Also, do you know why the IT employees' statuses are displaying on the R&D sheet instead of the IT sheet?

I appreciate your assistance.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/7/12 9:53 AM
Hi

I have tried to explain how the formula works in your spreadsheet, using examples to show the separate steps. Please let me know if you have questions about it.

Also, do you know why the IT employees' statuses are displaying on the R&D sheet instead of the IT sheet?

Yes, the R&D sheet is the sheet where the submissions of the form come. A spreadsheet only can contain one form, and there is always only one sheet where the submissions come. You can however, separate them on other sheet, using a function like FILTER, or QUERY.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/8/12 9:06 AM
Thank you, MarinusP! That was a great explanation and it worked for me. 

Now I just have to look into the FILTER and/or the QUERY functions for the the subsequent worksheet tabs to pull the data from the master sheet. I'll let you know how it goes.

Thanks again!
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/9/12 9:57 AM
Hi Marinus and Everyone,

Could you please advise on how I can use either FILTER or QUERY to get the Employees starting from # 200 onwards from the R&D sheet to populate the IT sheet with their statuses? I've tried this formula: FILTER('R & D'!B:Z; 'R & D'!A:A=MAX('R & D'!A:A))=C1:H1;C1:H1, but it's giving me a parse error. I'd appreciate your guidance very much. Thanks!
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/9/12 10:06 AM
It seems that the following did the trick:  =FILTER('R & D'!L:Z; 'R & D'!A:A=MAX('R & D'!A:A))

Thanks again!
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/9/12 10:23 AM
Hi

Yes, that should work well, if you only need the last row where is a timestamp. If you need all rows where is a timestamp, then you can easily use:

=FILTER('R & D'!L:Z; 'R & D'!A:A<>"")
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/22/12 7:33 AM
Hi MarinusP and All,

I am still having difficulty in getting the various tabs of the spreadsheet to populate, using the formula that was suggested here and I keep getting a Parse error. I'm sharing the actual rather than the model spreadsheet here for anyone who could please provide assistance: https://docs.google.com/spreadsheet/ccc?key=0Ar5XCh4AvyBLdDE2dDRXTWg5VTZuemRHWmYyT2h5R0E . In particular, if you go to the "QSDHA1 (Adm) tab and see the formula I'm using in Cell G2: =ARRAYFORMULA((TRANSPOSE(FILTER('Form Master'!B:T; 'Form Master'!A:A=MAX('Form Master'!A:A)))=G1:K1;G1:K1;)), I'm not sure why it won't populate from the Form Master tab. I would appreciate your assistance with this! Thanks.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/22/12 10:25 AM
Hi

I guess you forgot the function IF. Instead of:

=ARRAYFORMULA((TRANSPOSE(FILTER('Form Master'!B:T; 'Form Master'!A:A=MAX('Form Master'!A:A)))=G1:K1;G1:K1;))

please try:

=ARRAYFORMULA(IF(TRANSPOSE(FILTER('Form Master'!B:T, 'Form Master'!A:A=MAX('Form Master'!A:A)))=G1:K1,G1:K1,))

Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/22/12 11:42 AM
Thank you...you're a genius! That worked. Do you have any suggestions on how to populate the COOP Master Roster tab, based on each person's Office? On this tab, it lists all the employees, so I need to find a way to pull their status from the appropriate tabs of the spreadsheet.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/22/12 3:38 PM
Hi

Op dinsdag 22 mei 2012 20:42:15 UTC+2 schreef daniness het volgende:
Thank you...you're a genius!

You're welcome, and many thanks for calling me a genius, but I guess you haven't seen yet, what some other people on this forum, e.g. Adam and Yogi, can do with spreadsheet formulas. ;)
 
That worked. Do you have any suggestions on how to populate the COOP Master Roster tab, based on each person's Office? On this tab, it lists all the employees, so I need to find a way to pull their status from the appropriate tabs of the spreadsheet.

I think that will be not easy.At first I think it would be good, if you make all the separate sheets the same; I mean, what's in each column, should be the same for each sheet. Please have a look at the copy-sheet I added to your spreadsheet: "Sheets_MP", especially from column H and further.

If you have all sheets the same, then you can install the script: VMERGE, to merge all data from different sheets, into one collection-sheet.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/23/12 8:41 AM
Hi MP and All,

I'm not sure I understand what you mean when you say "...what's in each column, should be the same for each sheet.". 

However, I did have a thought and wanted to get your feedback. Do you think there's a way to programatically through a script or something have the spreadsheet populate the appropriate employee status by matching the full name from the COOP Master Roster tab with the full names in the column headings on the Form master tab? Please let me know your thoughts. Thanks!
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/23/12 1:28 PM
Hi



Op woensdag 23 mei 2012 17:41:04 UTC+2 schreef daniness het volgende:
Hi MP and All,

I'm not sure I understand what you mean when you say "...what's in each column, should be the same for each sheet.". 

I have taken a look at the sheet "COOP Master Roster", and it seems to me that you have in that sheet a collection of the data in all other sheets, right?

Now I have done the same in the new tab "Sheets_MP". However, since in ....
  • most sheets, column G,H, and I are:  At Work At Home On Leave
  • two other sheets, G,H,I,J,and K:        At Work On At Work Off Leave Off Duty Other
  • One sheet, G,H,I,J,K,and L:              At Work On         At Work Off At Home Leave Off Duty Other
the data will not be in the right column for some sheets. Let's say you have two tables with names and addresses. In table 1 the first and last name are in two separate columns. But in table 2, they are merged in only one column. So if you are going to merge those 2 tables, you won't have the correct results, because some last names are in the first column, and other ones, in the second column. You see what I mean?

I've added a script: VMerge, created by AHBanen to your spreadsheet, and some formulas to sheet "Sheets_MP". Please have a look at it, take your time, and think about it. Please let me know if you have questions about it.

 
However, I did have a thought and wanted to get your feedback. Do you think there's a way to programatically through a script or something have the spreadsheet populate the appropriate employee status by matching the full name from the COOP Master Roster tab with the full names in the column headings on the Form master tab? Please let me know your thoughts. Thanks!

OK, let's first talk about, what exactly you wish to do with sheet "COOP Master Roster". Talk later. :)
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/24/12 9:53 AM
Hi MP and All,

Thanks for your feedback. So I now understand what you meant by how all the sheets should have the columns designated by the same letters, G through K and I've I corrected this for every worksheet. However, I'm not sure about the formulas you've added, i.e.: =ARRAYFORMULA(TRANSPOSE(IFERROR(REGEXEXTRACT('Form Master'!B1:1;"\[(.*)]")))) and the VMerge formulas, etc.

Is there a simpler way? To answer your question, I'd like to get the COOP Master Roster sheet to display the corresponding status for each employee by pulling it from the various worksheet, i.e. I'd like Amato, Nicholas M on the COOP Master Roster to pull the status from QSDHA1 (Adm) sheet.

I also came across another thread where someone is trying to do something similar: https://productforums.google.com/forum/#!msg/docs/km_Y-uph_m4/BPvRzS2lar0J . In my case, each department's worksheet would be comparable to this person's "Sheet 1" which they are using as a reference to get the draft value based on the player's name whereas I'd like to get the employee status, and my COOP Master Roster sheet would be comparable to this person's "Sheet 2" because this is where I'd like the status to pull to. Please let me know if I can further clarify. I appreciate all your help!
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/24/12 5:43 PM
Hi

I made a start: (see your spreadsheet)

Explanation of:  =ARRAYFORMULA( TRANSPOSE( IFERROR( REGEXEXTRACT( 'Form Master'!B1:1;"\[(.*)]"))))
First the part:  REGEXEXTRACT( 'Form Master'!B1:1;"\[(.*)]")
Or actually the part:  REGEXEXTRACT( 'Form Master'!B1;"\[(.*)]") since the function REGEXEXTRACT does not accept multiple cell-ranges, unless it's inside an arrayformula...
Value in cell B1 of sheet: Form Master is:
Admin [Amato, Nicholas M]
Now have a look what the formula does:
Amato, Nicholas M
As you can see it results in only the part which is between the square brackets []
If you would like to have more specific information about the function REGEXEXTRACT, please let me know
Now we can put this formula in an arrayformula:
Amato, Nicholas M
This will allow us to use that function for a multiple cell-range, in this case B1:C1 (B1 to C1). You also can use B1:1, which is from cell B1, all the way to the right in row 1 to the rightmost end.
If we now add the function TRANSPOSE, the results will change from a horizontal array, into a vertical one:
Amato, Nicholas M
Baldwin, Cynthia A
As for the function IFERROR:
 between square brackets 
#N/A
 between square brackets 
Seems to be an error somewhere
If the function REGEXEXTRACT tries to extract text ,  for example between square brackets,  which are not found, it results in an error. The function IFERROR prevents that.

Not quite finished yet. I think I'll continue in a few days, perhaps Sunday. If you didn't see a post by me on Monda. please post back to remind me....
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/25/12 3:37 PM
Hi

Please have a look at sheet: Sheets_MP of your spreadsheet.

Op donderdag 24 mei 2012 18:53:09 UTC+2 schreef daniness het volgende:
Hi MP and All,

Thanks for your feedback. So I now understand what you meant by how all the sheets should have the columns designated by the same letters, G through K and I've I corrected this for every worksheet. However, I'm not sure about the formulas you've added, i.e.: =ARRAYFORMULA(TRANSPOSE(IFERROR(REGEXEXTRACT('Form Master'!B1:1;"\[(.*)]"))))

In cells V1 to V21 I've tried to give an explanation to this question.
 
and the VMerge formulas, etc.

Cells V23 to Z36

Is there a simpler way? To answer your question, I'd like to get the COOP Master Roster sheet to display the corresponding status for each employee by pulling it from the various worksheet, i.e. I'd like Amato, Nicholas M on the COOP Master Roster to pull the status from QSDHA1 (Adm) sheet.

In cell B25 I've tried to get your expected results. Please let me know if you have questions or suggestions about it.

 
I also came across another thread where someone is trying to do something similar: https://productforums.google.com/forum/#!msg/docs/km_Y-uph_m4/BPvRzS2lar0J . In my case, each department's worksheet would be comparable to this person's "Sheet 1" which they are using as a reference to get the draft value based on the player's name whereas I'd like to get the employee status, and my COOP Master Roster sheet would be comparable to this person's "Sheet 2" because this is where I'd like the status to pull to. Please let me know if I can further clarify. I appreciate all your help!

It seems to me there is at least one difference. The other person is talking about sheet1 and sheet2. However, you have multiple sheet1's. If I'm right about 22. One other thing: The other person is suggesting that some names on sheet2 might not be found on sheet1. Therefore my question: Is it intentional that there are some names on the COOP Master sheet which are not be found on other sheets, and some names on the other sheets are not found on the COOP Master sheet? (O11 to P21) 
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 5/29/12 1:05 PM
Hi MP & Everyone,

Thanks so much for your insight. I really appreciate it. I'm just not good with the complex formulas  such as in cell B25, as you are.

It seems to me there is at least one difference. The other person is talking about sheet1 and sheet2. However, you have multiple sheet1's. If I'm right about 22. One other thing: The other person is suggesting that some names on sheet2 might not be found on sheet1. Therefore my question: Is it intentional that there are some names on the COOP Master sheet which are not be found on other sheets, and some names on the other sheets are not found on the COOP Master sheet? (O11 to P21)  

It actually is not intentional that some names are not on both the COOP Master and the other sheet. For our purposes, we can eliminate those names. Would you know of a way of pulling the statuses for each name from the various department sheets onto the COOP Master Roster sheet through a script that might be easier than using a formula? Now don't get me wrong, I'm not comfortable with writing scripts either, but I'll try anything at this point.

I appreciate your assistance!
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/29/12 1:58 PM
Hi

The formula in cell B25 is actually not as complex at it seems, but it is very long, and that's because you have so many sheets.

=SORT(vmerge(FILTER(INDIRECT(A1&"!A2:K");INDIRECT(A1&"!C2:C")<>"");FILTER(INDIRECT(A2&"!A2:K");INDIRECT(A2&"!C2:C")<>"");FILTER(INDIRECT(A3&"!A2:K");INDIRECT(A3&"!C2:C")<>"");FILTER(INDIRECT(A4&"!A2:K");INDIRECT(A4&"!C2:C")<>"");FILTER(INDIRECT(A5&"!A2:K");INDIRECT(A5&"!C2:C")<>"");FILTER(INDIRECT(A6&"!A2:K");INDIRECT(A6&"!C2:C")<>"");FILTER(INDIRECT(A7&"!A2:K");INDIRECT(A7&"!C2:C")<>"");FILTER(INDIRECT(A8&"!A2:K");INDIRECT(A8&"!C2:C")<>"");FILTER(INDIRECT(A9&"!A2:K");INDIRECT(A9&"!C2:C")<>"");FILTER(INDIRECT(A10&"!A2:K");INDIRECT(A10&"!C2:C")<>"");FILTER(INDIRECT(A11&"!A2:K");INDIRECT(A11&"!C2:C")<>"");FILTER(INDIRECT(A12&"!A2:K");INDIRECT(A12&"!C2:C")<>"");FILTER(INDIRECT(A13&"!A2:K");INDIRECT(A13&"!C2:C")<>"");FILTER(INDIRECT(A14&"!A2:K");INDIRECT(A14&"!C2:C")<>"");FILTER(INDIRECT(A15&"!A2:K");INDIRECT(A15&"!C2:C")<>"");FILTER(INDIRECT(A16&"!A2:K");INDIRECT(A16&"!C2:C")<>"");FILTER(INDIRECT(A17&"!A2:K");INDIRECT(A17&"!C2:C")<>"");FILTER(INDIRECT(A18&"!A2:K");INDIRECT(A18&"!C2:C")<>"");FILTER(INDIRECT(A19&"!A2:K");INDIRECT(A19&"!C2:C")<>"");FILTER(INDIRECT(A20&"!A2:K");INDIRECT(A20&"!C2:C")<>"");FILTER(INDIRECT(A21&"!A2:K");INDIRECT(A21&"!C2:C")<>"");FILTER(INDIRECT(A22&"!A2:K");INDIRECT(A22&"!C2:C")<>""));6;1;1;1)

As you can see, the same part is repeated 22 times:

FILTER(INDIRECT(A1&"!A2:K");INDIRECT(A1&"!C2:C")<>"")

which can be simplified by omitting the function INDIRECT:

FILTER(Sheetname!A2:K;Sheetname!C2:C<>"")

So it's a pretty simple formula, which filters only the rows where column C is not empty. Then you have to change the word "Sheetname" into the real sheetnames, so you don't need the list of names in cells A1 to A22. Then putting all those 22 formulas into Vmerge:

vmerge(FILTER(Sheet1!A2:K;Sheet1!C2:C<>"");FILTER(Sheet2!A2:K;Sheet2!C2:C<>"");....;.....;....)

to put all the data from different sheets into one. Then finally sort all data on the 6th column first, and then on the first column, both ascending:

=SORT( long story ;6;1;1;1)

So, does the formula provide your expected results, or did you have something different in mind?

Would you know of a way of pulling the statuses for each name from the various department sheets onto the COOP Master Roster sheet through a script that might be easier than using a formula? 

Well, I actually know nothing about scripts, so I'm not able to answer that question.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 5/29/12 2:28 PM
Hi

You also might want to use this formula on an empty sheet:

=QUERY(vmerge('QSDHA1 (Adm)'!A2:K;'QSDHAA (Fac)'!A2:K;'QSDHAAB (Bin)'!A2:K;'QSDHAAEC (GN4)'!A2:K;'QSDHAD (IT)'!A2:K;'QSDHAAEB (Pro)'!A2:K;'QSDHAAE (Exp)'!A2:K;'QSDHAAAA (Rec)'!A2:K;'QSDHAAEA (GN3)'!A2:K;'QSDHAA1 (For)'!A2:K;'QSDHAAA (S_R)'!A2:K;'QSDHAAAB (Sto)'!A2:K;'QSDHAAG (Ngt)'!A2:K;'QSDHAAC (Bul)'!A2:K;'QSDHAAD (Dom)'!A2:K;'QSDHAAF (MHE)'!A2:K;'QSDHAAGA (Bul)'!A2:K;'QSDHAAGB (Exp)'!A2:K;'QSDHAAGC (Bin)'!A2:K;'QSDHAB (ISB)'!A2:K;'QSDHAAGD (Sto)'!A2:K;'QSDHAC (CSC)'!A2:K);"select * where Col3 <> '' order by Col6 , Col1 ";)
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/1/12 12:40 PM
I've been wracking my brain all week on this:

The formula in cell B25 is actually not as complex at it seems, but it is very long, and that's because you have so many sheets.

=SORT(vmerge(FILTER(INDIRECT(A1&"!A2:K");INDIRECT(A1&"!C2:C")<>"");FILTER(INDIRECT(A2&"!A2:K");INDIRECT(A2&"!C2:C")<>"");FILTER(INDIRECT(A3&"!A2:K");INDIRECT(A3&"!C2:C")<>"");FILTER(INDIRECT(A4&"!A2:K");INDIRECT(A4&"!C2:C")<>"");FILTER(INDIRECT(A5&"!A2:K");INDIRECT(A5&"!C2:C")<>"");FILTER(INDIRECT(A6&"!A2:K");INDIRECT(A6&"!C2:C")<>"");FILTER(INDIRECT(A7&"!A2:K");INDIRECT(A7&"!C2:C")<>"");FILTER(INDIRECT(A8&"!A2:K");INDIRECT(A8&"!C2:C")<>"");FILTER(INDIRECT(A9&"!A2:K");INDIRECT(A9&"!C2:C")<>"");FILTER(INDIRECT(A10&"!A2:K");INDIRECT(A10&"!C2:C")<>"");FILTER(INDIRECT(A11&"!A2:K");INDIRECT(A11&"!C2:C")<>"");FILTER(INDIRECT(A12&"!A2:K");INDIRECT(A12&"!C2:C")<>"");FILTER(INDIRECT(A13&"!A2:K");INDIRECT(A13&"!C2:C")<>"");FILTER(INDIRECT(A14&"!A2:K");INDIRECT(A14&"!C2:C")<>"");FILTER(INDIRECT(A15&"!A2:K");INDIRECT(A15&"!C2:C")<>"");FILTER(INDIRECT(A16&"!A2:K");INDIRECT(A16&"!C2:C")<>"");FILTER(INDIRECT(A17&"!A2:K");INDIRECT(A17&"!C2:C")<>"");FILTER(INDIRECT(A18&"!A2:K");INDIRECT(A18&"!C2:C")<>"");FILTER(INDIRECT(A19&"!A2:K");INDIRECT(A19&"!C2:C")<>"");FILTER(INDIRECT(A20&"!A2:K");INDIRECT(A20&"!C2:C")<>"");FILTER(INDIRECT(A21&"!A2:K");INDIRECT(A21&"!C2:C")<>"");FILTER(INDIRECT(A22&"!A2:K");INDIRECT(A22&"!C2:C")<>""));6;1;1;1)

Could you please advise where this formula should be placed? I'm trying to put it in cell G2 on the COOP Master Roster sheet, but it's giving me a parse error. I'm so confused :-(. Could you please advise? Thanks and I appreciate your help!

 
 
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 6/1/12 12:48 PM
Hi

Do you please have time to chat via the chat window of your spreadsheet?
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/4/12 8:50 AM
Hi MP and All,

Thanks for your help thus far. I've tried using the formula you suggested a few days ago: =QUERY(vmerge('QSDHA1 (Adm)'!A2:K;'QSDHAA (Fac)'!A2:K;'QSDHAAB (Bin)'!A2:K;'QSDHAAEC (GN4)'!A2:K;'QSDHAD (IT)'!A2:K;'QSDHAAEB (Pro)'!A2:K;'QSDHAAE (Exp)'!A2:K;'QSDHAAAA (Rec)'!A2:K;'QSDHAAEA (GN3)'!A2:K;'QSDHAA1 (For)'!A2:K;'QSDHAAA (S/R)'!A2:K;'QSDHAAAB (Sto)'!A2:K;'QSDHAAG (Ngt)'!A2:K;'QSDHAAC (Bul)'!A2:K;'QSDHAAD (Dom)'!A2:K;'QSDHAAF (MHE)'!A2:K;'QSDHAAGA (Bul)'!A2:K;'QSDHAAGB (Exp)'!A2:K;'QSDHAAGC (Bin)'!A2:K;'QSDHAB (ISB)'!A2:K;'QSDHAAGD (Sto)'!A2:K;'QSDHAC (CSC)'!A2:K);"select * where Col3 <> ''";), but it's not working and instead is giving me an "Invalid query: Column [Col3] does not exist in table." error, but I checked each worksheet tab and verified that there is a 3rd column and I'm not sure why this is happening. Could you please advise? Thanks.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 6/4/12 9:21 AM
Hi

When I watch the formula in cell A2 of COOP Master Roster, the formula seems to work fine. If you do so, you get an error?

So I can't say what was the problem. Seems very strange to me.....
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/4/12 9:31 AM
Hello MP,

This is actually happening in the Google spreadsheet I'm working on which is behind our firewall here, which requires a logon. I've been using the spreadsheet that I was able to share to serve as a model for the actual one I'm working on. The actual one won't allow me to share...is there another way you can view it, via screen sharing or something?
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 6/4/12 10:14 AM
Hi

Are you sure that you have installed the script: VMerge, into the other spreadsheet?
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/4/12 10:24 AM
Hi,

I actually did not, so I just installed it, reloaded the spreadsheet, reinserted the formula, but am still getting the same "Invalid query: Column [Col6] does not exist in table." :-(. Any other suggestions you might have, please?
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/7/12 7:06 AM
MP..........Help please!!! So I thought everything was working fine, but now when I test the spreadsheet, not all cells on the COOP Master tab are populating correctly from the corresponding spreadsheet, specifically the QSDHA1 (Adm) and the QSDHAA (Fac) tabs. To confirm, I made sure that the VMerge script was installed and then reloaded the spreadsheet, but not all the cells on COOP Master are pulling the data from all the tabs...any idea as to why? I appreciate your continuous assistance and expertise!
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 6/7/12 8:41 AM
Hi

If a formula seems not to work correctly, then split it in smaller parts. For example, start with:

=VMerge('QSDHA1 (Adm)'!A2:K;'QSDHAA (Fac)'!A2:K)

Like I did in cell L2 of sheet COOP_MP. If that's working fine, then put that part into the QUERY:

=QUERY(VMerge('QSDHA1 (Adm)'!A2:K;'QSDHAA (Fac)'!A2:K);"select * where Col3 <> '' order by Col6 , Col1 ";)

That way you might find the cause of the problem.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/7/12 12:10 PM
Once again, thanks for saving the day, MP. It seems to be pulling the data into the COOP Master from the other tabs correctly now....you're a rockstar! :-)
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 6/7/12 12:47 PM
Hi

You're welcome!

Glad that you could solve the problem.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/13/12 12:45 PM
Hi MP and All,

For some odd reason, the array formulas are not working properly on each individual department tab of the spreadsheet. Here is a sample formula I'm using on one of the tabs as per your suggestion;

 
=ARRAYFORMULA(IF(TRANSPOSE(FILTER('Form Master'!C:T, 'Form Master'!A:A=MAX('Form Master'!A:A)))=G1:I1,G1:I1,))

and I've altered the letters in red for each tab to reflect the corresponding cell range based on the Form Master tab to show the correct employees, but the data is not correctly populating. I'm not sure why it stopped working because at one point it all appeared fine. Could you please advise??
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 6/13/12 1:03 PM
Hi

If I'm right you've been talking about 3 different spreadsheets, during this topic:
  1. A small example sheet, of which you posted the link in your first post. (Shared with anyone with the link)
  2. A big example sheet, of which you posted the link later. (also shared)
  3. The "real" spreadsheet, which is not shared at all.
I guess, the problem you're talking about occurs in #3, right?

If so, I would like to advise that you reproduce the problem on one of the other spreadsheets which are shared, and tell us on which sheet and in which cell the problem occurs.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/22/12 12:06 PM
Hi MP!

Thanks for your response to my previous post. I'm now actually running into another issue. I need to display the datetime stamp on corresponding worksheets, if an employee from a certain department has their status updated, according to the the datetime stamp from the last added row on the Form Master tab. This is the formula I've been trying to use on each of the department worksheets:

=if(('Form Master'!A:A)=MAX(Not(ISBLANK)), ('Form Master'!A:A),' ') 

but am having to luck as it is just gives me a parse error. Any thoughts? Thanks! 
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 6/23/12 3:41 AM
Hi

Your formula does not have a correct syntax. If you only would like to have the latest timestamp in column A of 'Form Master'!A2:A then this will be sufficient:

=MAX('Form Master'!A2:A)

However, if you wish to return the entire row, with the latest timestamp, please try this:

=FILTER('Form Master'!A2:IV;'Form Master'!A2:A=MAX('Form Master'!A2:A))

See also cells W2 and W3 of your test spreadsheet.

I hope this helps.
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/26/12 9:57 AM
Hi!

Thanks for your suggestion...this works great. I also wanted to know how to get this datetime to show on the tabs for the departments whose employees's statuses were updated via the Google form, i.e. if only the employees from the Admin department were updated, but not the IT department, how would the datetime show up on only the Admin department's tab? Do you have any suggestions? As always, I appreciate your assistance :-).
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/26/12 12:39 PM
I'm trying this formula, but it's giving me a parse error:

=if(('Form Master'!C:T; ROW('Form Master'!A:A)=MAX(Not(ISBLANK)), ('Form Master'!A:A),' ')) 

I'd appreciate any suggestions...thank you! 
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 6/26/12 4:11 PM
Hi

I'll be glad to help, but I don't understand what you're trying to do, and I don't understand at all, the formula in your last post. So I would like to suggest, to make an easy example, of what you're trying to do, in a small spreadsheet with only one or two sheets.

As for the formula in your last post:
  • The function IF doesn't accept multiple cell ranges, so it needs to be wrapped in an ArrayFormula: =ARRAYFORMULA(IF(....
  • You used 2 parentheses after the function IF: =IF((  . Then the part after the second parenthesis, is supposed to be a value, so it should not contain a semicolon.
  • The function ISBLANK seems to have no parameter at all!
daniness 6/29/12 8:01 AM <This message has been deleted.>
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 6/29/12 9:25 AM
 MP,

Sorry for the confusion. I'll try to clarify...if you please take a look at the following spreadsheet's QSDHA1(Adm) tab in cell B21: https://docs.google.com/spreadsheet/ccc?key=0Ar5XCh4AvyBLdDE2dDRXTWg5VTZuemRHWmYyT2h5R0E#gid=4, I'm trying to get the datetime stamp to show from the Form Master sheet if the statuses for the employees from this department are updated. I'd like to use this formula on the other sheets as well. I'm using this formula:

=if('Form Master'!B:S; ROW('Form Master'!A:A)=MAX(Not(ISBLANK()), ('Form Master'!A:A),' ')) 

to look at the columns that contain the employees from a specific department whose status has been updated with a "At Work", "On Leave", etc and then getting and displaying the latest datetime stamp, but if the column range for a specific department on the Form Master tab are empty and have no statuses, then I don't want the latest datetime stamp to show for that particular department's spreadsheet, which is why the last part of this formula contains ' '. I'm not sure if this is the right approach because I'd want the last date time stamp from when the spreadsheet was previously updated to show, instead of it being blank. Please let me know if I can further clarify.  
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data MarinusP 6/29/12 1:05 PM
Hi

The formula you're trying to use, still doesn't make sense to me, so I guess your explanation should do. 

You are talking about "departments". Since the sheet is called: "QSDHA1 (Adm)" may I assume that's the department?

If so, how can I detrmine, which person on sheet "Form Master" is of which department?
Re: Update corresponding column of master spreadsheet based on other spreadsheet cell data daniness 7/3/12 10:17 AM
Hi MP,

You're right, that in the formula, the department is, "QSDHA1 (Adm)".
 
If so, how can I detrmine, which person on sheet "Form Master" is of which department? 

To answer your question, I indicate the people that are in the particular department by specifying the column range that contains the name of the employees in the department:
=if('Form Master'!B:S; ROW('Form Master'!A:A)=MAX(Not(ISBLANK()), ('Form Master'!A:A),' '))
The red specifies columns B to S on the Form Master sheet, which includes the names including Amato, Nicholas to Wille, Dale E. I will change this range  to 'Form Master'!T:W for the QSDHAD (IT) department's sheet. Please let me know your thoughts and if I need to further clarify. Thanks!


  
More topics »