Formulas In Cell Comments

Greetings,

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

If so, how?

-Minitman
0
Minitman
4/23/2010 9:07:41 AM
excel.programming 6508 articles. 2 followers. Follow

9 Replies
381 Views

Similar Articles

[PageSpeed] 13

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" <steve@minitmaidsofaustin.com> wrote in message 
news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax.com...
> Greetings,
>
> Is it possible to run formulas or vba code inside of a cell comment
> box?
>
> If so, how?
>
> -Minitman 


0
Peter
4/23/2010 9:38:47 AM
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" <steve@minitmaidsofaustin.com> wrote in message 
>news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax.com...
>> Greetings,
>>
>> Is it possible to run formulas or vba code inside of a cell comment
>> box?
>>
>> If so, how?
>>
>> -Minitman 
>

0
Minitman
4/23/2010 4:29:27 PM
I don't understand, describe your overall objective, if necessary with 
example. Otherwise it's just a guessing game.

Regards,
Peter T

"Minitman" <steve@minitmaidsofaustin.com> wrote in message 
news:p223t5pu8mdq8cpo10t722cm2bb8u7vlps@4ax.com...
> 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" <steve@minitmaidsofaustin.com> wrote in message
>>news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax.com...
>>> Greetings,
>>>
>>> Is it possible to run formulas or vba code inside of a cell comment
>>> box?
>>>
>>> If so, how?
>>>
>>> -Minitman
>>
> 


0
Peter
4/23/2010 6:13:12 PM
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" <steve@minitmaidsofaustin.com> wrote in message 
>news:p223t5pu8mdq8cpo10t722cm2bb8u7vlps@4ax.com...
>> 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" <steve@minitmaidsofaustin.com> wrote in message
>>>news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax.com...
>>>> Greetings,
>>>>
>>>> Is it possible to run formulas or vba code inside of a cell comment
>>>> box?
>>>>
>>>> If so, how?
>>>>
>>>> -Minitman
>>>
>> 
>

0
Minitman
4/24/2010 12:00:51 AM
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" <steve@minitmaidsofaustin.com> wrote in message 
news:ui54t5l8sh6uos3rrkll82t2b3h5q2tcb8@4ax.com...
>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" <steve@minitmaidsofaustin.com> wrote in message
>>news:p223t5pu8mdq8cpo10t722cm2bb8u7vlps@4ax.com...
>>> 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" <steve@minitmaidsofaustin.com> wrote in message
>>>>news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax.com...
>>>>> Greetings,
>>>>>
>>>>> Is it possible to run formulas or vba code inside of a cell comment
>>>>> box?
>>>>>
>>>>> If so, how?
>>>>>
>>>>> -Minitman
>>>>
>>>
>>
> 


0
Peter
4/24/2010 10:14:54 AM
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" <steve@minitmaidsofaustin.com> wrote in message 
>news:ui54t5l8sh6uos3rrkll82t2b3h5q2tcb8@4ax.com...
>>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" <steve@minitmaidsofaustin.com> wrote in message
>>>news:p223t5pu8mdq8cpo10t722cm2bb8u7vlps@4ax.com...
>>>> 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" <steve@minitmaidsofaustin.com> wrote in message
>>>>>news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax.com...
>>>>>> Greetings,
>>>>>>
>>>>>> Is it possible to run formulas or vba code inside of a cell comment
>>>>>> box?
>>>>>>
>>>>>> If so, how?
>>>>>>
>>>>>> -Minitman
>>>>>
>>>>
>>>
>> 
>

0
Minitman
4/25/2010 9:38:50 AM
"Minitman" <steve@minitmaidsofaustin.com> wrote in message
> Hey Peter,
>
> Your right, it can't be done without appending the data and formulas
> to each item.

Not sure I follow, you might need one set of 36 Lookups all with the same 
food-item lookup (rearranged into say a 3x12 matrix), but not a seperate set 
for each food item.

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

I'm not sure what you mean, what's a cycle time, excercise?

Regards,
Peter T


