copy & paste using a formula ?

[Gene Holmes]

I am trying to do something that I can find no reference to!

1. I created a section on a sheet (reference area/list/table) with
prefilled data. Say 10 c wide x 10 r high .
2. Each row is numbered ascending.
3. Each row is "named" as a range.
4. I have another section of the workbook where I wish to "copy a range"
into. This section will be the same 10 x 10.

I wish to input a number into a cell, (1-10) this # would reference a row of
the data section. I need the program to "copy" the cell range to the new
location.

over simplified formula:

=IF('Material'!F2=1,"copy(H34:M34), paste 'Part Info'!D14:J14",
=IF('Material'!F2=2,"copy(H35:M35), paste 'Part Info'!D14:J14",
=IF('Material'!F2=3,"copy(H36:M36), paste 'Part Info'!D14:J14",
=IF('Material'!F2=4,"copy(H37:M37), paste 'Part Info'!D14:J14",

Am I missing something here?

 

[Bernie Deitrick]

Gene,

Well, except for your example trying to copy 6 columns (H to M) into 7
columns (D to J), it's pretty easy.

For this solution, I'm assuming you want 7 columns over to 7 columns.

In cell D14 on Sheet "Part Info" enter this formula
=INDEX(Material!$H$34:$N$43,Material!$F$1,COLUMN(D14)-3)
and copy to E14:J14.

HTH,
Bernie
MS Excel MVP

 

[G Holmes]

I am not explaining my problem well.
Let's try again, lets say that in cell (A1) I choose to input the number 1,
by putting #1 in this cell I want the range contents of (H34:M34) to be
copied to 'Part Info'!D14:I14", or lets say that in cell (A1) I choose to
put the number 2, by putting #2 in this cell I want the range contents of
(H35:M35) to be copied to 'Part Info'!D14:I14. Basically I need a different
cell range copied to a specific range location based on which number I enter
into cell A1.

=IF A1= #1, "copy(H34:M34) cell content, and paste into
'Part Info'!D14:I14",
=IF A1= #2, "copy(H35:M35) cell content, and paste into
'Part Info'!D14:I14",
=IF A1= #3, "copy(H36:M36) cell content, and paste into
'Part Info'!D14:I14",
=IF A1= #4, "copy(H37:M37) cell content, and paste into
'Part Info'!D14:I14",

Thanks
Gene

 

[Bernie Deitrick]

Gene,

I guess I didn't explain my solution well.

You don't need to copy and paste: you can extract the information of
interest using formulas, and specifically, using the Index function. Your
'pseudo' code showed that you wanted to change the values of D14:J14 on
sheet "Part Info" based on the value in cell F2 on sheet "Material",
extracting different rows from range H34:N43, also on sheet "Material". My
formula does just that (though I inadvertently used F1 rather than F2.)

Did you try the formula solution?

In cell D14 on Sheet "Part Info" enter this formula
=INDEX(Material!$H$34:$N$43,Material!$F$2,COLUMN(D14)-3)
and copy it to cells E14:J14.

If you want to link those cell values to a different cell, say A1, simply
change the $F$2 to $A$1.

HTH,
Bernie
MS Excel MVP

 

[Ken Wright]

You cannot 'copy and paste' using formulas. A formula can only 'pull' a
values(s) into a cell(s), based on whatever parameters you put into the formula.
You also cannot 'push' a value into another cell using a formula. Only VBA or
macros can do that, but that suffers from being a one time operation that will
not be updated unless you rerun the macro. What Bernie has given you is a
formula that will (apart from not actually *pasting* the data in), give you in
your destination cells, exactly the data you had wanted.

 

[G Holmes]

Thanks Bernie,
I haven"t used Offset or Index much so the solution took me a while to soak
in.
I decided to change my sheet layout around so as not to use different sheet
ref's in formulas (more user friendly). I ended up using your solution as a
guide to do what I was looking for and came up with something that was
similar but different. My first cell formula ended up like this
=IF($J2>0,INDEX($K$34:$O$37,$J2,1)," ") I had to put in the IF so I didn't
get a REF/VALUE return if the data cell was empty. Strange thing, as I
copied this formula across from K2:O2 (destination range) I manually had to
go in and set the column ref ?? Column didn't increment ? This normal ? Or
is something about this formula not completely correct, but it seems to work
fine after correction ? Copying down worked fine.

I also accomplished this by using multiple IF statements, but it would not
work well with more than a couple choices. Your solution is better for a
larger range of choices.

My data input range is now K34:O37
My destination range is now K2:O27
My Input # range is J2:J27

Thanks Again
Gene

 

[G Holmes]

Boy am I brain dead......
All is OK now, I didn't realize what I did wrong with the COLUMN references.

Thanks again
Gene