Hidden Formulas

[Jani]

I have been given an Excel file that has a hidden formula and I can't get it
to display. I have tried the various options found on this site (select
entire sheet & check format tab for hidden cells, did a search by formula for
the cell that is changing, checked named ranges [not sure what to look for on
these but didn't see anything unusual], checked VB code for hidden
worksheets, etc.) but to no avail. Does anyone have any other suggestions?

Thanks!!! Jani

 

[Dave Peterson]

If the worksheet is protected and that cell is locked, then you won't be able to
see the formula in that cell.

If you unprotect the worksheet, you'll be able to see the formula.

I have been given an Excel file that has a hidden formula and I can't get it
to display. I have tried the various options found on this site (select
entire sheet & check format tab for hidden cells, did a search by formula for
the cell that is changing, checked named ranges [not sure what to look for on
these but didn't see anything unusual], checked VB code for hidden
worksheets, etc.) but to no avail. Does anyone have any other suggestions?

Thanks!!! Jani

 

[Jani]

Thanks for the quick reply Dave. However, I have already unprotected the
sheet, selected the entire sheet and unlocked any cells, and there were no
cells showing as hidden. Somewhere there is an IF statement or a function
similar to IF that when an amount >X is entered, a message is displayed in a
different cell. I have searched formulas on all worksheets for text in the
message and the cell location, but have found nothing. Any other thoughts?

If the worksheet is protected and that cell is locked, then you won't be able to
see the formula in that cell.

If you unprotect the worksheet, you'll be able to see the formula.

I have been given an Excel file that has a hidden formula and I can't get it
to display. I have tried the various options found on this site (select
entire sheet & check format tab for hidden cells, did a search by formula for
the cell that is changing, checked named ranges [not sure what to look for on
these but didn't see anything unusual], checked VB code for hidden
worksheets, etc.) but to no avail. Does anyone have any other suggestions?

Thanks!!! Jani

 

[EdMac]

Hi Jani,

have you tried using the formula auditing tool bar to trace
precedents?

Ed

 

[Dave Peterson]

If there is no formula in that cell that you can see when the sheet is
unprotected, then it may not be a formula doing the work.

Excel monitors for certain events--like you changing a cell. Developers can tap
into those events and write code to do whatever they want/can.

It sounds like that could be what's happening.

If you right click on the worksheet tab (of the worksheet that's doing this) and
select view code, can you see anything thing in that newly opened window?

Thanks for the quick reply Dave. However, I have already unprotected the
sheet, selected the entire sheet and unlocked any cells, and there were no
cells showing as hidden. Somewhere there is an IF statement or a function
similar to IF that when an amount >X is entered, a message is displayed in a
different cell. I have searched formulas on all worksheets for text in the
message and the cell location, but have found nothing. Any other thoughts?

If the worksheet is protected and that cell is locked, then you won't be able to
see the formula in that cell.

If you unprotect the worksheet, you'll be able to see the formula.

I have been given an Excel file that has a hidden formula and I can't get it
to display. I have tried the various options found on this site (select
entire sheet & check format tab for hidden cells, did a search by formula for
the cell that is changing, checked named ranges [not sure what to look for on
these but didn't see anything unusual], checked VB code for hidden
worksheets, etc.) but to no avail. Does anyone have any other suggestions?

Thanks!!! Jani

 

[Jani]

EdMac and Dave - Thanks again for trying to help me figure this out. I have
looked to see if there is code and there is not. I have not used the formula
auditing tool before so turned that on and it appears to be pointing from one
cell to another but I don't see anything in either cell that is causing this
text to pop up. Could I send/attach the file so that you can see what is
happening?

If there is no formula in that cell that you can see when the sheet is
unprotected, then it may not be a formula doing the work.

Excel monitors for certain events--like you changing a cell. Developers can tap
into those events and write code to do whatever they want/can.

It sounds like that could be what's happening.

If you right click on the worksheet tab (of the worksheet that's doing this) and
select view code, can you see anything thing in that newly opened window?

Thanks for the quick reply Dave. However, I have already unprotected the
sheet, selected the entire sheet and unlocked any cells, and there were no
cells showing as hidden. Somewhere there is an IF statement or a function
similar to IF that when an amount >X is entered, a message is displayed in a
different cell. I have searched formulas on all worksheets for text in the
message and the cell location, but have found nothing. Any other thoughts?

If the worksheet is protected and that cell is locked, then you won't be able to
see the formula in that cell.

If you unprotect the worksheet, you'll be able to see the formula.

Jani wrote:

I have been given an Excel file that has a hidden formula and I can't get it
to display. I have tried the various options found on this site (select
entire sheet & check format tab for hidden cells, did a search by formula for
the cell that is changing, checked named ranges [not sure what to look for on
these but didn't see anything unusual], checked VB code for hidden
worksheets, etc.) but to no avail. Does anyone have any other suggestions?

Thanks!!! Jani

 

[Dave Peterson]

If the file is small, you can send it to me directly--don't send it to the
newsgroup.

Please include what cells you're reviewing.

EdMac and Dave - Thanks again for trying to help me figure this out. I have
looked to see if there is code and there is not. I have not used the formula
auditing tool before so turned that on and it appears to be pointing from one
cell to another but I don't see anything in either cell that is causing this
text to pop up. Could I send/attach the file so that you can see what is
happening?

If there is no formula in that cell that you can see when the sheet is
unprotected, then it may not be a formula doing the work.

Excel monitors for certain events--like you changing a cell. Developers can tap
into those events and write code to do whatever they want/can.

It sounds like that could be what's happening.

If you right click on the worksheet tab (of the worksheet that's doing this) and
select view code, can you see anything thing in that newly opened window?

Thanks for the quick reply Dave. However, I have already unprotected the
sheet, selected the entire sheet and unlocked any cells, and there were no
cells showing as hidden. Somewhere there is an IF statement or a function
similar to IF that when an amount >X is entered, a message is displayed in a
different cell. I have searched formulas on all worksheets for text in the
message and the cell location, but have found nothing. Any other thoughts?

:

If the worksheet is protected and that cell is locked, then you won't be able to
see the formula in that cell.

If you unprotect the worksheet, you'll be able to see the formula.

Jani wrote:

I have been given an Excel file that has a hidden formula and I can't get it
to display. I have tried the various options found on this site (select
entire sheet & check format tab for hidden cells, did a search by formula for
the cell that is changing, checked named ranges [not sure what to look for on
these but didn't see anything unusual], checked VB code for hidden
worksheets, etc.) but to no avail. Does anyone have any other suggestions?

Thanks!!! Jani

 

[Jani]

Dave, what e-mail address should I use? I used the one on the web and it came
back undeliverable (recipient's address unknown or invalid). Perhaps you
could send me an e-mail and I can reply and include the file (it's <300kb)?
[email protected].

If the file is small, you can send it to me directly--don't send it to the
newsgroup.

Please include what cells you're reviewing.

EdMac and Dave - Thanks again for trying to help me figure this out. I have
looked to see if there is code and there is not. I have not used the formula
auditing tool before so turned that on and it appears to be pointing from one
cell to another but I don't see anything in either cell that is causing this
text to pop up. Could I send/attach the file so that you can see what is
happening?

If there is no formula in that cell that you can see when the sheet is
unprotected, then it may not be a formula doing the work.

Excel monitors for certain events--like you changing a cell. Developers can tap
into those events and write code to do whatever they want/can.

It sounds like that could be what's happening.

If you right click on the worksheet tab (of the worksheet that's doing this) and
select view code, can you see anything thing in that newly opened window?

Jani wrote:

Thanks for the quick reply Dave. However, I have already unprotected the
sheet, selected the entire sheet and unlocked any cells, and there were no
cells showing as hidden. Somewhere there is an IF statement or a function
similar to IF that when an amount >X is entered, a message is displayed in a
different cell. I have searched formulas on all worksheets for text in the
message and the cell location, but have found nothing. Any other thoughts?

:

If the worksheet is protected and that cell is locked, then you won't be able to
see the formula in that cell.

If you unprotect the worksheet, you'll be able to see the formula.

Jani wrote:

I have been given an Excel file that has a hidden formula and I can't get it
to display. I have tried the various options found on this site (select
entire sheet & check format tab for hidden cells, did a search by formula for
the cell that is changing, checked named ranges [not sure what to look for on
these but didn't see anything unusual], checked VB code for hidden
worksheets, etc.) but to no avail. Does anyone have any other suggestions?

Thanks!!! Jani

 

[Dave Peterson]

Remove XSPAM from my email address.

By munging my email address, I'm hoping that the email address robots can't
determine who I am. You may want to munge your email address in any post to a
public newsgroup.

Dave, what e-mail address should I use? I used the one on the web and it came
back undeliverable (recipient's address unknown or invalid). Perhaps you
could send me an e-mail and I can reply and include the file (it's <300kb)?

If the file is small, you can send it to me directly--don't send it to the
newsgroup.

Please include what cells you're reviewing.

EdMac and Dave - Thanks again for trying to help me figure this out. I have
looked to see if there is code and there is not. I have not used the formula
auditing tool before so turned that on and it appears to be pointing from one
cell to another but I don't see anything in either cell that is causing this
text to pop up. Could I send/attach the file so that you can see what is
happening?

:

If there is no formula in that cell that you can see when the sheet is
unprotected, then it may not be a formula doing the work.

Excel monitors for certain events--like you changing a cell. Developers can tap
into those events and write code to do whatever they want/can.

It sounds like that could be what's happening.

If you right click on the worksheet tab (of the worksheet that's doing this) and
select view code, can you see anything thing in that newly opened window?

Jani wrote:

Thanks for the quick reply Dave. However, I have already unprotected the
sheet, selected the entire sheet and unlocked any cells, and there were no
cells showing as hidden. Somewhere there is an IF statement or a function
similar to IF that when an amount >X is entered, a message is displayed in a
different cell. I have searched formulas on all worksheets for text in the
message and the cell location, but have found nothing. Any other thoughts?

:

If the worksheet is protected and that cell is locked, then you won't be able to
see the formula in that cell.

If you unprotect the worksheet, you'll be able to see the formula.

Jani wrote:

I have been given an Excel file that has a hidden formula and I can't get it
to display. I have tried the various options found on this site (select
entire sheet & check format tab for hidden cells, did a search by formula for
the cell that is changing, checked named ranges [not sure what to look for on
these but didn't see anything unusual], checked VB code for hidden
worksheets, etc.) but to no avail. Does anyone have any other suggestions?

Thanks!!! Jani

 

[Dave Peterson]

I got the workbook and it looked like the results of the formulas were hidden by
format|conditional formatting.

But I'm awaiting a reply from Jani.

Remove XSPAM from my email address.

By munging my email address, I'm hoping that the email address robots can't
determine who I am. You may want to munge your email address in any post to a
public newsgroup.

Dave, what e-mail address should I use? I used the one on the web and it came
back undeliverable (recipient's address unknown or invalid). Perhaps you
could send me an e-mail and I can reply and include the file (it's <300kb)?

If the file is small, you can send it to me directly--don't send it to the
newsgroup.

Please include what cells you're reviewing.

Jani wrote:

EdMac and Dave - Thanks again for trying to help me figure this out. I have
looked to see if there is code and there is not. I have not used the formula
auditing tool before so turned that on and it appears to be pointing from one
cell to another but I don't see anything in either cell that is causing this
text to pop up. Could I send/attach the file so that you can see what is
happening?

:

If there is no formula in that cell that you can see when the sheet is
unprotected, then it may not be a formula doing the work.

Excel monitors for certain events--like you changing a cell. Developers can tap
into those events and write code to do whatever they want/can.

It sounds like that could be what's happening.

If you right click on the worksheet tab (of the worksheet that's doing this) and
select view code, can you see anything thing in that newly opened window?

Jani wrote:

Thanks for the quick reply Dave. However, I have already unprotected the
sheet, selected the entire sheet and unlocked any cells, and there were no
cells showing as hidden. Somewhere there is an IF statement or a function
similar to IF that when an amount >X is entered, a message is displayed in a
different cell. I have searched formulas on all worksheets for text in the
message and the cell location, but have found nothing. Any other thoughts?

:

If the worksheet is protected and that cell is locked, then you won't be able to
see the formula in that cell.

If you unprotect the worksheet, you'll be able to see the formula.

Jani wrote:

I have been given an Excel file that has a hidden formula and I can't get it
to display. I have tried the various options found on this site (select
entire sheet & check format tab for hidden cells, did a search by formula for
the cell that is changing, checked named ranges [not sure what to look for on
these but didn't see anything unusual], checked VB code for hidden
worksheets, etc.) but to no avail. Does anyone have any other suggestions?

Thanks!!! Jani

 

[EdMac]

Hi Dave/Jani

out of idle curiosity - Did you get to the bottom of this one?

E

 

[Dave Peterson]

It was the format|conditional formatting that was hiding the results.