Forum Discussion
Solved
Move cell to match referenced cell value
Hi all!
I have two spreadsheets I'm working on, one which is read-only (we'll call it Original) and another that is pulling data from the read-only spreadsheet and being added to (we'll call it Edited). Cells A-O have formulas to pull data from Original and cells Q-V are manually filled out with information related to each row.
If rows were added on Original, how can I keep cells Q-V aligned with the correct referenced value?
For example, say cell A3 refers to apples and Q3 talks about types of apples. If I change A3 on the original to be oranges either by copy/pasting apples to a different location or by adding a row above A3, how do I set it up so Q3 queries where apples went and moves to the appropriate row?
Should I put my values for Q-V on a different sheet and set a formula "IF A3=Apples, then pull".....
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.
