Copy full page of formulas linked to specific workbook to a new book

[Sourpickle]

Hi.
I'm having an issue with a sheet of formulas that i made in (let's say) Sheet 2! based on the information in Sheet 1!. Now i want to copy this whole sheet 2! as is into a new sheet of information, Sheet 2! shall do the same calculations based on sheet 1! info from the 'current' workbook.
So far i have to manually redo all formulas to link them to the info of sheet 1! of the new sheet.
Is there a way to tell the formula to always refer to "SHEET 1" of the CURRENT book?
Thanks in advance!!!

[easyexcelanswers]

I personally run into the same problem myself. I have all kinds of formulas on one sheet and would like to use the same formulas on another sheet.

What I have done, is I always have a Notebook file open. You are able to copy the formulas from you excel sheet to a Notebook file and then copy them from the notebook to the next excel file without creating weird and wonderful references.

[Sourpickle]

Thanks so much for your help.
My problem is that the excel file is more than 30MB large. And every time i paste in or modify any formula it takes over 3 minutes for the change to take effect.
What would help is a certain way to tell him in the formula 'take this workbook name as a reference'....

[Tony Valko]

Now i want to copy this whole sheet 2! as is into a new sheet of information

Can't you just create a copy of the sheet then rename it?

Select the sheet in question
Right click the sheet tab>Move or copy...
Check: Create a copy
OK

[Sourpickle]

Can't you just create a copy of the sheet then rename it?

Do you mean 'Copy the workbook & put in the new info in sheet 1!"?

I need it for a certain report based on bi-weekly sales, it's new info every period...

[Tony Valko]

In other words...

You want to use this same sheet in a different file?

Maybe this...

Create a copy as I suggested in my other reply.

With the sheet still in its original file...

Press the key combo of CTRL H. This will open the Find/Replace userform.
Find what: =
Replace with: some character that will be unique that is not already in any cells on the sheet, like maybe the power symbol ^

Then, move the sheet to the new file and reverse the above steps:

Find what: ^
Replace with: =

The formulas should now refer to sheets in the new file.

[Sourpickle]

The way it worked best for me was by creating an INDIRECT formula. I designated a cell where i enter the name of the page (ex: Sheet1) and the INDIRECT function always includes that as the reference.
For an example: I needed the minimum amount of column O, I designated Cell "H29" as the name of the previous sheet.

The formula went like: = MIN ( INDIRECT ( H29 & " !O:O" , TRUE ) ).

That way if the name of SHEET1 ever changes, all i have to do is update the name in cell H29.

BTW, Is there a way to enter a certain sheet name through a formula???

[Tony Valko]

The way it worked best for me was by creating an INDIRECT formula. I designated a cell where i enter the name of the page (ex: Sheet1) and the INDIRECT function always includes that as the reference.
For an example: I needed the minimum amount of column O, I designated Cell "H29" as the name of the previous sheet.

The formula went like: = MIN ( INDIRECT ( H29 & " !O:O" , TRUE ) ).

That way if the name of SHEET1 ever changes, all i have to do is update the name in cell H29.

Yeah, that'll work as long as you don't have too many formulas using INDIRECT.

BTW, Is there a way to enter a certain sheet name through a formula???

Can you be more specific?

[Sourpickle]

Can you be more specific?

Sure. Is there a function to that will return the name of any pages?
Ex: I have a SHEET1 with info there, then i have SHEET2. I want a formula to tell me the name of my other sheet ("Sheet1")?

[Tony Valko]

Sorry, don't understand.

[Sourpickle]

It's okay. The above formula worked just fine for me.
Thanks so so much!!!

[Tony Valko]

Looks like you solved the problem yourself! Thanks for the feedback.


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.