Saturday, June 4, 2011

yogi_Recreate Form Entries In Rows For All Submittals

Yogi Anand, D.Eng, P.E.                                   Google Spreadsheet                     www.energyefficientbuild.com

manchi said:
How to add the inputs one below the other- New entries should be added after the last line..
Say for example, I am creating a form for Job Requirement.. It has multiple fields(like reporting manger, Job Duties, responsibilities etc).. Is there a way when somebody fills form, it gets added like a word document format(One below the other, Not adding in columns!)..I am not sure I am explaining correctly, But I can explain more if needed.


More info: If I have n fields, all the fields should get entered in n rows and the new entry should start from n+1 row and so on.
-----------------------------------------------------------------



Manchi commented ...
Thanks Yogi, This worked.. This is exactly how I wanted and one more small request.. How to have it start from n+2 row(this way I will have a empty row between the entries).

I have added some more formulas in yogi_Sheet1, and yogi_Sheet1a; I have modified the formula in yogi_Sheet2b, and I have added yogi_Sheet2c.


Manchi commented ...
How to highlight the entire row of Timestamps (I want to highlight all the rows which have timestamp). I was able to just highlight the cell containing Timestamp not the entire row.
Google spreadsheet does not have a built-in feature to highlight an entire row. However, my workaround presented in Sheet2d might be of some interest to you.

11 comments:

  1. Thanks Yogi, This worked.. This is exactly how I wanted and one more small request.. How to have it start from n+2 row(this way I will have a empty row between the entries).

    Thanks,
    Manchi

    ReplyDelete
  2. Hi Manchi:

    I have modified some formulas and I have added yogi_Sheet2c -- to incorporate an empty line between the entries.
    I hope this helps.

    Cheers!
    Yogi Anand

    ReplyDelete
  3. Yogi,When I am trying to copy the formula for "=ArrayFormula(if(len(A:A),char(10),iferror(1/0)))" its giving me a circular redundancy error. I was able to copy the same formula on sheet 1(Nth column with no problem". Could you please look into this.

    ReplyDelete
  4. I got it worked, I modified the formula in A2 on Sheet1a(Changed from N$1 to M$1). Still not getting the blank row between the entries.

    ReplyDelete
  5. Nevermind Yogi, Got it worked with the empty row.. First time I did with only 3 colums, Now I have it with Sheet1, Sheet1a, 1b and 1c. Thanks a lot for all your help.

    ReplyDelete
  6. Sorry one more request. How to highlight the entire row of Timestamps (I want to highlight all the rows which have timestamp). I was able to just highlight the cell containing Timestamp not the entire row.

    ReplyDelete
  7. Hi Manchi:

    The formula =ArrayFormula(if(len(A:A),char(10),iferror(1/0)))
    is supposed to be entered in row1 of the first blank column yogi_Sheet1 (the sheet with data)--
    is there any existing formula in that cell?
    I suggest you share your spreadsheet with me and I will check what is happening.

    Cheers!
    Yogi

    ReplyDelete
  8. Hi Manchi:

    In response to your statement ...
    How to highlight the entire row of Timestamps (I want to highlight all the rows which have timestamp). I was able to just highlight the cell containing Timestamp not the entire row.

    Google spreadsheet does not have a built-in feature to highlight an entire row. However, my workaround presented in Sheet2d might be of some interest to you.

    Cheers!
    Yogi

    ReplyDelete
  9. Thanks Yogi,

    Also in the form, can the new entries be on the top row , instead of adding them to the bottom row.

    ReplyDelete
  10. Hi Manchi:

    The best way to do that would be to create a sister sheet to the ResposeTable in which the data coming from the ResponseTable is automatically sorted in descending order -- then you apply the formulas I have provided to the sister sheet rather than to the actual ResponseTable.

    I hope this helps.

    Cheers!
    Yogi

    ReplyDelete
  11. Yogi, How will I create a sister sheet and sort automatically?

    ReplyDelete