Hey Peter,

Your right, it can't be done without appending the data and formulas
to each item.

But I am still at a loss as to how to speed up these 30 min cycle
times.

Any Ideas?

-Minitman

On Sat, 24 Apr 2010 11:14:54 +0100, "Peter T" <peter_t@discussions
wrote:

You could certainly write a table able the lines of "what you are aiming to
see" in a cell comment. Thereafter, in theory at least, extract data from
the comment table for use in some other code. Personally I wouldn't do it
that way. Better to keep all your data in cells, normal Lookups and/or VBA
code to process etc., which if I gather correctly is the way you did it
before. No problem to have a bit of code to populate comments the way you
want them purely for visual purposes

Is your data stored in table form per food item (along the lines you propose
for your comments. If it is, I'd suggest arranging it as one row of data per
food item, ie food-name and 36 columns of data. You will probably want a
data entry form or better still a userform with say 3x12 text boxes in which
to enter new food data, and/or read existing food data and update the food
data if/as necessary.

It would be easy enough to have 3x12 VLookups to display the row of 36 data
items in a 3x12 matrix. However you might not even need that (other than for
display). It should be possible for you to have a very simple (as far as
user is concerned) data entry system something along these lines

Enter Food-name (say from dropdown list), qty, date/time.
(data could be entered cells or a userform)
Press a button

Code to update date sheet with food name, qty, the 12 items associated with
the food name, in turn totalled for the day.

Though this might not answer your question directly, maybe it is food for
thought!

Regards,
Peter T


"Minitman" wrote in message
.. .
I have diabetes and my doctor wants to see what foods I am eating, as
well as how much and how often. I built a spreadsheet to record all
of this information.

In a data sheet called "FoodList", I entered the 12 categories of
nutrition that the FDA requires all prepackaged foods sold in the US.

On the log sheets, each of these categories is split into 3 columns
(1-Category values of the food in the description column I call an
"ITEM", 2-Add all "ITEM" values listed in the same date-time I call a
"MEAL" and finally 3-Add all of the "ITEM" values that have the same
date I call this "DAILY TOTAL")

I used VLookUps to get the data for the category "ITEM" into the log
sheet. The VLookUps values are adjusted by a ratio of the log sheets
actual size divided by the data sheets standard size.

I have formulas to do all of this.

I had to delete all of them (the formula cells not the sheets - and I
still have the back-ups)!

After filling up a few months of log sheets, the cycle time for each
entry was approaching 30 min. I did everything I could think of to
clean up and speedup my code. Finally I had to admit, my whole
approach was flawed, so I have gone back to the drawing board.
I was thinking of using the cell comment to display the 12 USDA facts
in an array appearing format (not an ARRAY as defined in Excel)

I still have a data sheet called "FoodList", which has about 8000 rows
with 13 columns (it is still growing). I have a lot of monthly log
sheets labeled as dates in the format of "Mmm-yy" starting with
"Nov-06" up to present.

Without actually naming each FDA nutritional item, this is what I am
aiming to see in a comment box:
_______________________________
FDA | ITEM | MEAL | DAILY |
ITEMS | VALUE | TOTAL | TOTAL |
Item 01 |________|________|_______|
Item 02 |________|________|_______|
Item 03 |________|________|_______|
Item 04 |________|________|_______|
Item 05 |________|________|_______|
Item 06 |________|________|_______|
Item 07 |________|________|_______|
Item 08 |________|________|_______|
Item 09 |________|________|_______|
Item 10 |________|________|_______|
Item 11 |________|________|_______|
Item 12 |________|________|_______|

Is this possible?

Thanks for your interest and help.

-Minitman

On Fri, 23 Apr 2010 19:13:12 +0100, "Peter T" <peter_t@discussions
wrote:

I don't understand, describe your overall objective, if necessary with
example. Otherwise it's just a guessing game.

Regards,
Peter T

"Minitman" wrote in message
...
Hey Peter,

Thanks for the reply

I'm not sure how to convert your code from an absolute reference to a
relative reference. I need to insert this code into an entire column.

How do I do this?

-Minitman


On Fri, 23 Apr 2010 10:38:47 +0100, "Peter T" <peter_t@discussions
wrote:

Not sure about VBA code but something like this might -

Sub test()
Dim sFml As String
Dim cm As Comment
sFml = "A1*4+A2"

Set cm = Range("A1").Comment
If cm Is Nothing Then
Set cm = Range("A1").AddComment
End If

cm.Text sFml

Range("A1").Value = 20
Range("A2").Value = 10

Range("C3").Formula = "=foo()"
Application.CalculateFull

End Sub

Function foo()
On Error GoTo errH

foo = Evaluate(Range("A1").Comment.Text)
Exit Function
errH:
foo = CVErr(xlErrValue)
End Function

Might need to press Ctrl-Alt-F9 after manually editing the comment

Regards,
Peter T

"Minitman" wrote in message
om...
Greetings,

Is it possible to run formulas or vba code inside of a cell comment
box?

If so, how?

-Minitman