Excel Gurus Please Help !!

[Sir Stinkalot]

If you have found it to this thread you must be an Excel Guru :2tsup:

I have a formula that I would like to simplify as I need to do it hundreds of times.

I have numerous worksheets that are all of the same template with different figures.

I also have a summary sheet that needs to be able to calculate a figure for each project from a number of individual work sheets.

At the moment I am manually entering the following formula, where 0x(G) is the worksheet and I would like Excel to tally up value P17 from in this case 3 worksheets.

='04 (G)'!P17+'20 (G)'!P17+'68 (G)'!P17

Is there a way where I can tell Excel to add all values P17's without having to enter in the worksheet number and value individually. I was thinking something like:
=P17 [04(G)+20(G)+68(G)]

The aim being I also need the summary sheet to run the same calculation with a different cell value, with something like the above formula I would only need to change P17 to P18 once and not have to change it multiple times as in the current formula.

I hope it makes some sense.

Thanks

Stinky.

[Sir Stinkalot]

It might help if I post an example ..... the file is a small exert of the larger project. It has been set up only to calculate the executive hours spent.

As you can see each project may be spread over a number of different sheets. The columns E-H need to calculate the total hours spend across all project sheet as listed in column P (P9 in the first example).

Each of the formulas entered in the example were done manually ..... so I had to manually change each worksheet number, and cell number in each calculation .... something that will take forever and also lend itself to errors.

Hope it is a little clearer now!

[Sir Stinkalot]

Sorry I don't like to bump but as I plan to start entering all of the information manually tomorrow I thought I might try one more time to find an Excel guru who can answer my question ...... and hopefully save some time!

Thanks.

[Wood Butcher]

You can create a 3D cell reference, but that is only for a range of worksheets, not selecting particular worksheets. So you can only add up totals from (in your case) worksheets G05, G04, G03, G02, G01 inclusive (as it makes a range G05:G01) not only G05 & G03.

Try putting this into cell E3 in your Summary Executive sheet and have a play
=SUM('G05:G01'!F9). Also hit F1 in excel and type 3D Reference for the search query. Maybe there is more to it but that is all I know.

[Wood Borer]

Stinky,

Not quite the solution I had in mind but it will save you time.

I have quickly made a table of addresses using the CONCATENATE function.

I have used the INDIRECT function to reflect these values and then summed them.

Try extending my tables by dragging across and dragging down.

These calculations including the reference cells could be hidden or put on another sheet.

[Wongo]

Sir, are you still after a quick/accurate solution or have you decided to do it manually?

[Wongo]

PM me your emial address. I have present for you.:cool:

[Wongo]

Unzip the files and save them to you C drive.
Open excel first
Go to Tools -> Add-ins
Click on Browse and look for wongo_addin.xla
Now open sample_modifiedbywongo.xls
The second table in “Summary Executive” does exactly the same thing as the first table. Except there is no error. :D (see E4)

I created a new function called “get_sum()”. There are 2 inputs to this function – a list of target sheets and a target cell. You can copy and paste the function like you do with standard excel functions.

Have fun.

[Wood Borer]

Scott,

I would also be interested in seeing your solution.

[Sir Stinkalot]

Gentlemen ..... thank you for your assistance and coming to my rescue. I have just returned from work ready to start the long process of manual entering and I thought I would check the forum to see if anybody had an answer ...... and I am so happy that I did :2tsup:.

Wood Butcher ..... I understand your thinking but unfortunately the work sheets are sorted by jobs and then date. Each worksheet covers three months but there may be many other jobs between so one job may be on sheet G1, G12, G29 with another G2, G8, G10.

Wood Borer .... Thanks for your work around ..... after a but of investigation to see how it all works I think it would have worked a treat.

Scott .... you are a champ who has saved many, many hours of tedious formula entering (approx 1728 but who's counting). I have applied it against all of the sheets and it seems to do everything I needed in an instant.

Just a couple of small problems:

1. When I enter the formula into the summary sheet (on all summary sheets that I have) the first 8 rows or so come up with an error. The rest of the summary sheets (below the error cells) seems to work a treat. The error listed is "A value used in the formula is of the wrong data type". The formula and data type seem to be the same as the ones entered below which work ???? :?

2. I find that if I alter a figure in the work sheet it doesn't automatically update the calculations in the summary sheet. If I add another work sheet into the summary calculation, ie have G2, G5, G6 and then add G4 it will increase the number of hours spent. Is there any update that I need to run so that it goes back and rechecks all calculations?

I know I am asking a lot but it will help me alter the hours on each sheet to suit and then still come up with the final answer.

Excel is a great program if you can follow it ..... I am happy to try and research but the problem that I have is I don't actually know how to phrase a question in Google to get the answer to what I am after.

"How can I get excel to add up a value from a number of different sheets into a summary sheet that can be updated automatically when I update the values in the worksheets?" :-

[Wongo]

OK you had the free bit. Now you will be charged by every 30 mins of programming. :D

Let me have a quick look OK.

[Sir Stinkalot]

Its worth it :)

I have solved issue number 1. Stupidly I was labelling the sheets 01G, 02G and the like however in the summary sheet I put in 1G, 2G ..... resulting in the error. A quick update of the summary sheet to read 01G, 02G all was fixed there ..... I will now check to see if that my help with issue 2.

[Wongo]

OK it is a tough one. I don't have the answer for you but there is a way

Alter the figures
Back to the summary sheet
go to tool -> options
click on the Transition tab
check the "Transition formula evaluation" option
click OK and it will re-calculate.

when finished uncheck the "Transition formula evaluation" option

Sorry it is not a solution as such but it will help you for now.

I will let you know if I had more luck.

[ajw]

I'd achieve this by using a combination of two Excel functions, provided all the individual sheets have the same layout.

I've done a quick modification of your sample, and it includes a formula that looks like this:

=INDIRECT(ADDRESS(F$7,F$8,,,$E11))

I'd grab the data from each individual sheet into a running total table, and then use a SUMIF function to add up the totals.

Let me know if you'd like a more comprehensive explanation. This procedure is very useful once you get the hang of it.


Allan

[Sir Stinkalot]

Thanks again for everybodys help :)

Wongo ... please do not go to any more trouble. I will test your work around and if sucessful it will get me out of my jam. The main problem is the one you have already solved for me .... the updating of the figures shouldn't be a big problem. If I do update a few of the sheets I will just do your work around and that will fix it .... alternatively I just re-enter the formula and drag down.

Cheers all ...... cant thank you all enough!

[Wongo]

No worries mate.

Anyway, all formulas will be recalculated when you open the file. So problem solved.:2tsup: