如何将动态生成的单元格的内容与另一个单元格锁定 [英] how to lock content of a dynamically generated cell with another cell
问题描述
背景
我喜欢使用
替代问题
解决此问题的另一种方法是，每次更改数据表时(由于用户映射的更改)..新数据集可以转储到新数据表中(即data-v2)，并且 estimate-v2 仍将它们链接到适当的估算值?
简短答案
不可能直接将手动输入的值锁定到公式结果行.而是使用后端/前端架构类型.
广泛说明
-
后端表.创建一个表，用作Cardboard的镜像"！数据和工程师在电子表格上输入的估算值(一个后端，两个前端)
1.1备选方案1:使用脚本来更新两个来源的数据值.
1.2备选方案2:使用公式将两个来源的数据值合并在一起. -
前端工作区.使用此表可以手动输入时间估算值.
2.1为了更轻松地保持数据同步，请在Cardboard中添加ID！CSV文件(假设这是一个永久ID)
- 2.1.1您可以隐藏此列以保持当前布局的外观.
2.2从后端表传递数据
- 2.2.1如果您继续使用公式，请冻结"数据，以防止仅通过复制和粘贴值将手动输入的估计值对齐.这可以通过使用UI命令，键盘快捷键或脚本来完成.
备注
如果要在会议上使用电子表格，我认为时间驱动的触发器或自定义菜单比编辑时的触发器要好，这是因为运行脚本需要花费时间以避免在会议期间分散注意力.
如果要保留修订版本，请在每次导入CSV文件或进行预算审核会议时复制电子表格.将副本保留为修订快照，并继续使用原始文件.这是因为Google表格修订历史记录可能包含太多详细信息，以致于无法轻松跳转至主要修订，但是如果您需要进行详细的历史修订，则可能会有所帮助.
稍后，您可以使用IMPORTRANGE汇总所有修订估算值，即制作估算值随时间变化的图表.
基于公式的实施示例.
起点
假设您有两个电子表格-数据:保存从第一次导出到CSV文件的数据.-估算值:它保留屏幕截图中显示的布局.
步骤1
将ID值添加到估算表中.假设它被插入为D列.
步骤2
在Cardboard it！上的纸板上进行更改后，下载并导入新的CSV文件，可以说这个新表名为data-v2.为简单起见，在此示例中，将其添加到同一电子表格中.
步骤3
将以下公式添加到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))
注释公式
= ArrayFormula(//告知Google表格非数组函数应作为数组工作函数，结果应扩展到所需区域(行/列)ARRAY_CONSTRAIN(//将结果限制为所需的行数.{//开始一个新数组{前端"，后端"}；//具有列标题的数组IFERROR(//如果出现错误，将返回一个空白VLOOKUP(B2:B，//查找值的范围.{'estimate-v1'！D:D，'estimate-v1'！E:F}，//数组.考虑到这是我的首选表示法以便将来可以移动ID列，但此时可以将其替换通过'estimate-v1'！D:F{2,3}，//这使得VLOOKUP返回一个包含两列的数组.第一列是填充第二列中的值，则该列填充中来自第二列的值第三栏.FALSE()//这告诉VLOOKUP第二个参数未排序.)，)}，counta(A:A)，//将结果行限制为所需的行(丢弃为空白)行)2000000//一个很大的数字(这是单元格的最大数量))) 步骤4
- 为新的估算值评估创建一个新工作表.
-
将以下公式添加到A1
= QUERY('data-v2'！A2:M，按C选择E，C，E，D，B，L，M，其中A =卡"和E白"阶，Ĵ标签C'名称'，E'颜色'，D'注释'，B'id'，L'前端'，M'后端'，0) -
全选，复制并粘贴为值.
- 应用格式和公式来计算估计总数.
纸板！CSV标头
- 种类
- id
- 名称
- 说明
- 颜色
- 状态
- 大小
- 注释
- x
- y
- image_url
另请参见
参考
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.
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?
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
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.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
- Create a new sheet for the new estimates review.
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)Select all, copy and paste as values.
- 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
这篇关于如何将动态生成的单元格的内容与另一个单元格锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
