Is there a faster way?

[roc_ent]

Hi all, I'm working with a 14 page workbook, where the first sheet is my control sheet. Second tab is named Jan, 3rd tab is Feb, all the way to Dec. On the first sheet or "Control sheet" I have all my formulas and are perfectly duplicated in each month cell to cell #. Is there a way to drag the formulas so that the SHEET name, changes from Jan, to feb, to mar, etc..... Thanks for your time. I'm using a find and replace now but I've got a long way to go if there is no better way. Thanks.

 

[Sully_T]

If your just pulling a value from the Monthly sheets then indirect would work, assuming you have a row containing the sheet names
This would pull the value from cell A1 of the sheet name referenced in B1 of the current sheet ie Jan
=INDIRECT(B1&"!A1")

 

[jasonb75]

if there is no better way.

The INDIRECT suggestion might be easier to set up, but would be far from a better way, the link below lists a number of functions that are best avoided when possible and the reasons for doing so.

Volatile Excel Functions -Decision Models

Microsoft Excel Calculation Secrets and optimisation tips, calculation methods, calculation sequence, dependencies and memory limits.

www.decisionmodels.com

Looking at what you have asked for, I would say that find and replace is the better way, just not necessarily the quickest.

 

[roc_ent]

If your just pulling a value from the Monthly sheets then indirect would work, assuming you have a row containing the sheet names
This would pull the value from cell A1 of the sheet name referenced in B1 of the current sheet ie Jan
=INDIRECT(B1&"!A1")

Thanks Sully, perhaps I did not explain it properly. So my sheet names are where I enter all expenses for that month. I'm only using 4 columns on each monthly tabs. Date, Particulars, Amount, Account Code.
On the Control Sheet is where all the total data is being calculated.
For example in B20 of the Control Sheet is the accumulated total from January sheet for Maintenance Account. Now I need to drag that formula to B21 down to B22 but pick up the total in February sheet.

 

[roc_ent]

The INDIRECT suggestion might be easier to set up, but would be far from a better way, the link below lists a number of functions that are best avoided when possible and the reasons for doing so.

Volatile Excel Functions -Decision Models

Microsoft Excel Calculation Secrets and optimisation tips, calculation methods, calculation sequence, dependencies and memory limits.

www.decisionmodels.com

Looking at what you have asked for, I would say that find and replace is the better way, just not necessarily the quickest.

Thanks Jason, that is what I've been doing thus far.

 

[jasonb75]

Could you post an example of the formulas that you are using so that I can see the references?

Are your sheets named as 'January' etc, or are they abbreviated?

 

[jasonb75]

Re:- private message, I only asked for an example of the formulas so you could post that without XL2BB.
You could upload your workbook to dropbox, or similar and post a link but it would need to be posted here, not sent privately.

 

[roc_ent]

Sorry about the confusion, here is a pic of the formulas I am using for the first three expense accounts. I'm dragging down the repair to the next month, using find and replace to change the month (which is the tab name), then I drag the new formula to the right and go in and change every account name one by one. So in summary what I was looking to find is a simpler way to drag or use another formula that would let me drop down from month to month. Thanks hope the pic helps.

 

[jasonb75]

You wouldn't be able to drag and keep the formulas exactly as shown because the ranges used are not all the same size. With a bit of consistency, you could do something like this but it is takes a bit of thinking about to create the formulas. Personally, I would say that this is more complex than find and replace, but it might be quicker with your layout if you are able to follow the process without too much confusion.

I've used the first two accounts and two months for the example below, you will see that I've used one formula to create another as a text string. I have not used $ symbols on the sum and criteria ranges, as they are text strings they will not increase when you drag and fill.

Once the formulas are entered, you can copy the range and pastespecial > values, which will convert the formulas to text as shown in the bottom section of the example (which is included for demonstration only, in the proper sheet you would paste over the cells with the formulas).

Once this has been done, using find and replace to replace = with = will force excel to recognise any text strings starting with = as valid formulas and calculate them accordingly.

Hope that makes sense

sumif weeknum (version 1).xlsb
	A	B	C
1	Accounts	Rep/Main	Synergy
2	January	=SUMIF(Jan!D5:D47,"Rep",Jan!C5:C47)	=SUMIF(Jan!D5:D47,"Syn",Jan!C5:C47)
3	February	=SUMIF(Feb!D5:D47,"Rep",Feb!C5:C47)	=SUMIF(Feb!D5:D47,"Syn",Feb!C5:C47)
4
5	Accounts	Rep/Main	Synergy
6	January	=SUMIF(Jan!D5:D47,"Rep",Jan!C5:C47)	=SUMIF(Jan!D5:D47,"Syn",Jan!C5:C47)
7	February	=SUMIF(Feb!D5:D47,"Rep",Feb!C5:C47)	=SUMIF(Feb!D5:D47,"Syn",Feb!C5:C47)
Sheet2

Cell Formulas
Range	Formula
B2:C3	B2	="=SUMIF("&LEFT($A2,3)&"!D5:D47,"""&LEFT(B$1,3)&""","&LEFT($A2,3)&"!C5:C47)"

 

[mikerickson]

The title asks for a "faster way", and INDIRECT is probably the quickest way to get the formulas in cells.
But once its set up, the volatile INDIRECT combined with the (sort of) array formulas SUMIF, suggest that the long term fastest would be to hard code the sheet names rather than using INDIRECT.