Best Practices (Counter)

[Dax Arroway]

I'm guessing the best way to do this would be to use a database but my "stone
age" agency that I work for insists on using Excel so I'm sort of stuck. I
have a counter page that I'd like to automate and I'm wondering if you excel
gurus out there might lend me a brain cell and tell me what the best way of
achieving this might be.

Sheet 1 = Patient count. Like a database table with headers and each row is
a new record. Headers include Name, Age, DOB, ClientID, Insurance Type,
AdmitDate, DischargeDate, ...that sort of thing.

Sheet 3 = Bed Days. Bed Days is a counter page which has the dates in a
month running down one column and a count of clients with 4 different types
of insuraces across the top (State, Contract, Oregon, Private) with totals at
the bottom. The next month would start 5 cells over to the right and it's
getting a little crazy (December 08 is columns AU:AY!).

The Old Way:
What my secratary was doing was going through the Patient Count sheet and
counting how many clients of each insurance type we'd have in a given day and
manually enter them next to the corresponding date. (OMG! There has to be a
better way!)

The New Way:
What I'm looking for is a formula that looks at the Patient Count sheet and
collects this data for me and then enters in on the Bed Days sheet. I could
change the layout of the page so there'd be only 5 columns running down the
sheet (Date, State, Contract, Private, Oregon) but I'm not sure how to get
Sheet3 to collect and enter data for specific dates. I'm thinking I could
use a CountIF statement, asking it to collect the counts from a specific type
of insurance if the date that's listed is within the admit and discharge
dates of the client but entering a formula like this in all the cells seems
to be asking the sheet to do a LOT of calculating. I'm not so sure that this
would be the best way of obtaining what I'm after.

If this were SQL I'd say the statement should be:
SELECT State, Contract, Private, Oregon
FROM Sheet1
WHERE Sheet3_Date is between Sheet1_AdmitDate AND Sheet1_DischargeDate
....but I don't know how that translates in Excel-speak.

Any help with this one? Much thanks in advance! You guys have always come
through with brilliant suggestions. I really appreciate it! Please let me
know if any of this is unclear and I'll try to explain better. I can send
an example if needed but I need to know where to send it to please.
--Dax

 

[Pete_UK]

You would use a single SUMPRODUCT formula to do this, which would then be
copied across the 4 columns and down the 31 days. If you had two drop-down
cells in that sheet to allow you to select the month and the year, then you
would only need the 5 columns, as you could generate the table automatically
through these formulae and change the month/year quite easily.

Can you just confirm which columns you use in Sheet1 (and is this the
correct sheetname?) and in Sheet3, and how many records you are likely to
have in Sheet1? If you do this then I can give you a formula specific to
your set-up. If you need help setting up the drop-downs then let me know, or
you can send me a sanitised version of your file (.xls NOT .xlsx) to:

pashurst <at> auditel.net

(change the obvious).

Hope this helps.

Pete

 

[Dax Arroway]

Hi Pete,
Thanks for your help. I could type out what my sheets look like but its
easier to send you a sanitized sheet. I'll send it to the address provided.
Thank you so much for taking a look at it.
--Dax

 

[Pete_UK]

I received the file from Dax, and after a few emails were exchanged I
sent him back an amended file along the lines that I described
earlier.

Pete