See Attachment



In the Q1 table, I have three fields related to the Glucose reading.
(D) Libre reading (continuous monitoring sensor)

(E) Blood reading
(F) Calibrated Glucose value


I divide the blood reading by the Libre reading to produce a calibration factor. This factor is stored in the "Glucose Factor" table by start date. This calibration factor varies from one sensor to the next. Each sensor lasts 14 days. I need to re-calibrate each new sensor after it is installed. The factor usually varies +- around 1.0. The factor for several of the previous sensors was .9x. The factor for the current reading is 1.03.



The Glucose Column contains a formula which looks up the factor in the "Glucose Factor" table and multiplies the Libre reading by that factor for the current date to produce the calibrated Glucose result.



As you can see in row 3, the formula is not producing the proper result. The calibrated Glucose reading should be near 220 (219 in this case). However the formula is producing a result for a factor of .95. This is the same factor for row 29 in the Glucose Factor table beginning on 21 Jul 2023.



For some reason, the VLookup function is no longer returning the correct value. It has been working for over a year. Is there a limit to the number of rows that can be in the Glucose Factor table? What has happened between Jul and Sep?


FYI: Row 2 is hidden to preserve formatting when I insert new rows at the top.

Attached Files

Diabetes Manager.zip (873.7 KB, 4 views)

The formula in column F refers to a named range GlucoseFactor. There are in fact 2 such named ranges, one Workbook-scoped and one Sheet-scoped. One refers to:
='Glucose Factor'!$A$2:$B$29
the other to:
='Glucose Factor'!$A$2:$B$32
I suggest you change the formula to:
=IF([@Libre]=0,[@Blood], IF([@Libre]>0,[@Libre]*VLOOKUP([@Date],Table4,2),0))

On sheet Glucose Factor you have a Defined Table Table4, which expands automatically whenever you add new rows to this table. You also have defined a Named Range ClucoseFactor='Glucose Factor'!$A$2:$B$29, which defines a range of table on sheet Glucose factor from rows 2:29. This named range is fixed one - remains same when you add new glucose factor values. In your formula, you refer to this fixed range! Replace it with reference to Defined Table instead (like p45cal adviced)!
And you have more than one copy of Named Ranges there - with scope 'Workbook', and with scope 'Prt Date Rng'. A possible explanation is, you created the sheet 'Prt Date Rng' coping some other sheet where those named ranges were used in formulas (or did some other copy-paste operation which had this effect). Probably you have to delete all Named Ranges with worksheet scope (Except the ones used to define Print Areas) to avoid some problems in future (I suspect when the scope is a worksheet, you can refer to this range from this worksheet only, and as both copies of range have same name, it is up to Excel to decide, which one is referred to!).