Help with formula across sheets.

[Mechjo16]

Hello all,

I am trying to use a formula that references the prior sheet. I was wondering if anyone can help me figure out how to do this without going into each sheet individually. This is VERY time consuming and I am sure there is a quicker way. I just have been unable to figure it out. When ever I try to select all sheets and put the formula in, It just references sheet 1. I need it to reference the prior sheet. I also need sheet 1 on reference the last sheet in the next row.

Please see example workbook.




Any help is greatly appreciated.

Thank you in advance!

-John

[dflak]

If this is a one-off then here is a way to do this.

Set up sheet 2 the way you need it. Then make as many copies of this sheet as you need. Then go to each sheet and select a cell.

Press CTRL_H to bring up the Find and Replace dialog box.

In the find box type Sheet 2 Name (whatever it is). In the Replace with box type Sheet 3 Name. Click on Replace All.

When you go to the next sheet and click on CTRL-H, these two fields will still be "in memory" - edit the Replace with field. Lather - Rinse - Repeat.

If you have to do this repeatedly, then I'd record a macro and modify it.

[Mechjo16]

If this is a one-off then here is a way to do this.

Set up sheet 2 the way you need it. Then make as many copies of this sheet as you need. Then go to each sheet and select a cell.

Press CTRL_H to bring up the Find and Replace dialog box.

In the find box type Sheet 2 Name (whatever it is). In the Replace with box type Sheet 3 Name. Click on Replace All.

When you go to the next sheet and click on CTRL-H, these two fields will still be "in memory" - edit the Replace with field. Lather - Rinse - Repeat.

If you have to do this repeatedly, then I'd record a macro and modify it.

Thanks for the reply!

I am not real familiar with Macros and was thinking that was what was needed. This is a task that is done regularly and sometimes 40-50 sheets are required which is what makes this task so daunting. I will try this solution tomorrow when I get to work.

Thank you!

[dflak]

OK, I recorded said macro and modified it. There is a bit of code you will have to modify and I will highlight what it is. What this macro does is take the "template" sheet - in this case Sheet2 and makes a copy of it at the end of the workbook. It gets, the previous sheet's name and substitutes the String to replace - in this case "Sheet1" with the previous sheet name.

Here is the code. What you have to change is highlighted in red

Please Login or Register  to view this content.

In the attached workbook, this macro is linked to CTRL-S.

You will wind up with a series of sheets like Sheet2(2), Sheet2(3), etc. that you will have to rename manually unless you can tell me that there is a way to name the sheet by its position in the book (E.g. Month and Year) in which case, I can rename the sheets as I go along.

[Mechjo16]

Thank you for your efforts! I will give this a shot. Just as a FYI, the sheet names are always different based on the task so I will have to name them.

OK, I recorded said macro and modified it. There is a bit of code you will have to modify and I will highlight what it is. What this macro does is take the "template" sheet - in this case Sheet2 and makes a copy of it at the end of the workbook. It gets, the previous sheet's name and substitutes the String to replace - in this case "Sheet1" with the previous sheet name.

Here is the code. What you have to change is highlighted in red

Please Login or Register  to view this content.

In the attached workbook, this macro is linked to CTRL-S.

You will wind up with a series of sheets like Sheet2(2), Sheet2(3), etc. that you will have to rename manually unless you can tell me that there is a way to name the sheet by its position in the book (E.g. Month and Year) in which case, I can rename the sheets as I go along.

[Mechjo16]

OK, I recorded said macro and modified it. There is a bit of code you will have to modify and I will highlight what it is. What this macro does is take the "template" sheet - in this case Sheet2 and makes a copy of it at the end of the workbook. It gets, the previous sheet's name and substitutes the String to replace - in this case "Sheet1" with the previous sheet name.

Here is the code. What you have to change is highlighted in red

Please Login or Register  to view this content.

In the attached workbook, this macro is linked to CTRL-S.

You will wind up with a series of sheets like Sheet2(2), Sheet2(3), etc. that you will have to rename manually unless you can tell me that there is a way to name the sheet by its position in the book (E.g. Month and Year) in which case, I can rename the sheets as I go along.

I am getting the sheet to add with the macro but the number is coming back s REF. I have attacked a workbook for you to see.

Thank you!

[dflak]

I don't seem to be able to replicate the issue.

[Mechjo16]

I don't seem to be able to replicate the issue.

Could it have to do with the name of the file? It might be because it's a macro and I am getting rejected through security.

[Pepe Le Mokko]

@mechjo
please don't quote entire posts unnecessarily. They are just clutter and make threads hard to read.
Use the "Quick reply" instead
Thanks

[dflak]

The name of the file should not matter. Only sheet names should matter.

If you have your security set not to run macros then it's not going to run at all.

About the only thing I can see that would interfere with the way the macro runs is if the workbook is protected.

In the attached, I changed the line of code that detects whether the sheet name needs to be "quoted" in the formula to be understood. Maybe that will make a difference for you.