Transferring data from a cell in Sheet2 to cell in Sheet1

[VIPds]

Thank you for taking the time to read this post. Let me start by apologizing if this post is redundant. I have searched the forum for similar questions but have yet to find one that specifically addresses my issue. I am an inexperienced Excel user, so the terminology is sometimes a bit beyond me.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
That being said, I would like to transfer data from a cell in Sheet2 to a cell in Sheet1 (same workbook). The data in Sheet2 will change daily, so I need the corresponding cell in Sheet1 to update accordingly.
<o></o>
For example, cell A6 in Sheet2 is data in dollar format (ie. $25.00). It is the sum of A7:A500. Sheet1 is basically a summary sheet. I need the data from A6/Sheet2 to update in A6/Sheet1. I will then do the same through column P.
<o></o>
I have tried simply “paste special” as well as several formulas I found searching these forums and the Internet. Nothing has worked as of yet. I’m sure this is a simple task for someone knowledgeable in Excel, but like I said, I am a newbie at best. I think some classes are in line.
<o></o>
Also, is there any way to find the sum through infinity? For example, instead of A7:A500 using A7:A∞. My column will eventually surpass 500 in length and I would prefer to not have to constantly update the formula.
<o></o>
Thanks again for taking the time to read this post. I look forward to any answers/support you can offer.

 

[robertpl]

THIS is a form of the same question that I have...EXCEPT the Paste Special command then Past Link works fine for me...transfers all the data and updates perfectly...EXCEPT for the cells in the original sheet that didnot contain data it puts a zero (0) in those fields (on the second sheet)...Is their a way NOT to have those zeros appear...I.e. if I copy range a1 to a5 and a3 is blank on the source sheet...how do I keep a3 blank on the second sheet...thank you so much

 

[BillP]

Just write a formula that references the cell on the other page. Type the = sign, then click the tab for the page you want to reference, then the cell on that page. Presto. =Sheet2!A6 Then copy the formula across to P6 on Sheet1

Check Help for 3-D formulas; there a couple of ways to do it. One consideration: If you're going to do this, name your sheets and don't put any spaces in the names--it'll make your life easier.

And, no, you cannot go to infinity. Excel only has a certain number of cells in a column (depending on which version). but you could certainly give it a longer range.

 

[robertpl]

Thank you Bill...BUT correct me if I am wrong....if I use your logic =Sheet2!A6 that will not work....I need to have the cells (or column)copied from one sheet to another...iI need a command whereby f updates are made to the original sheet that they are carried over to the second sheet AND those cells in the original sheet which are blank, not to have a value of 0 stated in the same cell in the second sheet...

 

[VIPds]

BillP,

Worked perfectly! Thank you so much.

 

[ljungren13]

Thank you Bill...BUT correct me if I am wrong....if I use your logic =Sheet2!A6 that will not work....I need to have the cells (or column)copied from one sheet to another...iI need a command whereby f updates are made to the original sheet that they are carried over to the second sheet AND those cells in the original sheet which are blank, not to have a value of 0 stated in the same cell in the second sheet...

use an if statement like this:

=if(Sheet1!A1="","",Sheet1!A1)

This just makes the cell empty if the cell it is pulling from is empty

 

[ljungren13]

Thank you for taking the time to read this post. Let me start by apologizing if this post is redundant. I have searched the forum for similar questions but have yet to find one that specifically addresses my issue. I am an inexperienced Excel user, so the terminology is sometimes a bit beyond me.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
That being said, I would like to transfer data from a cell in Sheet2 to a cell in Sheet1 (same workbook). The data in Sheet2 will change daily, so I need the corresponding cell in Sheet1 to update accordingly.
<o></o>
For example, cell A6 in Sheet2 is data in dollar format (ie. $25.00). It is the sum of A7:A500. Sheet1 is basically a summary sheet. I need the data from A6/Sheet2 to update in A6/Sheet1. I will then do the same through column P.
<o></o>
I have tried simply “paste special” as well as several formulas I found searching these forums and the Internet. Nothing has worked as of yet. I’m sure this is a simple task for someone knowledgeable in Excel, but like I said, I am a newbie at best. I think some classes are in line.
<o></o>
Also, is there any way to find the sum through infinity? For example, instead of A7:A500 using A7:A∞. My column will eventually surpass 500 in length and I would prefer to not have to constantly update the formula.
<o></o>
Thanks again for taking the time to read this post. I look forward to any answers/support you can offer.

you can find a sum through an entire column which might be better than using a very large number

=SUM(A:A)

this would sum the entire A column

 

[BillP]

Robertpl: I wasn't answering your post, I was answering the first post, and it was just what VIPds needed.

Your question had nothing to do with the post you tagged on to. It should have been a separate post, since the problems are completely different.