Disclaimer: I am not employed in the use of Excel, retired and testing the stock market waters. I'm experienced in Excel but nowhere close to trained, I can kludge and test my way through problems but would rather create and hide columns rather than nest functions.

My issue is that I have a workbook that I concocted to track stocks, each sheet works as intended to track stock name and daily gains/loss and is combined on sheet 1, "analysis". If a stock is sold I move the stock sheet to another workbook, clear my inputs on the original workbook, and move it to the end of the original workbook for something else.

My problem is that sheet 1 tracks the move and turns the formula '2'!$F$1 into 'z'!$F$$1 when I would like the results of the current '2'!$F$1. My first thought was $'2'!$F$1 but that's a bust. I use sheet 1 to print out stocks currently held but the gaps in the column tend to annoy me.

Any suggestions?

Can you do a cut / paste data rather than a move.

No, if '2'!$F$1 is empty it shows 0 in the cell on sheet 1 referencing it but... thanks for the insight, might be a good idea to sort on the column I'm looking at on sheet 1.

Lotus 1-2-3 and dBase III are my style, thanks!

I'm a super expert in Excel but I cannot quite follow what you want to do and the error that arises.

here's what I understand:
You have two Excel files. In one file, you have sheets for each stock and you pull the data for each stock / sheet into one summary sheet in the same file (workbook).

When you sell a stock, you move the sheet from the first workbook to a second workbook. But what happens is the summary page on the first workbook still keeps track of the sheet that was moved to the second workbook.

The easiest solution without really understanding or improving your process is this:
If you don't know how to do this, let me know. But you need to tell me what Excel version you're using (2003, 2007, 2010, or 2016).

I'm assuming that to move the sheet, you right click on the sheet tab then move it to the second workbook, yes?

In the dialog box to move the sheet, click the box in the lower left corner that says "Create a copy." Then the formulas on the summary sheet will still refer to the original sheet. You can do what you want with this original sheet that remains in the first workbook. If you delete the sheet, then the summary sheet will return #N/A.

If howevery you have formulas in the stock sheet, then the sheet in the second workbook will continue to refer to the first workbook. But I don't think you do have formulas in the sheet referring to other sheets, right?

If you do, let me know.

A second alternative is every time you want to move a sheet, then create a new sheet in the second workbook. Copy the whole sheet in the first workbook. You do this by selecting the all the cells by clicking on the upper left corner bordering th column letters and row numbers. Then Ctrl+c then click in cell A1 in the new sheet in the second workbook and pasting (Ctrl+v). But this will work only if the formulas in the sheet do not refer to other sheets in the first workbook.

You might want INDIRECT()

A quick example. I have a two sheets Sheet1 and Sheet2. Sheet2!A1 contains "this is one", Sheet2!A2 contains "this is two".

Sheet1!A1 contains

=INDIRECT("Sheet2!A1")

And it displays: "this is one" in Sheet1 cell A1

Note the quotes. Indirect takes a string and converts it to a location

If I delete Column 1 in Sheet 2 - and select shift left. A1 in Sheet 1 changes to "this is two". So indirect will display whatever in in Sheet2A! regardless of how it gets there or is moved around.

If I use =Sheet2!$A$1 in Sheet1 A1 it will return a REF error if I delete column 1 in Sheet 2.

Hope this is clear.

Another way would be to use indexing. I find it harder to follow - but it will run faster. For this example

=INDEX(Sheet2!$1:$1048576,1,1)

Would go in Cell A1, Sheet1.

HRH and Chance, sorry for the delay but thank you both.

I happened upon the indirect function a few hours ago and thought this may solve my problem but I have other things going on at the moment.

I probably complicated my initial question, I just need for the formula in the "analysis" sheet to reference a worksheet by sheet name rather than the sheet's position in the workbook, I think indirect() will do this.

quote:

Originally posted by Alpine79830:
HRH and Chance, sorry for the delay but thank you both.

I happened upon the indirect function a few hours ago and thought this may solve my problem but I have other things going on at the moment.

I probably complicated my initial question, I just need for the formula in the "analysis" sheet to reference a worksheet by sheet name rather than the sheet's position in the workbook, I think indirect() will do this.

Glad you got it figure out.