Forum Discussion
Move cell to match referenced cell value
It is your intent that each row on Original represents some unique thing, right? As you have referenced apples and oranges, let's say that each row represents a type of produce. You first need to determine which column/columns on Original has/have a data value that uniquely identifies the type of produce. (Or, it may be that you need multiple columns to do that, but such a list of columns should be kept as small as possible. That requires more-complicated, less-efficient formulas, so is to be avoided where possible.) Values in other columns depend upon what that key column(s) contains.
Database systems have features that prevent key values from being duplicated (appearing more than once) in the Original types of records. But spreadsheet software does not have those features. If you want to avoid duplicates, the simple technique is to add conditional formatting to detect duplicates and flag/highlight them.The Edited sheet will preferably contain a stable copy of the key value(s) in a corresponding column(s), i.e., a copy that is a literal (such as "apples"), not simply a reference to the cell on Original that contains "apples". Other, non-key columns (within A-O) can and should be references to the Original data.
You do not indicate whether there will be a row (or even multiple rows!) on Edited for every type of produce on Original, so I won't comment on how to try to make sure that any particular key value exists on Edited.
<< how can I keep cells Q-V aligned with the correct referenced value? >>
<< ...where apples went and moves to the appropriate row? >>
Precision in terminology is important. By "aligned with", do you mean "consistent in data values with", where the two rows both refer to the same type of produce? Or do you mean "in the same row number as" (requiring moving data within Edited)? (Or do you mean something else?) The former can be done using a filtering technique (most likely the FILTER function). The latter might be doable with possibly complicated VBA (with its associated problems).
So, assuming you mean "consistent in data values"... You have generally the right idea in your last sentence of pulling data from Original, but it would be unnecessary work to put produce type-specific formulas in each row of Edited. Instead, use formulas that filter the data from Original (using the FILTER function and/or a type of lookup function) based on the stable key values on Edited.
See the attached workbook.
It is your intent that each row on Original represents some unique thing, right? As you have referenced apples and oranges, let's say that each row represents a type of produce. You first need to determine which column/columns on Original has/have a data value that uniquely identifies the type of produce. (Or, it may be that you need multiple columns to do that, but such a list of columns should be kept as small as possible. That requires more-complicated, less-efficient formulas, so is to be avoided where possible.) Values in other columns depend upon what that key column(s) contains.
Database systems have features that prevent key values from being duplicated (appearing more than once) in the Original types of records. But spreadsheet software does not have those features. If you want to avoid duplicates, the simple technique is to add conditional formatting to detect duplicates and flag/highlight them.
The Edited sheet will preferably contain a stable copy of the key value(s) in a corresponding column(s), i.e., a copy that is a literal (such as "apples"), not simply a reference to the cell on Original that contains "apples". Other, non-key columns (within A-O) can and should be references to the Original data.
You do not indicate whether there will be a row (or even multiple rows!) on Edited for every type of produce on Original, so I won't comment on how to try to make sure that any particular key value exists on Edited.
<< how can I keep cells Q-V aligned with the correct referenced value? >>
<< ...where apples went and moves to the appropriate row? >>
Precision in terminology is important. By "aligned with", do you mean "consistent in data values with", where the two rows both refer to the same type of produce? Or do you mean "in the same row number as" (requiring moving data within Edited)? (Or do you mean something else?) The former can be done using a filtering technique (most likely the FILTER function). The latter might be doable with possibly complicated VBA (with its associated problems).
So, assuming you mean "consistent in data values"... You have generally the right idea in your last sentence of pulling data from Original, but it would be unnecessary work to put produce type-specific formulas in each row of Edited. Instead, use formulas that filter the data from Original (using the FILTER function and/or a type of lookup function) based on the stable key values on Edited.
See the attached workbook.