Hello All ...

First off, I'm an okay Excel user ... I can usually get done what I need to and if not, I can look it up somewhere. I don't use macros (although I know how) or VBA. If a project is anything I consider "big," I use Access.



I am an Access power-user ... VBA coding and the whole bit. I came here from AccessForums.net

However, I know some things are better in Excel. I'm at that point with this latest project.

I want to do something, but I don't even know if it's possible ... so here I am.

I am tracking blood chemistry results. I want Excel to fill a cell based on data in another tab. I imagine it's conditional formatting on steroids.

For example:

SDMA Levels ... I have a table on my "Reference" tab that has four SDMA categories ... Normal, Moderate, High, and Severe. Each category has a low value and a high value i.e. Moderate is 13-16. So, the table looks like:

SDMA measured in ug/dl
Low High
Normal 0 14 92.171.8
Moderate 13 16 234.128.12
High 17 20 255.0.0
Severe 21 50 192.0.0


On my test result tab, I have the name of the various things I'm tracking in column "A" and then each column going across has a date at the top and results in the appropriate row.

I'd like Excel to see that on a particular date, the SDMA was 15 and turn the cell orange based on the RGB values in the table above. Obviously, I'd add test results as needed going across ... so the next date, the SDMA is 17 and that cell would turn red based on the RGB value.

I have ten different tests I'm tracking.

I'd also like to know where in the range a specific result falls. I'd like to create a comment for the cell so that when I hover over it, the range appears. If the SDMA was 15, the cell would be orange and the comment would be "Moderate 13-16." I would concatenate the comment from the information in the table.

Is this even possible? I assume I'd need to use VBA, but is there an easier way? I just figured VBA because I'd code it once for each row and be done with it ... I WILL say that I'm hit and miss with using conditional formatting ... sometimes I get the results I want and sometimes, I want to throw a brick at the monitor and just get out my highlighters!

Any help at all would be appreciated!

Susie
Kansas

Sounds involved. Your description is very good, but not as good as an Excel file would be. Attach one here, and perhaps include a 'desired results' sheet to show the kind of result you want.

OK, here is the file.

The first tab - Xander - is the basic data. The last tab - Reference - is the list of categories for each test.

The second tab is what I'd like Excel to automatically do though VBA. I'd want to be able for it to have unlimited test results with out having to copy the formatting or code. I'd assume I'd have code for "Glucose" for example, apply to row 11, regardless of the column designation.

Am I wanting something impossible?

Again, I'm pretty good with Access VBA, but have never worked with Excel VBA (other than having Access manipulate Excel files).

Thanks!

Susie
Kansas

Attached Files

Cat results.xlsx (27.7 KB, 7 views)

So far, as I see it, all of this is do-able.
I'll be looking at this on and off over the next few days.
In the meantime, a few questions:
1. The results sheets all currently have exactly the same layout; everything in Column A (apart from the name in A1) is exactly the same and in the same cells. Is this set in concrete? It makes coding very easy if it is set in concrete because we know where things will always be, but a little less robust should things move about.

2. I see you've put some work into supplying the RGB values of colours; is this an industry standard type of thing or just the colours you like? I can (and it's easier) use the actual colours you've used to fill the cells in columns B & C in the Reference sheet, that way, you can tweak the colours in that sheet in the usual way until it's visually pleasing, and the code will use those colours on the results sheets. [The same RGB value in different applications often leads to very different visual colours - even within the MS Office suite!]
I'll assume you're happy with copying the colours used on the reference sheet to the results sheets (ignoring the cells with RGB numbers in)… unless I hear different from you.

3. How constant is the placing of data on the Reference sheet? Is it set in concrete?
Is there the possibility of new tests being added, more or fewer ranges within an existing test? etc.



I intend to use thresholds instead of ranges. So I won't be checking if a given value falls between values in column C and values in column D, I'll only be using something like either column B or column C to determine which range a value sits in. It's easier logically and practically to use thresholds, that way there is no danger of a value not being categorised (For example, I see Haematocrit results come with one decimal point. Let's say you receive a value of 10.3, will that go into the Critical band or the Very Severe band? Currently it falls outside both. The same problem arises with a value of 19.6). Using thresholds means you don't have to worry about checking that you have contiguous bands. Perhaps you'd like to put your mind to updating me on what those thresholds are.
For BUN, you currently have:
Low: 0 to 15
Normal: 16 to 37
High: 38 to 120