>
> 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" <steve@minitmaidsofaustin.com> wrote in message
>>news:ui54t5l8sh6uos3rrkll82t2b3h5q2tcb8@4ax.com...
>>>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" <steve@minitmaidsofaustin.com> wrote in message
>>>>news:p223t5pu8mdq8cpo10t722cm2bb8u7vlps@4ax.com...
>>>>> 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" <steve@minitmaidsofaustin.com> wrote in message
>>>>>>news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax.com...
>>>>>>> Greetings,
>>>>>>>
>>>>>>> Is it possible to run formulas or vba code inside of a cell comment
>>>>>>> box?
>>>>>>>
>>>>>>> If so, how?
>>>>>>>
>>>>>>> -Minitman
>>>>>>
>>>>>
>>>>
>>>
>>
> 


0
Peter
4/25/2010 10:44:40 AM
Thanks Peter for your help.  You made me take a long hard look at what
I was asking for and realize my basic approach was flawed.    

 I have to store the VLookUp information (only 12 columns needed) as
values on the log sheet (like what you suggested).  But rather then
using VLookUp as paste down formulas (as in my original approach), I
need to modify the posting sub in my UserForm (which already has the
VLookUp information on one of the MultiPage pages, just not the log
entry page, but it is there).  My original attempt was to make the
entire row a kind of display (not very usable).  This approach should
eliminate a lot of the calculation problems and time delays I've been
experiencing.

The rewriting of the User Form code to make these changes is fairly
straight forward, if not a little time consuming.  

I then will be ready to attempt to set-up the comment box as my
display method.

Do you have an example of how to make a comment box with vba showing
the value of the column L in the row just loaded from the UserForm, in
the first column of the comment box.  

For the second column, I need a formula something like this where
column K is the result of combing the day value in column A with the
time value in column B (I could not see how to do the SUMIF without
the helper column.  Perhaps you see a better way?):

=SUMIF(K:K,K{current row})   

For the third column, something like this:

=SUMIF(A:A,A{current row})

Current row refers to the row of the cell just triggered by the mouse
over event.

Any help is greatly appreciated.

-Minitman

P.S. - I can send you a sample of my workbook if you would like.  If
so, please send me an email (my email address is not hidden).


On Sun, 25 Apr 2010 11:44:40 +0100, "Peter T" <peter_t@discussions>
wrote:

>
>"Minitman" <steve@minitmaidsofaustin.com> wrote in message
>> Hey Peter,
>>
>> Your right, it can't be done without appending the data and formulas
>> to each item.
>
>Not sure I follow, you might need one set of 36 Lookups all with the same 
>food-item lookup (rearranged into say a 3x12 matrix), but not a seperate set 
>for each food item.
>
>> But I am still at a loss as to how to speed up these 30 min cycle
>> times.
>
>I'm not sure what you mean, what's a cycle time, excercise?
>
>Regards,
>Peter T
>
>
>>
>> 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" <steve@minitmaidsofaustin.com> wrote in message
>>>news:ui54t5l8sh6uos3rrkll82t2b3h5q2tcb8@4ax.com...
>>>>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" <steve@minitmaidsofaustin.com> wrote in message
>>>>>news:p223t5pu8mdq8cpo10t722cm2bb8u7vlps@4ax.com...
>>>>>> 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" <steve@minitmaidsofaustin.com> wrote in message
>>>>>>>news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax.com...
>>>>>>>> Greetings,
>>>>>>>>
>>>>>>>> Is it possible to run formulas or vba code inside of a cell comment
>>>>>>>> box?
>>>>>>>>
>>>>>>>> If so, how?
>>>>>>>>
>>>>>>>> -Minitman
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>> 
>

0
Minitman
4/26/2010 4:06:13 PM
OK I'll have a look at your workbook off-line though I'm a little concerned 
I'll end up needing to remake it from scratch.

One thing though please, and don't take this the wrong way, before sending 
any email read it back and ask yourself if someone who doesn't know what you 
know, will be able to understand your explanation / question. For example in 
your message below you asked several questions. I'm sure they are all fairly 
straightforward but I really haven't got a clue about the details of any of 
them and hence how to answer them. It's been rather like that with some of 
your previous questions.

