Is there a way for the Cell("Contents") function monitor a specific column of cells?
For example, in cell A1 I have the formula
and the data to monitor changes in would be in column B, D or E, display this value in A1 with the stated formula. I thought maybe the Cell() be nested within an If function.
=Cell("contents")
So you want to use a specific cell, let's say A1 to monitor a specific column (let's say B). What are we monitorinig it for? Changes? If there is a change, what do you want A1 to say/do? What if there are multiple changes? Are we talking about manual changes or changes due to formulas? (for example in B7 is a formula = C1+D1 and someone changes D1 which causes B7 value to change)?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I think you are overcomplicating it. I want Cell("contents") to perform the way it would normally do but limit it to columns A, B and C. The way Cell("contents") normally behaves is, if you type anything in any cell other than the one with Cell() function in the same sheet will display the same value/text as where the last cell was written.
If I wrote "hi" to cell D7, A1 would also display "hi". If I then proceeded to write "hello" to cell F2, A1 would display "hello". I would like to have the Cell("contents") function limited to specified columns. I played around with if, countif and ranges but not sure if I can approach the Cell() function in this manner.
Oh I see what you mean. If there are two cells within a column that may have a formula that reference the same cell and both change at once.... I see... I do not think this will be the case. I am having drop down lists for several columns with no formulas as far as I am envisioning.... The end user, or anyone for that matter, cannot simultaneously click and select multiple dropdown list items unless I've mistaken.Originally Posted by ChemistB
You can't do what you are describing with a worksheet function, Cell or otherwise.
You would need a VBA Change event handler monitoring changes to the specific range you require. Ideally, that range should be kept to a minimum.
It would look something like this:
' Module: Sheet1 Class Module Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rInterest As Range, rValue As Range Set rInterest = Me.Range("A1:C20") Set rValue = Me.Range("A1") If Intersect(Target, rInterest) Is Nothing Then Exit Sub Application.EnableEvents = False If Target.Cells.Count = 1 Then rValue.Value = Target.Value Else rValue.Value = "Multiple changes" End If Application.EnableEvents = True End Sub
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
T86157 
Register To Reply
Bookmarks