Sergiy asks if there is any way to have a cell comment or a validation input box reflect a value from a formula in a cell. I couldn't find a good solution, other than using VBA. Episode 855 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Question today from Sergey, and I can't quite solve it-- I can't find a satisfying answer.
Sergey says, "Hey, you know how we have cell comments?" So, we do Insert, Comment, and you have a little, you know, some text that pops up every time that someone hovers over the comment.
He says, "That's pretty cool, but how can I make that text be dynamic, to grab the value from another cell or, you know, a formula on the worksheet?" And, you know, I tried a few things: I tried to go in and set up the comment to point to another cell and that didn't work; I even tried to edit the comment; and then tried all sorts of things here, like you can set an auto shape up to show a formula; and I even tried to paste the camera tool into here, and just simply couldn't find a good way to have that work.
Now, you know, the auto shape-- you can have a regular object and, basically, in here, type a formula of =G6, and so that will update every time we recalculate the spreadsheet.
It will update what's in the text box.
I thought, well, you know, if we could access that text box in the comment and, you know, have it point to a formula-- but that just didn't seem to work.
So, I guess I'm kind of left with a Macro.
In the Macro, we actually came into the code pane for this worksheet.
From the top left drop-down, choose Worksheet; from the top right drop-down, choose Calculate; and then, what I did here, is I used the input message.
So, enter Validation.InputMessage, and so that it's equal to a certain value.
So, basically, what happens is, every time we press the F9 key, the worksheet recalculates and then this cell down here is updated, so it's 10:12:54.
If I would press F9, and then click in again, the tool tip that pops up is 10:13:03.
We could use the same concept to change the comment code-- every time that something calculates, it goes in and updates it.
And it doesn't seem to be a huge lag.
I mean, we can go through and enter numbers.
So we just recalculated the spreadsheet five times-- that macro ran five times-- and there wasn't any visible flash or anything like that.
So, you know, I'm not satisfied with this, we had to use some VBA to do it.
If anyone knows a way to actually get to that comment box and change dynamically, please drop me a note and we'll talk about that next week.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
Question today from Sergey, and I can't quite solve it-- I can't find a satisfying answer.
Sergey says, "Hey, you know how we have cell comments?" So, we do Insert, Comment, and you have a little, you know, some text that pops up every time that someone hovers over the comment.
He says, "That's pretty cool, but how can I make that text be dynamic, to grab the value from another cell or, you know, a formula on the worksheet?" And, you know, I tried a few things: I tried to go in and set up the comment to point to another cell and that didn't work; I even tried to edit the comment; and then tried all sorts of things here, like you can set an auto shape up to show a formula; and I even tried to paste the camera tool into here, and just simply couldn't find a good way to have that work.
Now, you know, the auto shape-- you can have a regular object and, basically, in here, type a formula of =G6, and so that will update every time we recalculate the spreadsheet.
It will update what's in the text box.
I thought, well, you know, if we could access that text box in the comment and, you know, have it point to a formula-- but that just didn't seem to work.
So, I guess I'm kind of left with a Macro.
In the Macro, we actually came into the code pane for this worksheet.
From the top left drop-down, choose Worksheet; from the top right drop-down, choose Calculate; and then, what I did here, is I used the input message.
So, enter Validation.InputMessage, and so that it's equal to a certain value.
So, basically, what happens is, every time we press the F9 key, the worksheet recalculates and then this cell down here is updated, so it's 10:12:54.
If I would press F9, and then click in again, the tool tip that pops up is 10:13:03.
We could use the same concept to change the comment code-- every time that something calculates, it goes in and updates it.
And it doesn't seem to be a huge lag.
I mean, we can go through and enter numbers.
So we just recalculated the spreadsheet five times-- that macro ran five times-- and there wasn't any visible flash or anything like that.
So, you know, I'm not satisfied with this, we had to use some VBA to do it.
If anyone knows a way to actually get to that comment box and change dynamically, please drop me a note and we'll talk about that next week.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