Nowhere for 15.5 to go, nor 37.2.
What I'd like to see instead is the likes of:[see next msg. too]

0
Low
16
Normal
37.5
High
120

Then I'll rearrange that into something the code (formulae too, btw) can use.
(btw, what happens above 120? Is it still 'High'? In which case do we need 120 at all? Anything above 37.5 would be 'High')

More on what I'd like you to do:
In the attached is a version of your Reference sheet.
I'd like you to fill in column B (colours too but I can probably work those out myself) and attach the file here.

You'll see I've made a stab at Phosphorous at B47, and added various formulae in column C.
To illustrate the sort of thing I'll do in code I've highlighted cell L47 in yellow where you can type in experimental values for Phosphorus, and see in M47 the kind of text that will appear in the comment box.


Would you want the functionality where if a reading is so far positive or negative that it's nigh on physiologically impossible, you could have comments to suggest checking the validity of the result?

Attached Files

msofficeforums39829ReferenceSheet.xlsx (13.7 KB, 10 views)

WOW!

I did NOT expect you to do all this work!

I figured someone would point me in the right direction and I'd go from there.

To answer your questions:

1. As far as the layout ... I would assume so, although after speaking with the vet today, I'm adding a row for the UP/C ratio. I don't think there's anything else to add. But, if I see the VBA pattern, I can make adjustments.

2. The reason I was using the RGB codes was because I have two computers and one has Excel 2010 and the other has Excel 2016. I have found that using the "stock" colors ... like "Orange, Accent 2" gives me different results on each machine. I figured RGB would keep it consistent. (I know it has to do with the themes) If there is a better way, that's fine. Oh, and the colors are arbitrary ... I just picked something meaningful (depending on how many levels there were and the severity of each level).

3. The data on the Reference Sheet can be in any placement.

4. Thresholds is the way I wanted to go but I couldn't wrap my head around it. Unfortunately, most of the research I've done only gives the "normal" value and doesn't always say what an upper (or lower) limit is. I did get some more info today for some of the tests ... as far as how concerning a particular result is and how high (or low) can the results be.

But, for most of the tests, I just have to ball park it based on many different sites or my own knowledge ... for example, the specific gravity measures whether or not the kidneys are capable of concentrating urine.

The research I've seen says that normal is 1.035 to 1.060 and that from 1.061 to over 1.085 is "over concentrated" ... well, does that mean that 1.089 is unheard of? And that results of 1.008-1.012 indicate isothenuria and the kidneys are significantly affected. What about 1.007? I don't know, but I do know that the specific gravity of water is 1.0 and that would definitely be bad, so I made a range of 1.0 to 1.007 to account for that.

In some cases the research says that for a level over X, medication should be considered .... okay, but how far over X can the level get .... If it's X.2 is that bad? what if it's 2X? In many cases, I don't know ...

I will work on the spreadsheet tomorrow.

Thank you so much!

Susie

Here is the Reference Sheet ...

Some of the levels have been adjusted based on conversation with the doctor.

Thanks!

Susie
Kansas

Attached Files

msofficeforums - ReferenceSheet.xlsx (14.1 KB, 7 views)

In the attached:
The macro blah works on the active sheet.
DO NOT RUN IT while the Reference sheet is the active sheet; it will cock things up badly. There's rudimentary code to try and prevent it running on that sheet.

Reference sheet. Make careful note of the comments in it. Note also the lowest threshold in each mini-table; you might want to alter them later.

Xander sheet. You may want to try running blah while it's the active sheet.

Roxie - End Result sheet. Left as you left it, can update by clicking the button on that sheet.

Xander (2) sheet. Two buttons, one just runs blah on that sheet. The other runs through some values. It puts those values in the cells then runs blah once each time. It will allow you to do some testing. Instructions on the button itself.


Cells which are empty have their colour and comment removed.
Cells containing a value outside the possible ranges are coloured blue and a comment added to that effect.

Attached Files

msofficeforums39829Cat results.xlsm (42.1 KB, 8 views)

Wonderful!

I'll spend some time going through the information and code to learn about it. I'm SO impressed!

Susie
Kansas