Automatic update of worksheet name in formula

%3CLINGO-SUB%20id%3D%22lingo-sub-135759%22%20slang%3D%22en-US%22%3EAutomatic%20update%20of%20worksheet%20name%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-135759%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%26nbsp%3Bseveral%20worksheets%20within%20the%20same%20workbook%26nbsp%3Bin%20which%26nbsp%3B%20data%20is%20pulled%26nbsp%3Bfrom%20a%20worksheet%20titled%20%222017%20Budget%22.%26nbsp%3B%20I%20would%20like%20to%20update%20this%20workbook%20with%20my%202018%20budget%20numbers.%26nbsp%3B%20Is%20there%20a%20way%20to%20have%20the%20formulas%20in%20other%20worksheets%20automatically%20update%20to%20the%20%222018%20Budget%22%20without%20having%20to%20retype%20all%20formulas%3F%26nbsp%3B%20Your%20expertise%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-135759%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-139731%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20update%20of%20worksheet%20name%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-139731%22%20slang%3D%22en-US%22%3E%3CP%3EAmy%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20just%20ran%20across%20your%20post%2C%20and%20it%20sounds%20like%20we%20are%20doing%20similar%20things.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20two%20workbooks%3A%20Budget%202017%20and%20Budget%202018.%20Actually%20I%20have%20budgets%20back%20for%20several%20years.%20Each%20has%2012%20worksheets%2C%20layed%20out%20as%20a%20month%20in%20each%20worksheet%20with%20the%20number%20of%20lines%20corresponding%20to%20dates%20of%20the%20month.%20Formulas%20auto-update%20budget%20values%20for%20each%20day%20and%20for%20the%20month.%20The%20end%20value%20of%20each%20month%20auto-updates%20as%20the%20beginning%20value%20of%20the%20succeeding%20month%20(i.e.%2C%20the%20last%20entry%20in%20March%20auto-updates%20to%20the%20beginning%20value%20of%20April%2C%20and%20so%20on).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20create%20subsequent%20year%20budgets%2C%20I%20created%20a%20blank%20budget%20template.%20It's%20blank%20in%20that%20it%20has%20no%26nbsp%3Bdata%20for%20expenditure%20descriptions%20or%20associated%20amounts%2C%20but%20does%20have%20all%20the%20formatting%20info%2C%20including%20formulas.%20I%20just%20do%20a%20%22save%20as%22%20and%20rename%20the%20saved%20workbook%20as%20the%20appropriate%20year%20budget%20(e.g.%2C%20Budget%202019).%20Then%20I%20can%20fill%20all%20the%20new%20income%20and%20expenditure%26nbsp%3Bdata%20and%20get%20auto-updated%20daily%20and%20monthly%20totals.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMy%20problem%20is%20getting%20the%20ending%20value%20of%20December%202017%20(in%20the%20Budget%202017%20Workbook%2C%20for%20example)%20to%20auto-update%20to%20the%20beginning%20value%20of%20January%202018%20%3CSPAN%3E(in%20the%20Budget%202018%20Workbook%2C%20for%20example)%3C%2FSPAN%3E.%20All%20I%20have%20been%20able%20to%20do%20so%20far%20is%20%22link%22%20the%20associated%20cells%2C%20and%20then%20I%20can%20change%20the%20value.%20Any%20ideas%20on%20setting%20up%20the%20auto-update%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-135833%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20update%20of%20worksheet%20name%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-135833%22%20slang%3D%22en-US%22%3E%3CP%3EAmy%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20rename%20the%20worksheet%20%22Budget%202017%22%20to%26nbsp%3B%20%22Budget%202018%22%2C%20and%20the%20formulas%20in%20other%20worksheets%20that%20connected%20to%20this%20worksheet%20will%20change%20automatically!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGive%20it%20a%20try!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2090630%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20update%20of%20worksheet%20name%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2090630%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20Amy%2C%20I%20have%20a%20different%20problem%2C%20but%20I%20believe%20also%20related%20to%20the%20automatic%20update%20of%20worksheet%20name%20in%20formula%3A%20I%20decided%20to%20use%20lambda%20function%20to%20turn%20into%20formula%20a%20long%20command%20line%2C%20which%20had%20been%20working%20perfectly%20until%20then.%20The%20LAMBDA%20function%20also%20worked%20perfectly%20the%20first%20time.%20As%20soon%20as%20I%20closed%20the%20workbook%20and%20rebooted%20it%2C%20the%20function%20stopped%20working.%20I%20manually%20deleted%20the%20reference%20to%20the%20page%20at%20the%20beginning%20of%20the%20function%20and%20it%20returned%20to%20work.%20If%20you%20or%20others%20at%20the%20Tech%20Community%20can%20help%20with%20that%20too%2C%20I'd%20be%20very%20grateful.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have several worksheets within the same workbook in which  data is pulled from a worksheet titled "2017 Budget".  I would like to update this workbook with my 2018 budget numbers.  Is there a way to have the formulas in other worksheets automatically update to the "2018 Budget" without having to retype all formulas?  Your expertise would be greatly appreciated!

3 Replies

Amy,

 

Do rename the worksheet "Budget 2017" to  "Budget 2018", and the formulas in other worksheets that connected to this worksheet will change automatically!

 

Give it a try!

Amy,

 

I just ran across your post, and it sounds like we are doing similar things.

 

I have two workbooks: Budget 2017 and Budget 2018. Actually I have budgets back for several years. Each has 12 worksheets, layed out as a month in each worksheet with the number of lines corresponding to dates of the month. Formulas auto-update budget values for each day and for the month. The end value of each month auto-updates as the beginning value of the succeeding month (i.e., the last entry in March auto-updates to the beginning value of April, and so on).

 

To create subsequent year budgets, I created a blank budget template. It's blank in that it has no data for expenditure descriptions or associated amounts, but does have all the formatting info, including formulas. I just do a "save as" and rename the saved workbook as the appropriate year budget (e.g., Budget 2019). Then I can fill all the new income and expenditure data and get auto-updated daily and monthly totals.

 

My problem is getting the ending value of December 2017 (in the Budget 2017 Workbook, for example) to auto-update to the beginning value of January 2018 (in the Budget 2018 Workbook, for example). All I have been able to do so far is "link" the associated cells, and then I can change the value. Any ideas on setting up the auto-update?

 

 

Hi, Amy, I have a different problem, but I believe also related to the automatic update of worksheet name in formula: I decided to use lambda function to turn into formula a long command line, which had been working perfectly until then. The LAMBDA function also worked perfectly the first time. As soon as I closed the workbook and rebooted it, the function stopped working. I manually deleted the reference to the page at the beginning of the function and it returned to work. If you or others at the Tech Community can help with that too, I'd be very grateful.