Formula to Determine if Cell Value is from a Keyed Value or a Form

  • Thread starter Thread starter JG
  • Start date Start date
J

JG

Is there a formula that can tell me whether a cell's value is a keyed value
(probably a number, if that helps) or if it was determined via a formula.

What I'm doing: I'm building an estimation tool and I want to identify
where manual tweaks have been made (don't want to do protection here).

Thanks!
 
Not directly. You can find formulas/constants with the Go-to dialogue
(Ctrl+G, special) for a quick search, or you can use a UDF like this:

Function IsFormula(r As Range) As Boolean
If r.Value = r.Formula Then
IsFormula = False
Else
IsFormula = True
End If
End Function
 
If your users are limited to entering strings only, you could use
CELL("type", A1) which returns "l" if it is a string. Anything else returns
a "v".

From your description, I guess you wnat to see entered numbers. To my
knowledge, there are no functions to do what you want. You can, however,
write a simple function. I will try to give a simple example, with my
moderate knowledge:

Function IsKeyed(Inx as Range)
dim InputCell as Range, ValofCell

set InputCell = Inx.Cells(1,1)
' The purpose of this line is to make sure we deal with a single cell. If a
true cell
' range is entered, only the top-left cell is considered.
ValofCell = InputCell.Value

if InputCell.Formula = ValofCell
IsKeyed = 1 (or true, whatever you want to see)
else
IsKeyed = 0 (or false)
end if

end function

This works because Excel puts the entered value in the formula. However, it
would not work if the cell is a date. If you have to deal with dates as well,
insert the line

if IsDate(InputCell.value) then ValofCell = Cdate(InputCell.Value)

I think this would work.

Hope this helps
 
Back
Top