HELP... formula and general excel question

[matt179]

Hello,

Can anybody help?


Ive created a log sheet for patients i admit in the hospital i work at, i want to know is there any way to tab directly from the end of my last text column K, to the begginnning of the next row A?

Also ive created the formula's to add up how many patients are admitted per day with a COUNTA and have this working across different pages succesfully, what i want to know is that when a patient ends up not coming in, I strikethrough their information as i still want to keep their record on the log, is there any way to write a formula to subtract anyone who has been struckthrough off my totals?

Thank you in advance

 

[drsarao]

"Enter" followed by "Home" (or reverse). I am not aware of any shortcuts to achieve this on single key/key combination.

What is "strike through". Do you change the font?
Detecting strikethru font is not possible without using VBA AFAIK.
Easier will be to add a column for "Attendance" and then use COUNTIF().

 

[matt179]

Strike through is where you highlight the row and press the strikethrough button (customised toolbar) , creates a line through the whole text as if your crossing it out.

 

[drsarao]

2 options:
Make an additional column for "Attendance" and use COUNTIF().

If you want to go the programming way without adding another column then this link might help - http://www.excelforum.com/excel-programming/615086-counting-text-cells-with-strikethrough-font.html

 

[circledchicken]

Hi,

Concerning your first question if your using Excel 2007 - 2010 you might want to use Tables, which would let you tab directly from K to the next row A (you can easily convert your existing data into a table by highlighting the range and clicking on Insert>Table). I think Lists in Excel 2003 work similarly but I haven't tried them - here is a nice video tutorial if it helps: http://www.youtube.com/watch?v=YuXiLdac9i0

I agree with drsarao that an extra column to track patients who end up not coming in would be a better approach.

 

[matt179]

So if i make an extra collumn for patients that didnt attend DNA (Did not attend) and then just use a 'X' as a input how do i add that into a formula to subtract it from a total?

If my formula at the moment is =COUNTA(Monday!G2:G201) which counts all admissions on monday, how do you add in the extra formula in to subtract any that havnt attended to the net total?

 

[circledchicken]

Lets say your extra column is column H you would do something like this:

=COUNTA(Monday!G2:G201)-COUNTIF(Monday!H2:H201,"X")

 

[drsarao]

Assuming you use column T for DNA Try:
=COUNTA(Monday!G2:G201)-COUNTIF(Monday!T2:T201,"x")

Both "X" or "x" will work fine.

 

[matt179]

Ok so ive added a DNA column and used your formula which works fine, however an issue has arrisen, as there's 3 destinations for a patient to go to, and i need to work out net total for each indivdually, so if a patient has a destination but also has a DNA 'X' on i need this subtracting from each indivdual destination.

If you get me?

At the moment, it sums up the amount of patients that went to sugery, but then subtracts all patients across the board that didnt attend, i need it specifically only to subtract surgical patient that didnt attend. Surgical patients are in J and DNA's are in L

Current formula
=COUNTA(Monday!J2:J201)-COUNTIF(Monday!L2:L201,"X")

Again thanks for your help people...

 

[circledchicken]

Hi again,

Not sure I completely understand - maybe paste a small sample of how your data is laid out?

In your dataset does column J identify only patients marked for surgery - and how do you do this - with an X?

If so try something like this:

=COUNTA(J2:J201)-SUMPRODUCT((L2:L201="X")*(J2:J201<>""))

This says:

• Count all non-empty cells in the range J2 to J201 - i.e. Surgical patients
• Then subtract patients who did not attend ONLY IF they were identified as surgical patients i.e. the associated row in column J is not empty