Dynamic Formatting\updating of cells - excel2003

I have set up an excel spreadsheet  with some ‘warning lights’  in a column the way they work is that I have set formatted the column as having a ‘wingdings’ font

With conditional formatting against an adjacent column:

There is a formula for column E:

=IF(D3="","","l")

This gives me the button effect in Column ‘E’

I did this to try and make sure that the only time the buttons appeared was when the was a value in the Column D

 

However, This worksheet is refreshed at 15 minute intervals if  a new row appears in the worksheet it all appears to be ok, The problem arises when an item disappears off the sheet , The sheet displays what you see in 11 onwards, I’m guessing this is because there once an entry has been made into a row and then deleted the value in no longer ‘null’ but spaces (or vice versa)’ . The only way I can get rid of these lines is to manually copy the formula back into the offending cells in column E

I’d be grateful if someone out there can provide me with a solution that avoids this ‘messsiness.    
 
The spreadsheet is attached  - tablespace sheet is the one you require

There's also a word doc with some screenshots


excel-2003-prob.doc
test1.xls
paulpenAsked:
Who is Participating?
 
Eric ZwiekhorstConnect With a Mentor SAP Business ConsultantCommented:
Hi Paul, RK,

if this is the case, I would stilluse a combination of the formula's to avoid a problem with spaces..

=IF(ISNUMBER(INDIRECT("D2")),"l","")

Kind regards

Eric
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Hi Paulpen

thry this

=IF(ISNUMBER(D2),"l","")

it see if there is no number and that should work


kind regards
0
 
RKinspConnect With a Mentor Commented:
Hello Paul,

The sheet has the formula =IF(#REF!="";"";"l") for cells 11+. The "REF" indicates that the cells that this formula was pointing to has either been deleted (not the values, but the actual cel) or that you have cut paste on top of the cell.

If you want excel to reference a location even if it was delete or cut on top of, you have to use the indirect method:

example;

=IF(INDIRECT("D12")="","","l")

*notice that there are quotes around the d12 as well.

-RK
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
paulpenAuthor Commented:
So if i use indirect does this mean that if the worksheet is refreshed (as it is designed to do every 5 minutes) and rows are removed the formatting will remain intact ?  
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Yes it will, with the indirect in the formula excel does not change the code if rows are added or removed.

indirect ("B2") will stay allways  B2  even if you delete the rows above,..


Kr

Eric
0
 
RKinspCommented:
Hello Paul,

Please note that if you remove rows, indirect may also cause issues. Example: if you have the formula =IF(ISNUMBER(INDIRECT("D2")),"l","") on Cell C2 and a row above, how you will have the formula =IF(ISNUMBER(INDIRECT("D2")),"l","") on C1.

How are you deleting/moving these numbers around? If it is in vba, the best way to handle this would be redoing the formulas in VBA itself....

-RK
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Dear Paulpen,

A other solution is to change the webquerry (data properties) to Overwrite existing cells with new data clear unused cells..

That might avoid your problems to...


Kind regards

Eric
0
 
RKinspCommented:
I'd go with Eric's idea, that might solve your problem.

Can you tell us how the cells are updated?

thanks,
-RK
0
 
paulpenAuthor Commented:
I have a query that grabs the data from an external datasource and automatically refreshes the data every 5 minutes, i'm using the insert cells for new data, delete unused cells. The query updates n rows x the first 8 columns with the data.  The columns with the formatting are then adjacent to these.

I've now manually put conditional formatting and the formula on 75 rows. This appears to be working ok now as i never expect more than about 20-30 rows to be ever populated in the spread sheet.

Theres a screen shot with the end 'product' and then the tbspace ( Down 1 level) option screen, The tbspace option was where the main problem was because it was so volatile, it's been running for 24 hours and i haven't seen any errors in the display.  So i'm happy to close this now.

Anyone know anything about pivot tables and charts ?  I'm going to post on that one shortly - i think it may need some vba to solve my problem!

Thanks for all of your help
screenshot.JPG
screenshot1..JPG
0
 
paulpenAuthor Commented:
It's been great getting advice from people who clearly know what they are talking about
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Hi Paulpen,

Thanks for the grade and the points,
I'm glad I could be of help..

Kr Eric
0
 
RKinspCommented:
Thanks for the assist points!
Regarding pivot tables and charts, there are lots of experts here with experience with those.

Good luck!
-RK
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.