Programatically changing the way a cell calculates 1

[DBAMJA]

I have a spreasheet that has this month 50 sheets for patients. Next month some of those patients might me gone and new patients might be added.

What I want to do is have a summary sheet in the file that will allow the calculation of how many treatments and how much of certain drugs were used for the whole month.

Is there a way to programatically change the summary sheet to calculate the summaries when some sheets will be deleted in a given month and some added?



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[Corgano]

Having a sheet for each patient makes anything extremely difficult.

What you are looking for could be pretty easily done if all the information was in one sheet. The way it is now I couldn't being to think of an easy way.

 

[DBAMJA]

Unfortunatly, I was not the one that set up the file but each patient does need to be tracked individually. I am trying to avoid building an application in Access for the users because I just have too many other projects at this time to try to accomplish this.

As usual, the users develop things without getting IT support in the beginning and then we need to try to pull their asses out of the fire.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[ajakeway]

Why not go through each sheet and add it up?

Sub testst()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'your calculations here
Debug.Print ws.Name ' for example
Next ws
End Sub

 

[xlbo]

This is exactly why you should store data in one place - it can easily be diseminated if you wanted to look at one patient's records but it is FAR harder to put back together again - the only way I could think of doing this would be a custom function (UDF) that uses VBA to loop through all sheets without referencing their names. If the data is all consistently held in the same cell on each sheet, it would be fairly easy

Function SumSheets(rng As String)
Dim TempSum As Double
TempSum = 0
For Each sht In ThisWorkbook.Worksheets
With sht
  If .Name <> "Master" Then TempSum = TempSum + .Range(rng).Value
End With
Next
SumSheets = TempSum
End Function

You would call this by entering
=sumsheets("B10")
in your "Master" sheet - this will sum up all the numbers in cell B10 on all sheets except the one called "Master"

Change that to reflect what your summary sheet is called and you should be away

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ajakeway]

How is the data on each page stored? Is it consistently in the same location? Either in a specific cell, or the last cell in a row / column? Can you give us a bit more to work with here?

 

[AnotherHiggins]

Geoff,

That's great stuff! I've never created a User Defined Function like that before. I'm saving this for future reference. Have a star on me.

John

 

[KenWright]

DB, is your data structure the same on every sheet, with for example a 100 possible drugs/treatments on each sheet (as per every sheet) with data in just the relevant cells?

If this the case, then have you considered a summary sheet, with two wrapper sheets (a start sheet and finish sheet) around your patient sheets, and then drill through from start to finish to sum all the sheets in between. Note though, this ONLY works if the structure of each sheet is identical. It will though, allow the insertion and deletion of sheets, with no impact on the summary sheet other than updates to the data.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

As will my udf Ken ;-)
and you only need the one - just change the range address reference....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

Hi Geoff, that's true, but I guess I'm just averse to using a UDF to do what a standard formula will do, ie =SUM(Start:Finish!A1) with no need to change references to fit the UDF as they are all relative.

Regards
Ken.....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

Ken - true but I'm averse to adding extra sheets to workbooks ;-)

Either way, I guess we'll know if and when DBAMJA returns !

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

LOL

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------