Making ####### In a Cell Invisible (Excel 2007)

[ladyck3]

I have a cell on my spreadsheet which is the result of subtracting one time from another. If I put my formula in the results cell, it appears as #####. This is because the data has yet to be entered in the previous cells.... it is entered on a daily basis but I would like to have the formula in place and the cell to appear blank until such time as the data is entered.

U10 (Login Time)
V10 (Log out time)
W10 (difference less 1hr 30 minutes

U10 and V10 are manually entered, the formula for W is as follows:

=IF(ISERROR(SUM((V10*86400)-(U10*86400)-5400)/86400),"",(SUM((V10*86400)-(U10*86400)-5400)/86400))

I used the IF ISERROR formula to hide any error but the ##### is a result of a negative or strange number because the U and V cells are empty.

I tried to conditionally format the cell to make the #### white but that does nothing.

Is this possible, to mask these ### or do I have to copy the formula down on a daily basis? I prefer NOT to use VBA on this... so if there is no way to do it via formula I'll have to copy on a daily basis.

Thanks in advance...


LadyCK3
aka: Laurie

 

[SkipVought]

Hi Laurie,

FIRST, delete the ADVANCED ROWS containing forumals.

Use Data > List > Create List in your existing table.

As you ADD rows to your "List" the formulas will "magically" appear in the new row of data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Ok, so here we go again...stupid user.

I did something wrong, and I'm at a total loss...

I have an add-in in Excel 2007 to replicate the menus for 2003 or earlier so I did what you said I think

I get the option to "Create Table" and I selected W4:W4000
But where does the formula go?

I put it in the W5, W4 is the column header, and it does the same thing as before.

Excel 2007 does fancy stuff with tables... ticks me off that I don't know how to control it.... I've used the Insert > List function before for data validation for drop down lists but never to utilize formulas.

Please hold my hand a little?



LadyCK3
aka: Laurie

 

[SkipVought]

Lets say that your table is 2 columns of data, A & B and Row 1 has your two headings and rows 2-10 all have data: column A has some value that YOU enter and column B has a forumla.

In the past, you might have Pre-Filled column B with the formula down to row 20, but that means that in rows 11-20, you have these nasty looking #######s.

DELETE ROWS 11-20!!!!!

Then, with ANY cell in A1:B10 selected, do Data > List > Create List. Your formula is ALREADY THERE, in every row with data, doing what it's supposed to do.

Select in the LAST ROW and TAB TAB TAB until you get to...

THE NEXT ROW.

That's right. You can TAB from one row to the next!

When the ENTER the data in A11, assuming that's the next empty row, THAT is when your formula appears in B11.

VOLA, y'all!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Nope, 2007 does not have this function, it has Insert >Table.

I go to Insert > Table and it selects the whole sheet almost so I selected cells U4:W432 (line 4 is the header row) The dialog box has an option to check the first row is a header row but it does not allow me to tab on the worksheet...it create the table.

If I use the 2003 simulator it does the same thing and the formula does not computer in new lines... Please don't beat your head on the desk for me... I get it... that I don't get it...

2007 is a different beast and unfortunately I'm once again frustrated because I just KNOW this is a simple task but either I or it is not playing nice.

I'm grieving right now and trying to work... and I guess the two are not mixing well.

Thanks Skip... I do appreciate you, I guess we're going to have to agree on this one... I'm just not getting it and you don't play with 2007 so we're at a place.

Thanks though, seriously.
I tried to work.... UNCLE.

LadyCK3
aka: Laurie

 

[SkipVought]

alt+D I C

Works for me in 2007.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

In reference to your initial post, if you need to conditionally hide ###'s for negative time, just use condition: 'Value is less than 0'.

combo