Problem with copy in one sheet [Solved/Closed]

Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
-
Hello,

There is a table on sheet1 for example.

A B C D E
1
2
3
4
5

In cell A1 I drag the data from sheet2 cell A10
In cell A2 I drag the data from sheet2 cell A20

I need 100 same tables on the sheet 1, one below the other, now there is a problem, when I copy the table to cell A7 on the sheet1 in this cell A7 is formula from A1 moved to 6 places (difference inside the sheet1), and the data from sheet2 with cell A16 instead of cell A20, also in A8 cell is data from sheet2 cell A26 instead of cell A30.

http://i65.tinypic.com/iqlijq.jpg

Any help?

Thank you

System Configuration: Windows / Chrome 61.0.3163.79
See more 

6 replies

Best answer
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877
3
Thank you
Is this an export of some other Report or something?
Who is building the tables in this manner?

Your description is still lacking details, and still makes no sense.

Your table dimensions are not comparable, and are not equal as you stated what you are trying:
"when I copy A1:E5 to A8:E12
not ok because first table on sheet2 has range A1:J10 "


A1:E5 is not the same dimensions as A1:J10.

So no wonder they won't match up when trying to copy them and they over run other data. Have your secretary not build the sheet in this layout.

That is the best advice I can give you!


Say "Thank you" 3

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 4491 users have said thank you to us this month

kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
-
:-)

Of course that dimensions of tables not comparable, that's the problem from beginning.
Cells in tables on sheet1 is sum of different values from cells in tables on sheet2.

For example: one cell on sheet1 = sum of different cells on sheet2, so it is logical that are not comparable.

In meanwhile I got the information from one excel expert that there is no way to get those kind of references between different sheets just by formulas, I need VBA, Macro.

Thank you for your patience one more time :-)
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
-
here:

A1=sheet2!a1+c8+g3

http://oi67.tinypic.com/2lo517c.jpg

all the best...
ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877 -
Yea, I am glad the GURU told you need a MACRO (no kidding)!

If you have set up the sum of different cells on sheet2, then why not just make the final calculation where you want it?

Did you unlock the developer tab yet?
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
-
hahaha, why are you nervous, I said that "GURU" (hahaha) told "there is no way to get those kind of references between different sheets JUST BY FORMULAS" this is answer on my question "is it possible", of course that I know Macro is solution from beginning, but I don't know to work with it, so I try to find solution in this away...

"If you have set up the sum of different cells on sheet2, then why not just make the final calculation where you want it? "

there is no problem with that, problem is when I that final calculation (on sheet1) copy, in that moment everything mix up, just like you said "dimensions of tables not comparable"...
ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877 -
Nervous, You are funny! :) I thought I was helping a data analyst perform their job!

Welcome to Excel, there are sometimes hidden and wasted cells that do nothing but calculate, and are never ever presented to the user! But.... what do I know? Perhaps you need to rethink your "table" structure, and build your data to a more manageable, and a scheme that can be scaled!


Original Scope:
"I need 100 same tables on the sheet 1, one below the other, now there is a problem, when I copy the table to cell A7 on the sheet1 in this cell A7 is formula from A1 moved to 6 places (difference inside the sheet1), and the data from sheet2 with cell A16 instead of cell A20, also in A8 cell is data from sheet2 cell A26 instead of cell A30."

So in this original scope, where did you state "is it possible"?

This is a rhetorical question, I know the answer!

I told you your request was flawed from post one!

Have fun!
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877
2
Thank you
There is a flaw to your request: "I need 100 same tables on the sheet 1"

Each sheet is a table. You have not defined the table size in order to properly place all 100 tables on the same sheet!

Please understand, Excel has RELATIVE and ABSOLUTE values. Sometimes you might build a formula with RELATIVE VALUES such as in the below example:
=A2+B2

By default, a cell reference is a relative reference, which means that the reference is relative to the location of the cell.

Other times you will have the need to build an ABSOLUTE one, as in:

=$A$2+$B$2 Example.


Get a grasp of that, then let us know if you have any further questions.

Sometimes it is easier to make a programmer a manager, than a manager a programmer!



Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
0
Thank you
Hm, so I need relative cell range between sheets in same workbook.
I try all options with $ but it still doesn't work, error, ref etc...
Every time I copy cell from a1 to a7 in sheet1, relative cell range is from sheet1 but I need from sheet2...

thank you
ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877 -
Copy is a relative action.

Take a look here for an explanation.
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
> ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
-
Yes, but I did every options that paste special give me and it still doesn't work.
Formulas, values, formulas and number formats, values and number formats...
Here is file:

https://ufile.io/hyjba

What I'm doing wrong?

thank you
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877
0
Thank you
Here is how to MOVE a formula:
Select the cell that contains the formula that you want to move.

You can also move formulas by dragging the border of the selected cell to the upper-left cell of the paste area. This will replace any existing data.

Do one of the following:

To paste the formula and any formatting: In the Clipboard group of the Home tab, click Paste.

To paste the formula only: In the Clipboard group of the Home tab, click Paste, click Paste Special, and then click Formulas.

kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
-
But if I MOVE then in cellA1 I have nothing!
I don't want to have nothing!

I want:

in sheet1!cellA1 to have value from sheet2!cellA10
in sheet1!cellA2 to have value from sheet2!cellA20
in sheet1!cellA7 to have value from sheet2!cellA30
in sheet1!cellA8 to have value from sheet2!cellA40

