Is there such a formula?

[Julie P.]

Hi, I am trying to write a formula along these lines:

IF there the next cell contains the text "FVF", then do such and such a
formula; otherwise, simply write in the value I type.

So, for example,:

=IF(C2="FVF",[insert formula here],???????)

It's the least last I am having trouble writing.

Any help would be appreciated!

Julie

 

[Jerry W. Lewis]

You seem to understand IF() formulas, so I presume that you want to type
a number into a cell and yet have the contents of that cell change into
something else if the next cell contains "FVF" That cannot be done in a
worksheet function, but can be done using a VBA worksheet change event.

Jerry

 

[JulieD]

Hi julie

a cell can not contain a function and the ability to store a value in itself
so you can have
=IF(C2="FVF", your_formula,"")
which will put the formula in the cell if C2 = FVF and allow you to type
directly into the cell if you want to, but if you do type directly into the
cell you will overtype (ie lose) the formula - so then if at a later stage
C2 did =FVF then this cell would not change.

I believe this can however, be achieved using a macro solution - if you're
interested in this please post back.

Cheers
JulieD

 

[Julie P.]

Hi julie

a cell can not contain a function and the ability to store a value in itself
so you can have
=IF(C2="FVF", your_formula,"")
which will put the formula in the cell if C2 = FVF and allow you to type
directly into the cell if you want to, but if you do type directly into the
cell you will overtype (ie lose) the formula - so then if at a later stage
C2 did =FVF then this cell would not change.

I believe this can however, be achieved using a macro solution - if you're
interested in this please post back.

Thanks Julie. Actually, I never plan to change the value in the referent
cell after I enter it (in this case, C2="FVF"), so I wouldn't mind
overwriting the formula to enter a text value in other cases.

My plan is to store dollar amounts of various values in this one column, and
then in the next column, label what type of dollar value this is. In this
case, it is for eBay. So, for regular listing fees I am charged, these are
fixed, and I enter those manually. But for certain fees, such as the Final
Value Fee (=FVF), this is computed based on a sliding-scale formula,
depending on the amount your item sold for. So if this were the type of fee,
then I would want the formula to apply. But once I type in "FVF", I would
never change this. Otherwise, for other types of fees, I want to just type
in the value manually.

So, could I do the formula below:

=IF(C2="FVF",_my formula_,)

where I leave the final ternary value of the IF formula out? IOW, I want to
leave the cell blank if "FVF" is not in the referent cell.

 

[Julie P.]

You seem to understand IF() formulas, so I presume that you want to type
a number into a cell and yet have the contents of that cell change into
something else if the next cell contains "FVF" That cannot be done in a
worksheet function, but can be done using a VBA worksheet change event.

Thanks Jerry! Actually, when I would enter "FVF" in the next cell, that
would never change. And it would be entered at the same time as the cell
where the formula is is first computed. I think I might have explained this
a little better in my reply to JulieD.

 

[Guest]

As both of these experts indicated this can't be done. However, there is a simple to get around that while achieving your objective:

Currently you have one column (say column B) that stores "FVF"s and another column (say column C) to store the final $ values or "FVF"s. You could add an extra column (column D) with this forlume "=If(B2="FVF",B2,D2)". So as you type in the values in column C, then Column D will automatically determine if it should be a value or "FVF". Not sure if this helps.

 

[JulieD]

Hi Julie

you need the "false" part of the statement - otherwise your cell will show
false - just use the zero length text string ("") to indicate nothing

=IF(C2="FVF",_my formula_,"")

Cheers
JulieD

 

[Julie P.]

As both of these experts indicated this can't be done. However, there is a

simple to get around that while achieving your objective:

Currently you have one column (say column B) that stores "FVF"s and

another column (say column C) to store the final $ values or "FVF"s. You
could add an extra column (column D) with this forlume
"=If(B2="FVF",B2,D2)". So as you type in the values in column C, then Column
D will automatically determine if it should be a value or "FVF". Not sure if
this helps.
Thanks Anson. This is an idea I will consider. The thing is I actually
already have multiple columns, each with a different type of fee. So I was
trying to simplify things, and have only one column reserved for dollar
values. But only one extra column shouldn't be too bad.

 

[Julie P.]

Hi Julie

you need the "false" part of the statement - otherwise your cell will show
false - just use the zero length text string ("") to indicate nothing

=IF(C2="FVF",_my formula_,"")

Thanks Julie. I don't know why I didn't think of that before. That should
work.