Office 2013 Excel question

[setishock]

I needed a version of Office to put on my new rig and with the Office 2010 starter no longer available, I bought Office 2013. I'll get to the point.

I'm dinking around figuring out how to make formulas for spread sheets and even figured out (finally) how to get it to save them. I have 2 sheets I use for work of my own making but would like to do something different.
One sheet is my daily report. The other is a running total of the tax deposits. What I'd like to do is have the daily tax entry on the daily work sheet also appear on the tax running total sheet.
I know I can add the running total on the work sheet but I think it might be confusing for the accountant at the home office. Besides cluttering up my daily work sheet.
Any way to do this?

 

[jmacavali]

Could you explain a little better what you want. Without seeing the two worksheets it's a bit confusing.
Daily Report - Sheet1
Tax Deposits - Sheet2

You want a total from Sheet1 to show up on Sheet2?

I think what is confusing the most is the last sentence. Up to that point I thought you wanted something from Sheet1 on Sheet2, but when you say 'clutter up my daily work sheet (which I've labled sheet1)' it seems backwards to me....

 

[strollin]

First of all, there are workbooks that contain multiple worksheets. Are the 2 worksheets part of the same Workbook or in different workbooks?

If they are in the same workbook then it's extremely easy to reference a cell in another worksheet, just use the reference format of 'Sheet2'!A1 where 'Sheet2' is the name of the sheet you want to reference and A1 is the cell you want to reference on that sheet.

If the data you want to access is in another workbook, then you need to create what's called an external link. Here's an article that explains it: Create an external reference (link) to a cell range in another workbook - Excel - Office.com

 

[setishock]

I want to take cell xx data on sheet 1 and have it appear in cell yy on sheet 2,

No they are not in the same workbook.

 

[jmacavali]

The link from strollin is what you want. To paraphrase from the link:

Open the workbook that will contain the external reference (the destination workbook) and the workbook that contains the data that you want to link to (the source workbook).
1. Select the cell in the destination worksheet where you want to create the external reference and type = (equal sign).
2. Switch to the source workbook and select the cell that you want to link to.
3. Return to the destination workbook, and notice that Excel adds the reference to the source workbook and the cell that you selected in the previous step.
​

That should do it. Just played around with it and it works well. Updates properly and everything. Not sure what will happen though if you want to email the files to someone else and it opens and looks for the reference to the other workbook and can't find it. You could solve that by right before sending it off, going into that one cell, copying the data, right click and do a paste but choose 'value' instead of just paste. It will then stop referencing the other workbook and just have the value in there (you'll of course have to reset up the formula though).

 

[setishock]

I'll scope it out. Thanks guys.

 

[strollin]

I tested what happens when you email the sheet. When you open the workbook, excel will report that it contains links to other workbooks and gives you the choice of continuing with the current data (last value entered on external worksheet) or updating the links. If you send both workbooks then the user could update the link to point to the location where the 2nd workbook is saved.