Problem with copy in one sheet
Solved/Closed
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Status
Member
Last seen
January 4, 2019
-
Updated on Jan 3, 2019 at 08:43 AM
Blocked Profile - Jan 4, 2019 at 05:18 PM
Blocked Profile - Jan 4, 2019 at 05:18 PM
Related:
- Problem with copy in one sheet
- Little alchemy cheat sheet - Guide
- Google sheet right to left - Guide
- Mark sheet in excel - Guide
- Cmd network commands cheat sheet - Guide
- How to copy data from one sheet to another in excel automatically - Guide
6 responses
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!
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!
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!
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!
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Status
Member
Last seen
January 4, 2019
Jan 3, 2019 at 02:43 AM
Jan 3, 2019 at 02:43 AM
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
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
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Status
Member
Last seen
January 4, 2019
>
Blocked Profile
Jan 3, 2019 at 02:18 PM
Jan 3, 2019 at 02:18 PM
Where?
Please look at this, I take screenshots:
https://cdn1.imggmi.com/uploads/2019/1/3/e4633c410ca8337a80697478f8fa975f-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/2938608734cc9db2b7f8e334221e2bb1-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/ea8a12282122a22fe992ddc2770b0320-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/80081d3a2a77889fce48230a6dbfa760-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/8b7296f43502e3ef99e1e4c57b93f215-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/034298be4141e7e128a908f16ff86b26-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/b34ed58433724da4d381ab6bc541b695-full.jpg
Problem is when I copy cells: sheet1 A1-A5 to sheet1 A7-A11...
Thank you
Please look at this, I take screenshots:
https://cdn1.imggmi.com/uploads/2019/1/3/e4633c410ca8337a80697478f8fa975f-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/2938608734cc9db2b7f8e334221e2bb1-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/ea8a12282122a22fe992ddc2770b0320-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/80081d3a2a77889fce48230a6dbfa760-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/8b7296f43502e3ef99e1e4c57b93f215-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/034298be4141e7e128a908f16ff86b26-full.jpg
https://cdn1.imggmi.com/uploads/2019/1/3/b34ed58433724da4d381ab6bc541b695-full.jpg
Problem is when I copy cells: sheet1 A1-A5 to sheet1 A7-A11...
Thank you
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Status
Member
Last seen
January 4, 2019
>
Blocked Profile
Jan 3, 2019 at 03:34 PM
Jan 3, 2019 at 03:34 PM
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
Formulas, values, formulas and number formats, values and number formats...
Here is file:
https://ufile.io/hyjba
What I'm doing wrong?
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.
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
Status
Member
Last seen
January 4, 2019
Jan 3, 2019 at 03:53 PM
Jan 3, 2019 at 03:53 PM
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
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
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Status
Member
Last seen
January 4, 2019
>
Blocked Profile
Jan 3, 2019 at 05:11 PM
Jan 3, 2019 at 05:11 PM
Relative or absolute...If I cut cell A1, I will get empty A1 cell, so this is not the solutions.
Didn't find the answer you are looking for?
Ask a question
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
Status
Member
Last seen
January 4, 2019
Jan 4, 2019 at 11:25 AM
Jan 4, 2019 at 11:25 AM
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
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
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!
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
Status
Member
Last seen
January 4, 2019
Jan 4, 2019 at 12:36 PM
Jan 4, 2019 at 12:36 PM
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
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
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Status
Member
Last seen
January 4, 2019
>
Blocked Profile
Jan 4, 2019 at 12:49 PM
Jan 4, 2019 at 12:49 PM
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...
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...
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?
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
Status
Member
Last seen
January 4, 2019
Jan 4, 2019 at 01:04 PM
Jan 4, 2019 at 01:04 PM
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
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
kekserica
Posts
14
Registration date
Wednesday January 2, 2019
Status
Member
Last seen
January 4, 2019
Jan 4, 2019 at 01:39 PM
Jan 4, 2019 at 01:39 PM
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...
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...
Jan 4, 2019 at 03:34 PM
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 :-)
Updated on Jan 4, 2019 at 03:42 PM
A1=sheet2!a1+c8+g3
http://tinypic.com/images/goodbye.jpg
all the best...
Jan 4, 2019 at 04:46 PM
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?
Jan 4, 2019 at 05:13 PM
"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"...
Updated on Jan 4, 2019 at 05:31 PM
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!