My address is in the reply-to field, lightly disguised.

Regards,
Peter T


"Minitman" <steve@minitmaidsofaustin.com> wrote in message 
news:p80bt59jqd9bauh0c302m0kqb56md5mmfv@4ax.com...
> Thanks Peter for your help.  You made me take a long hard look at what
> I was asking for and realize my basic approach was flawed.
>
> I have to store the VLookUp information (only 12 columns needed) as
> values on the log sheet (like what you suggested).  But rather then
> using VLookUp as paste down formulas (as in my original approach), I
> need to modify the posting sub in my UserForm (which already has the
> VLookUp information on one of the MultiPage pages, just not the log
> entry page, but it is there).  My original attempt was to make the
> entire row a kind of display (not very usable).  This approach should
> eliminate a lot of the calculation problems and time delays I've been
> experiencing.
>
> The rewriting of the User Form code to make these changes is fairly
> straight forward, if not a little time consuming.
>
> I then will be ready to attempt to set-up the comment box as my
> display method.
>
> Do you have an example of how to make a comment box with vba showing
> the value of the column L in the row just loaded from the UserForm, in
> the first column of the comment box.
>
> For the second column, I need a formula something like this where
> column K is the result of combing the day value in column A with the
> time value in column B (I could not see how to do the SUMIF without
> the helper column.  Perhaps you see a better way?):
>
> =SUMIF(K:K,K{current row})
>
> For the third column, something like this:
>
> =SUMIF(A:A,A{current row})
>
> Current row refers to the row of the cell just triggered by the mouse
> over event.
>
> Any help is greatly appreciated.
>
> -Minitman
>
> P.S. - I can send you a sample of my workbook if you would like.  If
> so, please send me an email (my email address is not hidden).
>
>
> On Sun, 25 Apr 2010 11:44:40 +0100, "Peter T" <peter_t@discussions>
> wrote:
>
>>
>>"Minitman" <steve@minitmaidsofaustin.com> wrote in message
>>> Hey Peter,
>>>
>>> Your right, it can't be done without appending the data and formulas
>>> to each item.
>>
>>Not sure I follow, you might need one set of 36 Lookups all with the same
>>food-item lookup (rearranged into say a 3x12 matrix), but not a seperate 
>>set
>>for each food item.
>>
>>> But I am still at a loss as to how to speed up these 30 min cycle
>>> times.
>>
>>I'm not sure what you mean, what's a cycle time, excercise?
>>
>>Regards,
>>Peter T
>>
>>
>>>
>>> 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" <steve@minitmaidsofaustin.com> wrote in message
>>>>news:ui54t5l8sh6uos3rrkll82t2b3h5q2tcb8@4ax.com...
>>>>>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" <steve@minitmaidsofaustin.com> wrote in message
>>>>>>news:p223t5pu8mdq8cpo10t722cm2bb8u7vlps@4ax.com...
>>>>>>> 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" <steve@minitmaidsofaustin.com> wrote in message
>>>>>>>>news:tio2t5lfrdc4gn5goso0ligdp9psib9ofu@4ax.com...
>>>>>>>>> Greetings,
>>>>>>>>>
>>>>>>>>> Is it possible to run formulas or vba code inside of a cell 
>>>>>>>>> comment
>>>>>>>>> box?
>>>>>>>>>
>>>>>>>>> If so, how?
>>>>>>>>>
>>>>>>>>> -Minitman
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
> 


0
Peter
4/26/2010 8:15:01 PM
Reply:

Similar Artilces:

IF FORMULA 04-06-10
Hi: I am trying to write an if formula that if B2 is equal to a specific date in a range of dates then it is the end of a pay period, if B2 is equal to a date in a second range of dates then it state pay day. I have set up two tables with dates. One has pay period ending dates and the other table has pay day dates. I keep getting a Value error. Please help. -- Donna =IF(COUNTIF(A1:A10,B2),"end of pay period",IF(COUNTIF(C1:C10,B2),"pay day","")) Change ranges to suit. -- Regards Dave Hawley www.ozgrid.com "Donna" ...

