Previous Topic Next Topic

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
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://tinypic.com/images/goodbye.jpg

Any help?

Thank you

System Configuration: Windows / Chrome 61.0.3163.79

6 responses

Answer 1 / 6
Blocked Profile
Updated on Jan 4, 2019 at 02:23 PM
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!


3
kekserica Posts 14 Registration date Wednesday January 2, 2019 Status Member Last seen January 4, 2019
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 :-)
0
kekserica Posts 14 Registration date Wednesday January 2, 2019 Status Member Last seen January 4, 2019
Updated on Jan 4, 2019 at 03:42 PM
here:

A1=sheet2!a1+c8+g3

http://tinypic.com/images/goodbye.jpg

all the best...
0
Blocked Profile
Jan 4, 2019 at 04:46 PM
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?
0
kekserica Posts 14 Registration date Wednesday January 2, 2019 Status Member Last seen January 4, 2019
Jan 4, 2019 at 05:13 PM
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"...
0
Blocked Profile
Updated on Jan 4, 2019 at 05:31 PM
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!
0
Answer 2 / 6
Blocked Profile
Updated on Jan 2, 2019 at 06:31 PM
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!


2
Answer 3 / 6
kekserica Posts 14 Registration date Wednesday January 2, 2019 Status Member Last seen January 4, 2019
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
0
Blocked Profile
Jan 3, 2019 at 01:04 PM
so place sheet2! in formula
0
Blocked Profile
Jan 3, 2019 at 03:24 PM
Copy is a relative action.

Take a look here for an explanation.
0
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
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
0
Answer 4 / 6
Blocked Profile
Updated on Jan 4, 2019 at 01:00 PM
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.

0
kekserica Posts 14 Registration date Wednesday January 2, 2019 Status Member Last seen January 4, 2019
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
0
Blocked Profile
Jan 3, 2019 at 05:05 PM
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!
0
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
Relative or absolute...If I cut cell A1, I will get empty A1 cell, so this is not the solutions.
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 6
Blocked Profile
Jan 4, 2019 at 10:06 AM

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!
0
kekserica Posts 14 Registration date Wednesday January 2, 2019 Status Member Last seen January 4, 2019
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
0
Blocked Profile
Jan 4, 2019 at 12:10 PM
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!
0
kekserica Posts 14 Registration date Wednesday January 2, 2019 Status Member Last seen January 4, 2019
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
0
Blocked Profile
Updated on Jan 4, 2019 at 12:47 PM
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?
0
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
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...
0
Answer 6 / 6
Blocked Profile
Updated on Jan 4, 2019 at 12:58 PM
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?


0
kekserica Posts 14 Registration date Wednesday January 2, 2019 Status Member Last seen January 4, 2019
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
0
Blocked Profile
Updated on Jan 4, 2019 at 01:11 PM
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?
0
kekserica Posts 14 Registration date Wednesday January 2, 2019 Status Member Last seen January 4, 2019
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...
0