relative formula copied across worksheets

g48dd · 12-08-2008, 01:00 PM

Ok I am forgetting to do something. It's been a long time since I have worked with Excell, it use to be the " $ " that made things absolute or relative. I have this formula: ='1'!C194+C193 on sheet 1 named "1" without the " ". I need to copy it across 31 sheets. I thought that it was the " ! ", without the " ", that makes the address absolute so I took it out and then attemped to copie it to other sheets. The idea was that the formula would then read: ='2'!C194+C193 on sheet 2, and ='3'!C194+C193 on sheet 3 and so on. The formula is in the exact same location on each sheet, C194. But this won't work. I am at this time doing each formula one at a time. I have many workbooks that will use the same style of copy and paste; about 15 workbooks each with 31 sheets. It would help if someone could explain how to save time. I do know how to use the Ctrl to select different cells and how to use Ctrl+Page UP/Down to change tabs. I just have to get the formula correct.

Thank You
Ken

**JBeaucaire** · 12-08-2008, 01:42 PM

Less words, more straight example.

The name on sheet1 and the cell reference and the formula.
The name on sheet2 and the cell reference and the formula RESULT desired.
The name on Sheet3 and the cell reference and the formula RESULT desired.

That should be enough for us to figure out what you want.

**JBeaucaire** · 12-08-2008, 01:48 PM

If you're trying to enter the same exact formula on 31 sheets in the same cell so they all refer to the same range on their individual sheets, you're going about this the wrong way. You don't need the sheet reference in there at all.

Click on sheet tab #1. Hold down the SHIFT key and click on the LAST sheet tab you want. This highlights ALL the sheet tabs inbetween, too.

Click on the cell in sheet1 and enter your formula.

When you press enter, it just wrote that formula in the same cell on all 31 sheets.

g48dd · 12-08-2008, 03:15 PM

Now I see why examples are best, thanks but thats not what I am trying to achive but I just learned something new. Ok Example is attached, explanation is on sheet. Please look at sheet 2, then 3, then 4.

Thank You
Ken

**JBeaucaire** · 12-08-2008, 03:51 PM

Ok, you will HAVE to insure those sheetnames stay numerical for this to work.

I added this little tidbit to the center of a simple indirect/addition formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

This basically looks up your sheetname. I take that answer and subtract 1, then put that into the INDIRECT function to construct the cell reference for the prior sheet.

Have a look.

g48dd · 12-08-2008, 05:00 PM

Holly Cow Batman, not exactly what I thought I was going to see. It works just fine but before I say I understand what is going on I need to study it and break it down. I will study it a couple of days and get back. If I can break it down myself and explain what each of the opperators are doing then I can fix it when something goes wrong.

Thank You
Ken

**JBeaucaire** · 12-08-2008, 05:12 PM

Breakdown:

=C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")

The Blue section is just the total on the current page.

The Purple section outlines how we use INDIRECT to build a cell reference rather than just type it in.

Indirect([previous sheetname] + "!C12" becomes just 1!C12 once the construct completes.

The Green shows how we use a function to "figure out" what the [previous sheetname] is. That MID formula is a standard trick for displaying the CURRENT Sheetname in a cell, so I put it in the formula instead.

Since the current sheetname is numerical, we can subtract one from it and that becomes my [previous sheetname]. I underlined where I did that, above.

That completes the construct and it just becomes a normal reference at that point.

**JBeaucaire** · 12-08-2008, 05:14 PM

If you really want to watch a formula unfurl, turn on the Formula Auditing toolbar. Highlight the cell with the formula, then click the Evaluate Formula button.

Now you can step through the formula and watch it change variables on piece at a time and see how it works.

g48dd · 12-08-2008, 07:10 PM

Thank You, the Formula Auditing is exactly what I need. I have used functions and nest functions and understand the parentheses and order of operation. I have nver used INDIRECT or MID. I am trying to look INDIRECT & MID on the net because I do not have Excell book with me here in Iraq. I have some of it figured out but I don't get the use of the ] back to youu as soon as I figure it out.

Ken

g48dd · 12-08-2008, 09:51 PM

Hi, I pasted the formula in even though I have not figured every thing out yet just to see what would happen. Now I purposely created an example of what I was working with. The cell references I am using are different. I did this because I need to see if I can make the correct changes that make it work. That does not mean that I will really understand how it works but I should be getting close to understanding it if I can make it work for me. Right now I have a circular ref. problem. No hints please, I want to see if I can do it with out help, I will be able to fix it when someone screws up my work lol and I should be able to explain to my supervisor why something happened. This sheet also has to be approved by HQ its government paper work. I will be asked how I came up with some of these ideas; I do not take credit for ideas and work others do. It will be a couple of days before I get back to you; I have other work pressing at the moment.

Your Help has been much appreciated
Ken

g48dd · 12-14-2008, 02:53 AM

Still having trouble, it appears to me that whatever I have done, I can't call the previous sheet name. My totals should be running up 5 at a time. But all they are doing is totaling what is on the current sheet, take a look please.

Thanks Ken
Sorry it took so long to get back

**JBeaucaire** · 12-14-2008, 03:03 AM

You're last number is off. Need to be adding C13 from the previous sheet, not C12.

=C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C13")

g48dd · 12-14-2008, 09:59 AM

Thank you very much I am not paying attention. I still have some questions about this. The problem is solved. It works great and I see how each part of the formula is working but I don't understand a couple of things in the syntax. I am thining that I should start a new thread, one where I write my own MID function and make it call the name.

However this works great, thank you for your help

Ken

**JBeaucaire** · 12-14-2008, 06:57 PM

Awesome.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

g48dd · 01-02-2009, 01:48 AM

I am sorry that it has taken so long to get back, It will not let me go to Edit so that I can mark this has solved, I am making nw post that refers to this one has I need help understanding a few things but this worked perfect.

**DPAnders87** · 12-07-2010, 09:31 PM

Is the only way to make this work to name the worksheets simply 1, 2, 3, 4 , etc. ? I tried to use week 1, week 2, week 3, etc but got #value because it was trying to subtract Sheet2-1 rather than 2-1. Is there another way to do this without naming the sheets 1, 2, 3?

**JBeaucaire** · 12-07-2010, 09:43 PM

Not sure what you mean by "this". As per forum rules, please post your own thread with an appropriate title, attach supporting workbook(s) to demonstrate what you're trying to accomplish, include BEFORE/AFTER examples if helpful.

If this thread you really think is relevant to your need, include a link to it in your own thread.