I don't understand what you don't understand :-)

thank you
ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877 -
YOU ARE USING RELATIVE! YOU NEED ABSOLUTE!

After you cut the formula, use PASTE SPECIAL-paste LINK. See if that will build your absolute value for you!

Have FUN!
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
> ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
-
Relative or absolute...If I cut cell A1, I will get empty A1 cell, so this is not the solutions.
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877
0
Thank you

Take a look at the above picture. See how Cell A5 is pointing to A1, that is an ABSOLUTE path.



Now, if we copy A1 of Sheet1, and select Sheet2, select a cell, and right click and select PASTE SPECIAL, you will see choices.


From these choices, you can select VALUE, or PASTE LINK. If you place LINK, then it builds an ABSOLUTE PATH to your cut. Take note of the formula bar in the following image:


That is an ABSOLUTE PATH TO YOUR TARGET!

Have FUN!
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
-
Thank you for your patience, but how this solve my problem?
Now in your example I need to copy B3 on sheet2 to B5 on sheet2, and what I get?
Same thing, I will get value from A1 from sheet1.
But I need (for example) A8 from sheet1.
So, my question is:

How to get(in your example) to copy B3 on sheet2 to B5 on sheet2 and the value on sheet2!B5 is from sheet1!A8?
And of course value on sheet2!B3 is from sheet1!A1.
How to get that?

thank you
ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877 -
I explained it in my post. PASTE SPECIAL - PASTE LINK!

I am not providing you the complete solution, but a PARADIGM (MODEL) for you to apply to your situation! I am trying to teach you, not just give you a cut and paste to use and not completely understand what it is doing!

To answer your last question:
Q: "How to get to copy B3 on sheet2 to B5 on sheet2 and the value on sheet2!B5 is from sheet1!A8? "
A: "In sheet 2 B5 place =Sheet1!A8"


From the beginning I stated that you needed to know the differences in RELATIVE and ABSOLUTE. Did you read the MS article on the differences?

Never play chess with a pigeon. It will just crap on the chess board, and strut off like it won the game!

Have FUN!
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
-
You really don't understand or what?

To answer your last question:
Q: "How to get to copy B3 on sheet2 to B5 on sheet2 and the value on sheet2!B5 is from sheet1!A8? "
A: "In sheet 2 B5 place =Sheet1!A8"


That's your answer?
Wow...
Again, I know that I can just write "Sheet1!A8" but I don't want to, because I have a hundreds of tables, that's the point, how to copy and get value from different range from different sheet.

PASTE SPECIAL - PASTE LINK DOESN'T WORK, TRY!!!

Let me ask you one question:

do you in your example above copy B3 on sheet2 to B5 on sheet2, and what you get?

sheet1!A8 ?

I don't think so...

It is the simpliest way just write sheet1!A8 in sheet2!B5, but this is not the solutions.

Just try, be free to post screenshot if you succeed.

thank you
ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877 -
COPY is a relative action by default. So if you keep trying to cut and paste, you need paste special. What version of excel are you using?
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
> ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
-
2003.

but again, I can't CUT because I need that cell what you want to cut.
I need that cell to stay there so I can't cut I can only copy...
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877
0
Thank you
I am not saying CUT, I am SAYING USE PASTE SPECIAL!

Q: "do you in your example above copy B3 on sheet2 to B5 on sheet2, and what you get? "
A: " This is A1" (See images)

Cell A1 on Sheet1 (above)


Cell B3 on Sheet2 (above)


Cell B5 on Sheet2 (above)

I think I have covered it.


"You really don't understand or what?"

I am offended, sir or madam!

Are you even reading what I have referenced?


kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
-
Ok, now copy in your example on sheet2 range B2:B5 to cell B10, what you get?

Same thing what you have in B2:B5, same thing!

That's what I try to avoid.

When you copy B2:B5 to B10, in this cell B10 I want to have this:

B10=sheet1!A25

or

B10=sheet1!A29

or

B10=sheet1!A40

or every cell that I decide to choose...

That is what I need...

Thank you
ac3mark
Posts
11176
Registration date
Monday June 3, 2013
Status
Contributor
Last seen
January 18, 2019
1877 -
You are right, I get B2:B5, because this is what I COPIED!


How is B10 going to turn into Sheet1!A25 if I just copied it from B2:B5? This makes no sense!

Is this homework or something?
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Last seen
January 4, 2019
-
Uh, for example in sheet1 I have table with range A1:E5, all cells in this range drag values from table on sheet2, that table has the range A1:J10.

All that is not the problem if you have only one table on sheet1 and one table on sheet2.
Or 2 tables, or 3 tables...
But if you have 100 tables you need to copy first table A1:E5 with all values in it.

I just NEED to copy.

cells on sheet1:

A1=sheet2!A1 -> ok
A2=sheet2!A7 -> ok
etc...

when I copy A1:E5 to A8:E12 I get this:

A8=sheet2!A7 -> not ok
A9=sheet2!A14 -> not ok

not ok because first table on sheet2 has range A1:J10 so second table on first sheet grab values from first table on second sheet.

With A8 on first sheet start second table and that table must drag value from sheet2!A11, first cell of second table on sheet2

sheet1 sheet2
table1 -> table1
table2 -> table2
table3 -> table3
etc...

now I have:

sheet1 sheet2
table1 -> table1
table2 -> still table1
table3 -> table2
etc...