2

background

I like to use story mapping to gather requirements from clients (and to create estimates for them) using cardboardit.com. I'm currently making a sample estimate for a project that I have completed in the past (http://vibereel.com/).

Cardboardit offers the ability to dump data into a csv file, which can been in raw form in the data tab in this sheet

The data tab is pretty raw, so i used this formula to make it look nicer in the estimate tab:

=QUERY(data!A2:J,"select C,E,D where A='card' and E<>'white' order by I,J label C 'name', E 'color', D 'notes' ",0)

I then ask engineers to fill in the estimates in the rows. Engineers fill in the estimates under the other two columns (front end and backend)..

problem

The problem is when I realise that I missed some elements in the user map. So I adjust the user map, export the data once again and dump it in the data sheet. considering that the above formula is smart, the estimate sheet gets updated immediately and accurately.

However the problem is that the values the engineers entered are now misaligned with the new values.. Ie a front end task called "edit vibereel" previously had an estimate of 1 day, but once the new data got entered.. that one got misplaced

question

how can I do this so that - whenever engineers enter a value under the two columns, those values become locked, or linked to the tasks they were originally intended for under the name column.

enter image description here

alternative question

Another way of solving this can be that each time the data sheet is changed (due to changes in user map).. the new data set can be dumped into a new data sheet (ie data-v2), and another estimate sheet can be created (ie estimate-v2). this would make business sense as it shows the client the change in estimates.. In that case how would it be possible to simply copy and paste the estimates from estimate to estimate-v2 and still link them to the appropriate estimates?

Rubén
  • 34,714
  • 9
  • 70
  • 166
abbood
  • 23,101
  • 16
  • 132
  • 246
  • Excel and Google Sheets are similar but not the same. I.E. QUERY spreadsheet function is not available in Excel but it has another tool to do similar tasks (actually it does a lot more), but it only works on the installable application, AFAIK. – Rubén Nov 22 '16 at 13:09

2 Answers2

3

Short answer

It's not possible to directly lock manually entered values to formula resulting rows. Instead use a backend/frontend architecture kind.

Broad instructions

  1. Backend table. Create a sheet to be used as a "mirror" of the Cardboard it! data and of the estimates entered by the Engineers on the spreadsheet (One backend, two frontends)

    1.1 Alternative 1: Use scripts to update the data values from the two sources.
    1.2 Alternative 2: Use formulas to join the data values from the two sources.

  2. Frontend workspace. Use this sheet for manual data input of the time estimates.

    2.1 To make easier keep data synced, include the id from the Cardboard it! CSV file (assuming that this is a persistent ID)

    • 2.1.1 You could hide this column to keep the look of your current layout.

    2.2 Pass data from the backend table

    • 2.2.1 If you keep using formulas, "freeze" the data to prevent data that manually entered estimates be misaligned by copy and paste values only. This could be done by using UI commands, keyboard shortcuts or a script.

Remarks

If you will be using the spreadsheet on meetings, I think that a time-driven trigger or a custom menu are better than a on edit trigger due to time that it take to run the script to avoid distractions during the meeting.

If you want to keep revisions, make a copy of the spreadsheet each time you import a CSV file or have estimates review meeting. Keep the copy as revision snapshot and keep working on the original file. This is because the Google Sheets revision history could have too many details to make easy to jump to a major revision, but could be helpful if you need to do a detailed history revision.

Later you could use IMPORTRANGE to put together all the revision estimates, i.e. to make a chart of the estimates change overtime.

Implementation Example based on formulas.

Start point

Let say that you have two spreadsheets - data : It holds the data from the first export to a CSV file. - estimates : It holds the layout shown on the screen shot.

Step 1

Add the id values to the estimates sheet. Let say that it's inserted as Column D.

Step 2

After changes were made to the cardboard on Cardboard it!, download and import a new CSV file, let say that this new sheet is called data-v2. For simplicity, on this example it's added on the same spreadsheet.

Step 3

Add the following formula to data-v2!L1

=ArrayFormula(ARRAY_CONSTRAIN({{"frontend","backend"};IFERROR(VLOOKUP(B2:B,{'estimate-v1'!D:D,'estimate-v1'!E:F},{2,3},FALSE()),)},counta(A:A),2000000))

Commented formula

=ArrayFormula( // Tells Google Sheets that non array functions should work as array 
functions and that the result should be expanded to the required area (rows/columns)
ARRAY_CONSTRAIN( // Limit results to the required number of rows.
{ // Starts a new array
{"frontend","backend"}; // Array with the column headers
IFERROR( // In case of error will return a blank
VLOOKUP(
B2:B, // Range with the values look for.
{'estimate-v1'!D:D,'estimate-v1'!E:F}, // Array. This is my preferred notation considering 
that that the ID column could be moved in the future, but at this time it could be replaced
 by 'estimate-v1'!D:F
{2,3}, // This makes that VLOOKUP returns a array with two columns. The first column is 
filled up with values from the second column, the column is filled up with values from the 
third column.
FALSE() // This tells VLOOKUP that the second argument is not sorted.
),)
},
counta(A:A), // This limits the resulting rows to those that are required (discards blank 
rows)
2000000 // A big number (it's the maximun number of cells)
))

