Will This Work

[rockyw]

I asked how I could have on sheet 2 formulas to sheet one like =Sheet1!A1. Then I need to make a new sheet for the next day. I didn't want to make all those fomulas again. Can I rename sheet one, then when I copy and paste the next sheet do a FIND 1, replace with 2. Then all my formulas would move one row down? That make any sense? Is there a better way. The other post replys were very confusing to me, I am learning but I don't understand a lot of excel 2007 areas. Thanks

 

[CWatts]

Yes you can and if you copy the sheet before renaming, you wont have to make any changes.

However. once you make changes to your source sheet, your OTHER sheet will also change, so you'd have to do a select all, copy, and paste special > values to nullify all formulas.

 

[rockyw]

So like now I renames sheet one DataFeed. In sheet 2 I have in D4 =DataFeed!D1, So I copy the whole sheet, paste that into sheet 3, then find 1 and replace q with 2. So then sheet 3 d4 will have =DataFeed!D2. Is that correct? Is there a better way? Thanks

 

[CWatts]

Let's start out with 2 sheets: DataFeed and DateTemplate

DataFeed will always be changing and you want to keep a record for each time data feed is updated. Your DateTemplate sheet links back to DataFeed.

If you right click on the DataTemplate sheet tab at the bottom, and click "Move or Copy...". you can then choose "Move to end" and put the check in "Create a Copy".

You'll then have a sheet titled "DateTemplate (2)" which is identical to DateTemplate.

Select all values in DateTemplate (2) by clicking the master cell (the blank area above Row A and left of Column 1) to highlight everything. Copy it all, (CTRL-C) and then paste special and choose values. That will break all of the references to your DataFeed and put just the values in there. Then rename the sheet by double clicking the tab name below.

Now when you input new data in DataFeed, DateTemplate will update, and you can repeat the process.

 

[CWatts]

Also, if this is exactly what you want done, this can be made into a macro to do it with just one click.

 

[rockyw]

I see what that does, but I would like the workbook to have all the sheets needed for a month named as the dates, like 3/22/11. Sheet 1, Data Feed will have new data each day but the new values will be one row down each day. Today maybe A15:L15, tomorrow A16:L16 and so on. I would like to prep the workbook for the month so each day would just update to the correct sheet. Would the find process be better for that. Once I get sheet 2, named 3/22/11 working with formulas, I would like to creat all the rest of the month from that without a lot of work re doing formulas. Sorry if this is hard to follow. Thanks