5/29/10

Original Poster

eddg# find if thre is a formula in a cell?

1 Recommended Answer*since there is no way to lock a cell, I need to be able to check if the user deleted a a formula. I want to 'error trap' if that happens.*

how do I, say, insert another formula (g9) to look on c9 if the formula was deleted?

thank you

Community content may not be verified or up-to-date. Learn more.

Recommended Answer

5/30/10

ahabeddg,

There are some limitations to the weak pseudo protection method, i.e. it can not 'jump over' cells that should not be protected.

E.g. you want the formula to be in A1, the result in D1 but you want to use B1 and C1 for input. This can't be established as the formula in A1 has to create content - even if this means a blank cell - for all the cells it covers, so the formula in A1 must cover B1 and C1 for it to be able to put a result in D1You can however use a different strategy, don't use a hidden column but use a first hidden row (or both a hidden row and column).

Like this

D1: ={ "" ; B2 * C2}Hide row 1 in which resides D1

You now can enter data in (A2,) B2 and C2 and have the formula result in D2 protected this way.

Note that in the embedded array { ... } commas are used as column separators ( but backslashes "\" in locales using the decimal comma in numbers!) and semi-colons as row separators. e.g. {1

**,**2**;**3**,**4} .Note on the EXPAND function: this function was added to

**the***force***of the CONTINUE functions that contain the result in case they get deleted. This appears no longer to happen automatically when EXPAND is omitted, instead there is an error message in the cell the formula is in which suggest manual intervention to recreate the CONTINUE function (a select cell and press Ctrl+R message), and we won't want that to happen as the formula is hidden. (Google Docs spreadsheets is so wonderful, for each omission or aberration you can always try to find a workaround - unfortunately not with the entire 100% of them. ;)***re-creation*Recommended by

**Original Poster**Was this answer helpful?

How can we improve it?

All Replies (5)

5/29/10

ahabThere is no way to test if a cell contains a formula, using a spreadsheet function, not even a (GAS) spreadsheet function.

I could envision a e.g. onEdit triggered GAS script that performs a test (look if the retrieved formula and the retrieved value are equal, when they are equal the function has been deleted.), but the setup of such would mean the script needs to know in which cells to expect a formula. Note this does not keep the user from

*changing*the formula...(Perhaps you may be interested in [1] )

5/30/10

ahabHmm, I spoke too soon, misleaded by some GAS server errors. There is a possibility using a GAS custom function like this:

function IsFormula( rowoffset, columnoffset) {

var r = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();

return ( r.offset(rowoffset,columnoffset,1,1).getFormula().length != 0 )

}

var r = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();

return ( r.offset(rowoffset,columnoffset,1,1).getFormula().length != 0 )

}

Usage after installing and saving the custom function:

E.g.

A1: =INT( 123.45)

B1: =isformula( 0 ; -1)

Note the method using offsets is less than elegant, so the function needs refining, but I fear no direct references can be used - it's a current limitation in GAS that custom spreadsheet function only can get values passed, and nothing else. So the above is something of a workaround.

P.S. [1] contain the link I apparently forgot to add in my previous post

5/30/10

Original Poster

eddgAhhh!!! wonderful! Thanks Ahab. "weak pseudo protection" is was looking for. That was so easy, I would have never found the function to use. How come is not listed ?

thank you so much

thank you so much

5/30/10

Original Poster

eddgbut you know what, I dont know how to make it work in my case. I explain:

A1 will be the cell I use the EXPAND function (hidden to the user)

B1 and C1 user enters data

D1 will have the calculation: B1*C1

can you help me do this? I can't find much reference on help with this function

thanks

A1 will be the cell I use the EXPAND function (hidden to the user)

B1 and C1 user enters data

D1 will have the calculation: B1*C1

can you help me do this? I can't find much reference on help with this function

thanks

5/30/10

ahabeddg,

There are some limitations to the weak pseudo protection method, i.e. it can not 'jump over' cells that should not be protected.

E.g. you want the formula to be in A1, the result in D1 but you want to use B1 and C1 for input. This can't be established as the formula in A1 has to create content - even if this means a blank cell - for all the cells it covers, so the formula in A1 must cover B1 and C1 for it to be able to put a result in D1You can however use a different strategy, don't use a hidden column but use a first hidden row (or both a hidden row and column).

Like this

D1: ={ "" ; B2 * C2}Hide row 1 in which resides D1

You now can enter data in (A2,) B2 and C2 and have the formula result in D2 protected this way.

Note that in the embedded array { ... } commas are used as column separators ( but backslashes "\" in locales using the decimal comma in numbers!) and semi-colons as row separators. e.g. {1

**,**2**;**3**,**4} .Note on the EXPAND function: this function was added to

**the***force***of the CONTINUE functions that contain the result in case they get deleted. This appears no longer to happen automatically when EXPAND is omitted, instead there is an error message in the cell the formula is in which suggest manual intervention to recreate the CONTINUE function (a select cell and press Ctrl+R message), and we won't want that to happen as the formula is hidden. (Google Docs spreadsheets is so wonderful, for each omission or aberration you can always try to find a workaround - unfortunately not with the entire 100% of them. ;)***re-creation*This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

### Badges

Some community members might have badges that indicate their identity or level of participation in a community.

Community content may not be verified or up-to-date. Learn more.

### Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

- Post an answer.
- Having your answer selected as the best answer.
- Having your post rated as helpful.
- Vote up a post.
- Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

### View profile in forum?

To view this member's profile, you need to leave the current Help page.

### Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

### Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.