Step 4

  1. Create a new sheet for the new estimates review.
  2. Add the following formula to A1

    =QUERY('data-v2'!A2:M,"select C,E,D,B,L,M where A='card' and E<>'white' order by I,J 
    label C 'name', E 'color', D 'notes', B 'id', L 'front end', M 'backend' ",0)
    
  3. Select all, copy and paste as values.

  4. Apply formatting and formulas to calculate the estimates totals.

Cardboard it! CSV headers

  • kind
  • id
  • name
  • description
  • color
  • status
  • size
  • annotation
  • x
  • y
  • image_url

See also

References

Community
  • 1
  • 1
Rubén
  • 34,714
  • 9
  • 70
  • 166
  • for this part `=QUERY('data-v2'!A2:M,"select C,E,D,B,L,M where A='card' and E<>'white' order by I,J label C 'name', E 'color', D 'notes', B 'id', L 'front end', M 'backend' ",0)` assuming that i would like to simply provide the default value of estimates on `estimate-v2`, but also allow the engineers to _revise_ their old estimates.. i can simply just manually copy the values only from the `front end` and `back end` columns right? (ie paste value only) – abbood Nov 23 '16 at 15:08
  • Or you could use the columns to the right for the new estimates. – Rubén Nov 23 '16 at 15:10
1

Possible solution

It's too big to post it in single answer, but the plan is:

  1. make one more 'help' tab to store the data.
  2. connect current tab and 'help' tab by ID. ID in your example is column A: name. Use ArrayFormula + vlookup to make it update dynamically.
  3. make script onEdit: when the target column is being edited in current tab → read the value → clear cell → paste the value into the 'help' tab. And formula will bring the correct value back.

I've implemented this in my projects. The disadvantage is that you have to wait about 0.5 sec. while script is running when you edit cells.

I've made sample sheet, you may test it.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • 1
    your idea seems interesting.. but i'll need some working sample that demonstrates it at work, since it leaves a lot for imagination. You can apply it to my sheet or a sample sheet of your own if you prefer to work with a smaller data set – abbood Nov 22 '16 at 21:00
  • 1
    I've made a sample, please try. – Max Makhrov Nov 23 '16 at 08:24
  • your answer didn't work b/c in this formula: `{"Id";ArrayFormula(ROW(INDIRECT("A1:A"&max(data!B:B))))}` you are getting the max of `data!B:B`.. which is 984060 (id's generated by user map have huge values).. and so when i put the formula i get this error: `Result was not expanded automatically, please insert more rows (934061).`.. see experiment [in this sheet](https://docs.google.com/spreadsheets/d/1U1FtkwL-9R4mEbZsGsSUpSuOutPvMLhWnmWLpLePDfI/edit#gid=254239024) – abbood Nov 23 '16 at 11:52
  • i tested on the sample spreadsheet you provided.. it broke when i added IDs not in order and not beginning at 1, see [here](https://docs.google.com/spreadsheets/d/1T1QzqhledQcWmMF7MsOl5hgMtInY6NaezyvGu9BN2PU/edit#gid=360593442) – abbood Nov 23 '16 at 12:05
  • do you really have 984060 rows in your spreadsheet? By ID I mean the number of entry. If it is not, then script must also read all ID's from help sheet which will slow down the script. – Max Makhrov Nov 23 '16 at 12:37
  • i don't have 984060 rows.. that's just the max ID i get from the user map. – abbood Nov 23 '16 at 12:40
  • In help sheets ID's are to be sorted, because it is technical sheet. User may not even have direct access to this sheet. ID's are sorted because when you are using onEdit trigger, the speed is very important. But reading data from sheets is very slow (I hope, google will improve it somehow, because it works very fast in Excel). And the same reason is user's IDs can't be used. User may enter any value as ID. But we need simple numbers, starting from 1. I suggest generating ID's automatically and hide it, so user can't break them. – Max Makhrov Nov 23 '16 at 12:46
  • In relation to the [second comment](http://stackoverflow.com/questions/40738862/how-to-lock-content-of-a-dynamically-generated-cell-with-another-cell/40743184#comment68751895_40743184) by @abbdood, try COUNT instead of MAX on a column that holds the IDs. (IDs are numbers, the minimum and maximum IDs could be "any" integer). – Rubén Nov 23 '16 at 14:49