Expand forumula in cell

Hi

I need help to expand a formula when a new sheet is created in my workbook. Atm i have 2 sheet 1 named overview and 1 named project 1. I am using overview to supervise my projects.

e.g in cell B3:

=SUMIFS('Project 1'!C:C;'Project 1'!A:A;A6)

Now when a new project is created i want to update the forumula to:

=SUMIFS('Project 1'!C:C;'Project 1'!A:A;A6) + SUMIFS('Project 2'!C:C;'Project 1'!A:A;A6)

Can anyone help?

Thanks in advance

Br

Martin

At first glance, I'd transfer the work from a formula to a function in a module (or the Workbook module). The function would iterate over all the necessary worksheets, using the formula that you describe for any given worksheet.

The benefit of doing this is that the actual formula in the worksheet remains constant (ie a reference to the function), while the function is itself smart enough to do the work 'properly'.

Let us know if this sounds reasonable, and we can talk some more!

HTH

Tony

Hi Tony

I have never tried to make a function in a module, could you help me a bit?

Thanks

/ Martin

Try

=SUMPRODUCT(--(ISNUMBER(MATCH('Project 1'!A:A,A2:A20,0))),'Project 1'!C:C)

Hi Bob

Could you explain what the formula does? Is just get REF# when putting it into my cell

Thnx

/ Martin

One of the great things about this list is the opportunity to learn!

Robert & Bob, I didn't see any discussion in your posts about what seemed to me to be Martin's central issue - he has a formula that crosses one extra sheet, ad he now needs to includea second extra sheet. All of the suggestions stick with Project1, and there is no mention of Project2.

I know I'm missing something glaringly (well, perhaps a little) obvious, but could you elucidate, so I can benefit, too?

Please! Thanks!

Tony

Hi Martin

Here's a very small spreadsheet to show you the concept!

There are two buttons on a SummarySheet - Add Sheet and Remove Sheet. If you look at the VBA code, you will see that they do just that! Add Sheet adds a new sheet to the workbook, gives it a name, and assigns a random value to cell B4). Remove sheet simply deletes the last sheet (but never the SummarySheet)!

There is a module (Module1) which contains two user functions:

CountTheSheets () and SumTheSheets ()

These, again, do exactly what their names imply.

CountTheSheets () simply returns the number of sheets in the workbook (apart from the SummarySheet).
SumTheSheets () - which is really what you will want - iterates over all the sheets and adds the value in cell B4.

DISCLAIMER!

At the moment, the RemoveSheet is working fine, but for some reason Add Page needs kicking into activity. I have a separate request for help out on this issue (When does a custom function get calculated?)

HTH,

Tony

Well - the 3D reference sounds interesting. Couldn't find it in the online help (how rare is that ;-).

However, I'm sticking with my Custom function approach which doesn't require marker worksheets, or display ay sensitivity to where new sheets get added. Especially since the volatile function issue has now been resolved!

So, here's the update example spreadsheet. Then see if it will help meet your needs!

Hi Tony

Thanks alot for your help

As i see it your function is taking the sum of cell b5 in each sheet? How can i change your function to be a sumif function instead?

Thanks again

Br martin

Hi Martin,

As i see it your function is taking the sum of cell b5 in each sheet? How can i change your function to be a sumif function instead?

One of the ideas behind a model is that it shows how to tackle a problem (while carefully leaving the actual problem for you to deal with). What my model does is to show you how you can sum the values of corresponding cells in a workbook. It also allows you to add and remove sheets (which have data in the 'appropriate' place in the worksheet), demonstrating that the original (Overview) formula does not need to be changed.

Now - to use this model effectively, it's your job to figure out what formula you want in those cells. The information seems to be contained in Columns A and C of each project, but there's no more information given. This makes it hard to provide or even suggest, a solution! If you can let us know a little more about what's on those project sheets, then we can help you a bit more.

As I read your original request, you want to summarize the contents of column C, where the contents of column A match the value in A6 (possibly A6 on the Overview sheet, although that's not clear):

(SUMIFS('Project 1'!C:C;'Project 1'!A:A;A6))

Could you post some sample data, because I'm having a bit of a problem figuring out exactly what you're trying to do within each project!

Thanks,

Tony

Hi Tony

Sry for my slow respons. Plz see attached. Resource overview 20131201 v0b.xlsm

You can see in cell h6 what i want. i want the sum for each "resoruce" in the week (in this case week 51). and i want it for every sheet/project created. So when i create a new project(sheet) i want the sumif function to included the new sheet

Does it make sense? =)

Thanks

Br Martin

It's making a lot more sense now!

I'm revising the spreadsheet that I started from, because I think that you also need a sheet that manages the resources (there was nothing like that in your spreadsheet (The summary listed them, but did not offer much information about them). Ill have something for you shortly.

I also think that you'd be better off driving this from UserForms rather than buttons on the sheets themselves (just think - you need to be able to assign a resource to a project, and to remove it from a project, and to do that by project could require a pair of buttons on every single project sheet!).

Thanks for the clarification. Here's my latest sense of what you are trying to be able to do:

==
I have a set of resources and a set of projects. Resources have a rate at which they are charged out to a project. What I want to be able to do is to track (in a Summary sheet) the time that a resource spends on each project, on a week-by-week basis. Because new projects are continually being created, I need to be able to add a project (on a new worksheet) without changing any of the formulas that are used to create the summary.
==

Let me know if this reasonably accurate - it's the basis on which I'll be creating a model for you!

Tony

PS I hope you really don't work your people for 100 hours in a week!

Well - it's certainly more than you originally asked for!

The reason is managing the unknown data on the sheets to be added. This article (http://office.microsoft.com/en-us/ex...010102346.aspx) from Microsoft explains 3-D references pretty clearly - and unfortunately it looks as if the SUMIF function does not support such references. Some of the issues that you would be facing include:

1) What does it mean to have no data in a cell that you are summing (is it 0 or an error)?
2) You'd need to use end markers (as described in another post), to delimit the functions - what happens if you move a sheet incorrectly?

My original suggestion was a user function (let's call it SUM3d), which would would allow you to manage all these possible exceptions.
Here is a much simpler (still skeletal) spreadsheet that gives you these capabilities. Open up the worksheet, and press Alt+F11, to bring up the VBA environment. You'll see two UserFunctions - Sum3D and Count3D. You have to supply a valid string address of the cell you want to sum, and up to 5 names of worksheets to skip. The cell where you have placed the formula is skipped automatically.

Take alook, and see if this is closer to what you are thinking about. At the moment it is set up to skip the sheet "SkipThisSheet". Try changing the formula to SUM3D("B3") (and COUNT3D("B3")) and see how things change.

There are two things for you still to do:

1) Determine what constitutes valid data. Only you can do this, which is why I left it for you!
2) Figure out a way to determine if the sheets to be skipped are being passed in as names or numbers (hint - that's why the parameters are all Variants). If you want help woth this, give me a call.

HTH

Tony

PS Happy New Year!