/docs/community?hl=en
/docs/community?hl=en
5/29/10
Original Poster
eddg

find if thre is a formula in a cell?

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
Was this answer helpful?
How can we improve it?
All Replies (5)
ahab
5/29/10
ahab
There 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] )
ahab
5/30/10
ahab
Hmm, I spoke too soon, misleaded by some GAS server errors. There is a possibility using a GAS custom function like this:

function IsFormularowoffset, columnoffset{
  var SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();
  return  r.offset(rowoffset,columnoffset,1,1).getFormula().length != 
}

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
eddg
Ahhh!!! 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
5/30/10
Original Poster
eddg
but 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
ahab
5/30/10
ahab
eddg,
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 D1

You 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 force the re-creation 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. ;)
 
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.

 
Expert - Google Employee — Googler guides and community managers
 
Expert - Community Specialist — Google partners who share their expertise
 
Expert - Gold — Trusted members who are knowledgeable and active contributors
 
Expert - Platinum — Seasoned members who contribute beyond providing help through mentoring, creating content, and more
 
Expert - Alumni — Past members who are no longer active, but were previously recognized for their helpfulness
 
Expert - Silver — New members who are developing their product knowledge
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.