Diabetic Searches in Excel.

[George Carlisle]

I have this spreadsheet that I track my blood sugars. My blood sugar numbers fluctuate depending on what I eat and drink.

The daily spreadsheet looks like this:

At the end of the month, I pull all the data from daily sheets and consolidate in a monthly summary for discussion with my dietician. She asked for less data and wants me to just pull just the first day's blood sugar sample whatever time it is. I thought I could use a Vlookup or Hlookup or Xlookup but they require precision to function properly.

I say precision because my blood numbers can go from 0-500, and I may not take sample at the same time daily. Can some make a suggestion on how to solve this problem with Excel? Ultimately, I need to be able to present her a credible summary so we can have discussions about the month.

See the monthly spreadsheet:

If I had a FBC column, how will I get the data despite data being all over the grid/table?

[#2]

This was a manual solution I came up with but I would like something more automated.

This solution involves renaming a column to "FBC" and manually monthly just keying in the data from the table to this new column.

[#3]

@George Carlisle 

How about changing the way you record the A1c levels. Your daily example only shows two readings; I'm assuming you may only do at most three or four. A layout like this could be used to record, and it would be very easy to show your dietician the first reading only. 

[#4]

@George Carlisle 

What (for the uninitiated) is FBC? And what is the data you're proposing keying in?

Excel can do all kinds of things easily, but I (for one;  maybe it's just me) am not following what it is that you're talking about with that reference, and how it connects with the single daily blood sugar reading you've asked about.

[#5]

@mathetes     You are funny !!:)

About the blood count, in the first image at the bottom is table with the whole day by hour. I devised this since I don't know when I'll take the blood readings.

The second is a "summary" blood count from the whole month by day.

FBC = First Blood Count. Dietician wants that set out for review.

I hope this is clearer but if it isn't, you don't have to comment.

[#6]

@mathetes, my images may be too small. let me break them out better for discussion.

[#7]

This is my daily spreadsheet. This includes my food items, calories, and blood readings

[#8]

@mathetes, tell me the benefits of your spreadsheet design? I felt my design was optimized because it consolidated daily times and columns to make easier to read for a professional. I used hourly to record blood readings because I needed precision but not exact...hourly was satisfactory for medical team.

[#9]

This potential A1C sheet I designed for at-a-glance, future dax calculations, and shareability. Doctors only give patients maybe 30-40 minutes of time so this design can let them scan and consult.

[#10]

I can't see it clearly (too small), but if each of those columns is A1c data, that looks fine. My format was essentially the same idea, one row per day, with the separate readings separated with space for time (I left those Time fields blank, figuring you knew how you wanted to record them). If it were mine, I'd make the hours drop down menu items, with the options you have In your vertical array.

Basic point, though, a database in Excel is typically based on rows of data for days, or for days and times.....without lots of blank spaces. Your A1c original has blank spaces predominating over data.

[#11]

@mathetes 

Maybe I need to attach as file not post as image. See attachment.

I completed late last night. I put field on daily and referenced from other monthly a1c. Dropdowns are too time consuming.

[#12]

@George Carlisle 

Try this. I disagree with you regarding drop downs. They make for consistency--via their "data validation" usage; as I've done here they save a lot of space (file space). If consistency doesn't matter, but you still want to record time of testing to some degree of precision, you could just enter per your own comfort.

Anyway, it's your database, so do what seems right to you. Best wishes for controlling your condition.

[#13]

@mathetes, My comment was not to be taken personally. I hear your comments about validation but my data is not fixed and the sheet WILL change in the future. Formulas I can update faster. Dropdowns must be individually edit. I made changes to hundreds of sheets at the same time with formulas, I cannot do that with dropdowns in Excel...yet.

[#14]

@George Carlisle 

No worries. I didn't take it personally. We just approach design differently.