deselect cells within a range of cells
I have found this to be most frustrating!!! Windows Explorer allows you to select an entire list of items and then deselect individual items by holding the ctrl key down and clicking the items you wish to deselect. Why does this not work in Excel??????? I have a list of 100 items and wish to deselect about 10 that are randomly dispersed in the selection. This seems to such a simple thing, but I've found no solution. Try this: =INDIRECT("E"&C1) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all m...

Specifying next cell to "jump" to
When a user completes as much of a specified cell range as necessary an wishes to move on to the next range, I would like to give them a option to go "automatically" to the first cell of the next range a opposed to having to tab through the remainder of the range or click i the first cell of the range they wish to go to. For example, the firs range is A10 through D25. After inputting data in D15, the user want to go to the next range, the first cell of which is A30. How can th user most quickly and easily go from D15 to A30? I appreciate your help -- Message posted from http://...

"De-name" cell references in formulas
Hello, I'm working in Excel 2003 SP2, and I'm trying to replace all references to named ranges used within my formulas within a workbook with the underlying absolute cell references. I found a solution in the archives of this newsgroup ( http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/4ebb9d1ee0076045/a0d6406155d03ccc#a0d6406155d03ccc ) , but I can't get it to work (neither the manual version nor the macro version). My only guess is that the behavior has of TransitionFormEntry has changed in Excel 2003. If so, is anyone aware of any other solutions. I found...

Locking certain cells
I have an expense reporting template which I have locked certain cells that I do not want changed while allowing input into other cells. This is under the tools functions but its not coming to me right away how I did this. The users do not need to use any passwords --its just certain cells are read-only and cannot be changed unless unlocked. How'd I do this I need to update? Disregard- figured it out again "Scott" wrote: > I have an expense reporting template which I have locked certain cells that I > do not want changed while allowing input into other cells. Th...

IF number is grather than 100 color the cell red
I did this before but I can't remember how I did it. I want to have a column of numbers and if they are greater than a certain value ie: 120 , I want the sell to have the color red. Any ideas how to do this in Excel 2007? THanks Take a look at format|conditional formatting. Ron Boetger wrote: > > I did this before but I can't remember how I did it. I want to have a > column of numbers and if they are greater than a certain value ie: 120 > , I want the sell to have the color red. > > Any ideas how to do this in Excel 2007? > > THanks -- Dave Peterson ...

IF formula to round up values depending upon their outcome
I am using an IF formula to calculate between two cells, one is J (width) the other is K (length). Currently these formulas give an answer that then has to be rounded up based on the decimal place. I need the formula to also round up the amount to quarter increments. For example if the answer is 1.17 then the formula needs to make it 1.25, if it is 1.33 then the formula needs to make it 1.5, and finally if it is 1.63 then the formula needs to make it 1.75. So how do I add or make the formula round up to quarter increments? The formula that I am using is: =IF(J41<=3,K41/4,IF(J4...

Removing Excess Digits From Cells
I have two lists of numbers that I'd like to use to join two tables in Access. One list has an extra "Check Digit" at the end. I'd like to know how to remove the "Check Digit". (It may not matter but one of the characters in the list of "numbers" is a letter.) I tried to "Format Cells" by typing in one less ###-##-#### but it did not do anything. Hi Raquel, try using the formula =LEFT(E6;1) for a column next to the list and use E6 for every cell... so you get rid of the last digit. Best Markus >-----Original Message----- >I hav...

Copying Info Into Numerous Cells
I have just completed creating and entering data into thousands of worksheets. Now someone has decided they want to change and add columns to my worksheets. Is there any way to have data copied from 1 cell to another in several worksheets at a time, even if the data is different? If there isn't...I'm going to have a melt-down right here at work! :eek: -- calimari ------------------------------------------------------------------------ calimari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24537 View this thread: http://www.excelforum.com/showthr...

calculation formulas
Since today I am facing some peculiar change in my excel files. When I drag a formula from one cell to another cell, result of the first cell itself are reflected in the subsequent cells. Then I have to press F9 key so that all cells give proper calculation of the formulas. E.g Product Price Qty Value A 2 10 20 B 5 15 20 C 10 20 20 D 4 2 20 E 6 5 20 I did the above table in exel. Cell D2 was the result of multiplication of B2 & C2. Thats is correct. The I dragged the formula from D2 to D6. However in this res...

Formatting lost when copied by formula!
Hi, I'm having a problem retaining format in a target cell where a formul "copies" the contents from another cell. Here's an example tha describes the problem in detail: Say I have the following text in cell A1: "Some text." Say, also, tha the word "text" is bolded. In cell B2 I have a formula, =A1, tha "copies" the content of cell A1. I can see the content of cell A1 i B2, but without the bold (or any other formatting, including backgroun color, etc.). My question, of course, is how do I retain the original formatting? Thank you -- Message ...

Formula #23
I have some data in column A : 100, 102, 105, 110 I have data in cell B1 : 103 I want to check whether value 103 comes in range 100, 102, 105, 110. Here in the given case it has come as value 105 appears which include 103. I need a formula to check when value 103 arrives or arrived in tha range. in given case it arrived at 105. Please help me. Thank -- Message posted from http://www.ExcelForum.com Hi not quite sure but maybe =IF(AND(B1>=MIN(A1:A10),B1<=MAX(A1:A10)),"within range","out of range") -- Regards Frank Kabel Frankfurt, Germany > I have some d...

Can I use a worksheet name in a formula?
I'm using Office 2007 I have a workbook with 20+ pages. On one of them I want to use a formula that refers to a worksheet name, but in different cells I want it to refer to different worksheets. I have a cell that currently has a name typed into it that matches a worksheet name. Can I refer to the contents of that cell (INDIRECT) and use the content to refer to the correct worksheet and then go to that worksheet for the data? eg. Sheet called Christmas. In cell H2 value = Pudding on another sheet cell B2 value = Christmas. I want the formula in cell J12 to go to Sheet C...

How to select a sheet and input data into certain cells
I have 36 sheets, sheet1 is my menu, also on sheet1 I created in colum AB1=1stQTR AC1=2ndQtr AD1=3rdQTR AE1=4thQT AA2=2004 Sheet2 Sheet3 etc . . . AA3=2005 Sheet6 Sheet AA4=2006 Sheet8 Sheet AA5=2007 Sheet4 Sheet I would like to be able to select a year and a quarter and it goes to the sheet, i.e 2005 2ndQtr goes to sheet5 and in a couple of cells input 2005 and 1st Quarter I would also like to be able to print sheet5 and ask me if I want to print it agai or close and upon closing go back to sheet Is there an...

Formula to calculate and add tax
I am creating a simple (I thought) pricing guide and I am running int the circular reference problem. Here's what I want to do. I want t enter a price in a cell and have the tax automatically calculated an the total price returned to the cell. I know I can create an entirel new set of cells just to contain the price and the tax rate then hav that result displayed in the desired cell. But, I wondered if it i possible to enter the price directly into the cell. For example: In A1 A2 A3 I want to be able to enter the price before tax an then have the tax calculated and the total displa...

Inserting extra info into a cell
I currently have three columns in a spreadsheet. This spreadsheet is used as a bilingual glossary of terms. The first column simply contains the following: ** Now the second column contains my Spanish words and the third column my English words. So, it looks something like this:- ** Hola Hello Now, I wish to import this data into some new software I have however, it requires me to put the word <Spanish> before every words in the second column and <English> before every word in the third column. So, in essence, it will be ** <Spanish>...

export query with formulas to excel
Is there a way to export my Access query to Excel 2003 while keeping the formulas in Excel. For example, in my Access query I have a field called "Other" which is defined as the following: Other: [DiffTotal]-[BudTo] [DiffTotal] and [BudTo] are "real" fields in the query and become columns in Excel. Instead of having the value of "Other" appear in Excel, I'd like the "Other" column to have the formula where, for example in row 1, it could look like "=A3-A2." Thanks, ~Mark When Access exports to Excel, it exports the...

Formula to Identify groups of numbers to be added.
Hoping I can get a cell formula that will calculate as follows: Cells in Column H have this relative formula in them. =3DIF(M12=3D"Yes", "",C12) This results in a cell will either show a number, or is blank. Each week or so, another entry is made to the next empty line. After awhile, Column H might look something like this: Number are in cells H12 =96 H14 There is a blank at H15. Then numbers in cells H16-H19, and a blank at H20 Column H Line12 13.89 Line 13 7.89 Line 14 6.14 Line 15 Blank cell (this shows up as blank cell) Line 16 ...

Array formula to Identify Leading Zeros.
I have a bunch of spreadsheets that have a sizable number of columns - usually about 128 columns. And I need to identify those columns that have leading zeros in them. Logically, If I knew how many cells had a zero in the left most position, I could tell which column had a leading zero. But with 128 columns and 500-12000 rows, this is too hard to just eyeball. I think an array formula will do the trick - but I'm not having much luck getting one to work. For example, in column B, cells B7:B478, I've tried something like: {=sum(countif(B7:B478,left(B7:B478,1)=0))} Am ...

Conditional Formatting Across Multiple Cells
I have data file that I want to conditional format Red/Green fill based on >= conditional on a single row of data. In Excel 2003 it was fairly simple to lock a row but not a column and by selecting all the cells to format it would change the column relative to the cell. Just having an issue transitioning to conditional formatting in 2007. Sample Data a b c d 1 goal 10 245 125 2 day1 8 200 76 3 day2 8 250 125 4 day3 15 250 130 5 day4 15 300 150 6 day5 0 100 0 7 Avg 9 220 96 Cells Rows 2 - 7 should be conditional on Row ...

Ignoring blank cells in a combo box...
Is there any way I can ignore blank cells when using a combo box fro the forms menu? For instance, my combo box is being filled by th range A1:A5. However, if A3 is blank, I don't want it ( a blank) t show up in the drop down box. Is there is a more efficient way to d this or it might it be more simple to use a combo box from the contro toolbox? Any help would be appreciated. Thanks -- Message posted from http://www.ExcelForum.com The combobox from the Forms toolbar is also called a DropDown. And you could use a little code that filled up that dropdown: I chose to put it into the...

Odd Excel Formula Problem
I am building a spreadsheet tool that pulls data from either another sheet or another workbook. In both cases, when I write my formulas, the seem to work initially, but at some point something happens and instead of displaying the result of the formula, they display the formula as text. For example: In Cell A1 I type =if($A$2=$A$3,1,0) initially it display's my desired result ("0"). Then, if I go in and try to unlock the cells to copy down and across the formula result changes to "=if($A$2=$A$3,1,0)" I have never seen this error and can't figure...

Trouble with protected sheets & formulas
Hello, I have a macro which operates on a worksheet that I have protected. There are some ranges in the worksheet which I have both locked an hidden and there are others for which only the formulas are hidden (bu the cells are not locked). My macro needs to work on some cells (enter formulas, etc.) where wish to hide the formulas the macro enters. These ranges I hav designated as hidden but not locked. The problem is, whenever I run my macro (which autofills for a give universe), I only have an accurate calculation for the first row o formulas entered in these hidden (but not locked) rang...

Return a number in one cell to long hand text in another.
Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents Take a look at http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP "CP" <CP@discussions.microsoft.com> wrote in message news:C0D9B8E3-3D66-4EA0-9184-57762BD663D6@microsoft.com... > Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents Go to Google and search the newsgroups for "spell number" On Wed, 20 Oct 2004 15:07:02 -0700, "CP" <CP@discussions.microsoft.com> wrote: >Ie. 123.65 to One hundred, Twenty Three & Sixty Five Cents ...

Locking cells #3
hi there, I was wondering if it is possible to Lock a cell so that no-one ca enter data into it, the reason behind this is because the cell i update from a different worksheet. Thank you for your time Kind Regards Swmasso -- Message posted from http://www.ExcelForum.com Hi It's possible. Simply protect the sheet - by default all cells are locked then. When you want to allow users to access some cells, unlock them before protecting the sheet (Format.Cells.Protection). When you want to restrict the access to some cells only, and to allow for rest of worksheet, then unlock all cells ...