For the experts : Can a Cell be a formula OR a number, at the same time?

[HGL]

I need a cell to be either a formula, or a value, at the same time.
Does not sound possible, but maybe there is a work around.

The easiest way to explain what I need is to look at the attached workbook, which should explain exactly what I need.

This should bring out the best in you.

Thanks.

[oeldere]

although the file is clear to you, since it is your file, see it from our way.

we don't know at all what you are try to achieve, so the file should be clear about that.

I have no idea what you would try to achieve.

Please add the expected result in your file and mark the related cells with the same backgroundcolor.

[HGL]

Hello oeldere,
My apologies, I thought that the file did explain what I am trying to achieve, under the heading "Situation"
As you can see, the number 2 that you see in Cell N16 is originally derived from Cell B6, via a string of formulae.
This is what I am trying to achieve:
If I manually change Cell N16 from the number 2, to another number, then Cell G16 must change the same.
But if I do not change Cell N16, then N16 must still display the number it has got from the formula.

Hope this is a bit more clear.
Thanks for your time.

[shirleyxls]

Hi,

Once you've typed a number into N16, then the formula is of course lost.
My suggestion would be to have a helper column or two (maybe alongside where your data is pulled from the web) so that you can manually override those figures. Your formulas could then check which one to use, e.g. if you have a manual figure use that, if not use the automatic one.

Does that help?

[HGL]

Hi shirleyxls,

I was thinking along that line also, and might have to resort to that.
I was hoping there was a function to recognise the difference between a formula and a number.
Then in G16 you could have something like =isNotFormaula(n16,n16,n5) or something like that, so that G16 checks if N16 is a formaula or a number. If N16 is number then = N16, if formula then =N5

That is what I ultimately need.

[shirleyxls]

You have ISNUMBER or ISFORMULA which you could use, but it doesn't solve the problem that, once you overtype the original formula with a number it's gone, so next time you do your internet update you'll get incorrect results.

[HGL]

The way I would be utilising this, it doesn't matter that the formula is overtyped with a number when the web scrape is incorrect.
As long as the calculation page (sheet 2) has the correct information.
ISNUMBER return TRUE for both a number (2) and the formula which returns 2, so that won't work.
I have Excel 2010, the function ISFORMULA was introduced in 2013
If ISFORMULA returns TRUE for a formula, and FALSE for a number, that would be first prize.

[shirleyxls]

First prize goes to Excel 2013 and later ISFORMULA does indeed return TRUE for a formula and FALSE for a number

Sorry, I'd forgotten that ISFORMULA was only introduced in later versions so I guess that's not much of a consolation

[HGL]

Got the answer !!
Put in a bit of code, and now it works. Got ISFORMULA to work in 2010.

Updated workbook attached for anybody interested.

Now G16 takes the value from N5, but only if N16 is a formula.
If N16 changes to a value, then G16 takes that value from N16.

Sweet !!

[oeldere]

The used code in #9 is

Please Login or Register  to view this content.

[shirleyxls]

That's neat

[oeldere]

@HGL, since you are new on the forum:

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.