Excel/Excel formulas

Advertisement


Question
propagating formulas between sheets

I have excel file that has multiple sheets.  I use it for tracking investments week over week.

One thing that I want to add is a week to week over under for various investments (e.g. - how much did the DOW change).

Simple example:
Sheet1:A1 = 100.00
Sheet2:A1 = 150.00
Sheet2:B2 = 'Sheet2'!A1-'Sheet1'!A1

This example I can do easily for any cells that I want to compare between sheets.  The problem is that I can not figure out a way to automate this so that the inter-sheet formulas automatically propagate to new sheets that I create (I create a new sheet every week as a copy of the prior weeks sheet).  I don't want to have to recreate the inter-sheet formulas manually each time I create a new sheet.

Thanks in advance.

Answer
you might be able to use the INDIRECT() function to "construct" cell references from pieces of text.  For instance this formula give you the value of A1 directly:

   ='Sheet1'!$A1



This formula does the same thing indirectly.  If on Sheet2 you had the text "Sheet1" in cell B2, then this would "construct" the reference above indirectly:

   =INDIRECT("'" & B2 & "'!A1")

So with this technique you can construct your INDIRECT() formulas on Sheet2 to reference Sheet1 using the text in a cell on that sheet to indicate the name of the prior sheet to use.  When you make a new sheet, you'd need only update that one cell for all the formulas to change to reference that sheet instead.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.