Excel on google docs, summarising and coverging sheets

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:33 PM GMT

Question

QUESTION:

I hope you can help- i have had a look around and since im fairly new toi can't seem to fathom how to do somehting i really want to do!

essentially i have andocument on google docs. I have a master sheet which includes columns of all sorts of different data, some numbers, ie dates and some words, ie yes/no. Different people at different times update the cells when necessary.

Essentially what i want is a seperate sheet next to this master sheet which shows only selected columns (i dont need all columns in the master sheet, just some) and instead of a date ie 3/3/2009 i need it to say 'yes'(as in completed) and instead of a blank in that column i need it to say 'no'. A tick or a cross would also be fab but anything resembling yes and no is fine :)

I'm sure there should be a way when the master sheet on google docs gets updated by users, this automatically fills in the summary sheet of my selected columns- that way i can see at a glance what has and hasn't been done, and so can everyone else.


Any help you could give me would be super- this would make my ALOT easier and i will be sure to spread theabout your advice and the site :) it's great that we can all share info and knowledge in this way.

natalie

ANSWER: In Google Docs Spreadsheet, if you want a master sheet that shows selected columns of other sheets, you can use the ArrayFormula and Continue function.

I have created an example in Google Docs and you can access it using the link below. http://spreadsheets.google.com/ccc?key=0ApiGr9iIPRJpdHBmVXZfLXZxTTFVUERFekNKWkVEc2c&hl=en

Please let me know if this is what you are looking for?

As for the second part of your question: "instead of a date ie 3/3/2009 i need it to say 'yes'(as in completed) and instead of a blank in that column i need it to say 'no' ...." What is the date you are refering? Is it something that is entered by users?


---------- FOLLOW-UP ----------

QUESTION:

in terms of the date, yes this is entered by users, the whole document is shared amongst more than ten people who all change the cells when necessesary, so in the master sheet at the moment they put a dat in an individual sum- i want the summary sheet to say 'yes' so i can tell that it has been done. If there isn't a date, i want it to say 'no' it would also be usefull to know how to simply replicate the values in the cells from the master sheet too..as in your example.

Array formulas and continue functions are new ones tome i'm afraid, shall i use the help section into work it out or do you have some quick tips?

thanks again for this, it really is so nice of you to help me out,is something so powerful when you know how, i just dont!

hopefully you can help me a bit more, sorry for asking more questions!


natalie

Answer

As seen in my example, in the Summary sheet cell A1, type the following formula: =Data!A:A followed by Ctrl-Shift-Enter (hold and press together at the same time) and Google Docs will automatically converts it to =ArrayFormula(Data!A:A) and fills up the rest of the rows of the column A with the Continue function. Repeat this process for the first row of each column as needed.

For the cell that contains Date, for example in C2 in worksheet Data, use the following formula in any cell in Summary sheet to show "Yes" for date an "No" if cell C2 does not contain a date.

=if(isnumber(datevalue(Data!C2)),"Yes","No")


Hope this helps. Cheers!

Advertisement

©2021 eLuminary LLC. All rights reserved.