If cell is value, duplicate range of cells into other cells

[Chipper9979]

Hi all, complete newcomer here and I could do with some help. I’m trying to make something that I see as a shortcut for copying and pasting from cells into other cells.

What I need is:

If A3 is 9 then B3 to V3 should be the same as B9 to V9, and at the same time if A3 is 10 then B3 to V3 should be the same as B10 to V10, and at he same time if A3 is 11 then B3 to V3 should be the same as B11 to V11 etc.

I want to be able to input any number from 9-32 in A3 and have it repeat/instantly copy the relevant cells from those rows into the relevant cells in row 3.

Is it even possible to do it as a range of cells like that or would it have to be done one cell at a time? One at a time would be in B3 make a rule that if A3 is 9 then B9, if A3 is 10 then B10, if A3 is 11 then B11 etc. then go into C3 and make it so that if A3 is 9 then C9, if A3 is 10 then C10, if A3 is 11 then C11 etc.

Whether doing at as a group or one cell at a time I don’t know what to put in the input line.

Thanks for reading and for any help!

[anon73440385]

Hello,

please see the following sample file implementing your requirements. Just add the number of the row you want to reference to into cell A3. The function used is based on OFFSET() which just looks down the column, starting from the actual cell. The number of rows to look down is calculated (e.g by $A3-ROW(B3) in formula in cell B3). To visualize the current row referenced a conditional formatting has been defined (see Format -> Conditional -> Manage)

ReferenceRow.ods

Please note:

• You wording repeat/instantly copy is incorrect in the sense that no formula ever copies anything to another cell, but references another cell, shows the content of another cell or performs calculations from the content of another cell.
• The numbers in the sample file in range B9:V32 are just random numbers, but could be replaced by any other data

Hope that helps.

If the answer is correct or helped you to solve your problem, please click the check mark () next to the answer.

[Chipper9979]

Hello,

Thank you very much, it did indeed help.

I wasn’t expecting you to go so far as to produce an example for me to look at. That’s excellent.

Thanks too for the correction, clarity is important. I wouldn’t want to cause confusion due to using the wrong wording.

[douglas49]

I have a similar but, I think, much simpler problem. I would like to be able to have a series of cells (say, C3-9) copy the value I type into a parent (reference) cell, say A1. In other words, when I type a value in A1 I would like that value to immediately reflect in all the other cells (in this instance, C3-9 - but it could be B4, B9, C4, D7). I hope this is clear. Thanks.

[anon73440385]

I have a similar but …

Then this answer is not an answer to your question as well. Please ask a new question.