Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Replacing

Sep 3, 2004 2:01AM PDT

I have an Excel workbook containing a number of "analysis" worksheets. These "analysis" worksheets extensively reference a "data" worksheet containing daily closing stock prices. Is there some way to replace the entire "data" worksheet in the workbook each day rather than manually changing all of the stock prices?

I've tried, but when I rename the old "data" worksheet, the formulas in the "analysis" worksheets change as well. When I add the new "data" worksheet, I can't figure out an easy way to get the formulas to reference the new worksheet. Any suggestions?

Discussion is locked

- Collapse -
Re: Replacing sheet names in formulas
Sep 3, 2004 2:53AM PDT

I would think that the command Edit>Find would work.

Keep your same worksheet names, just search and replace the old sheet name with the new one. Once you are sure that all the references to the old sheet are replaced, and all things on that sheet are no longer needed, then (if you want to) just delete the old sheet.

In the "Find and Replace" window, on the Find tab, just enter the name of the 'old sheet' and on the Replace tab enter 'new sheet' (or whatever you called it) in the "Replace with" field.

For the searching options select "Look in:" Formulas.

I believe that will work. I just tried it for cell addresses within formulas, and that worked, so I would think it should also work for sheet names.

- Collapse -
Re: Replacing sheet names in formulas
Sep 4, 2004 12:13AM PDT

Chuck, thanks for your input. Though your solution works well with any one of the "analysis" worksheets, there are actually dozens of them. This makes your approach a bit cumbersome. Any ideas?

- Collapse -
(NT) (NT) Nope. No ideas, except Kee's suggestion, make a macro
Sep 4, 2004 4:43AM PDT
- Collapse -
Re: (NT) Nope. No ideas, except Kee's suggestion, make a mac
Sep 7, 2004 2:34AM PDT

Chuck, thanks for your help. I'm going to explore Kee's macro suggestion to automate your formula edit suggestion. Thanks, again, for your help.

- Collapse -
Or ...
Sep 3, 2004 6:03AM PDT

another way to do it, if you don't like ChuckT's excellent method (which you can automate with a macro, of course).

Add a new worksheet to your workbook on which you 'download' or 'refresh' somehow the daily data. The lay-out should be fixed, of course. And you must have an easier way to do that than typing in every number, of course, or you could that just as well on the current data sheet.
This should be a totally independent worksheet, not linked to any of the other. Then you can do with it what you want.

Then add a button to the 'data' page. It should contain code to copy the contents of your new page to your 'data' page, which is an easy task. The 'analysis' pages are automatically refreshed, although it would be faster to disable automatic recalculation during the data copy, then do 'manual' recalculation (like f9) of all the 'analysis' pages in the macro, then enable automatic recalculation again.

Kees

- Collapse -
Re: Or ...
Sep 4, 2004 12:24AM PDT

Kees, I like your idea, and it may work, but I'm not sure. The columns in the "data" and "download" worksheets are fixed, but the number of rows can vary. Each row represents a stock and its daily closing price. Stocks (rows) are added and deleted as stocks are bought and sold. Does this mean that the "button" code on the "data" worksheet needs to be changed everytime a stock is bought or sold?

- Collapse -
Re: Or ...
Sep 4, 2004 5:17AM PDT

Not so much changed, as being made smarter, maybe. The code could loop through the filled part of the 'fill-in' sheet to fill the 'data' sheet. No need to code on which row to end, if that can be determined dynamically (each time). In fact, it can be made as smart as you want it to be. For example, only make rows with today's price for the stocks you have, and let the macro (a) add these rows to the 'data' sheet, and (b) delete rows from the 'data sheet' for stocks not on the 'fill-in' form any more. If that's what you want. Remember that programming is a one-time investment in coding, while potential time-saving is repeated daily.

But, of course, there's nothing wrong with writing a macro that automates ChuckT's original suggestion (formula edit) on each of the 'analysis' sheets. If you can do it once, it's easy to loop through all sheets (which are a 'collection', I think).
I find it unconventional (so I never should have thought of it myself), but certainly effective. I'm a classic programmer, you see, and for me, code is static and data variable and to be moved around. Modern programmers generate code, and so have a more dynamic view of it. Getting old, I think.

Kees

- Collapse -
Re: Or ...
Sep 7, 2004 2:30AM PDT

Kees, thanks for all of your help. I'm afraid your programming approach is a bit beyond my capabilities. I think I'm going to explore your macro suggestion to automate ChuckT's (formula edit) suggestion. It may not be the ultimate solution, but it's one that I think I can manage. Thanks, again, for your help.