Solved

Dynamic Formatting\updating of cells - excel2003

Posted on 2011-03-07
12
224 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:paulpen
  • 5
  • 4
  • 3
12 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Hi Paulpen

thry this

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

it see if there is no number and that should work


kind regards
0
 
LVL 6

Assisted Solution

by:RKinsp
RKinsp earned 250 total points
Comment Utility
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
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 250 total points
Comment Utility
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
 

Author Comment

by:paulpen
Comment Utility
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
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
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
 
LVL 6

Expert Comment

by:RKinsp
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
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
 
LVL 6

Expert Comment

by:RKinsp
Comment Utility
I'd go with Eric's idea, that might solve your problem.

Can you tell us how the cells are updated?

thanks,
-RK
0
 

Author Comment

by:paulpen
Comment Utility
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
 

Author Closing Comment

by:paulpen
Comment Utility
It's been great getting advice from people who clearly know what they are talking about
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Hi Paulpen,

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

Kr Eric
0
 
LVL 6

Expert Comment

by:RKinsp
Comment Utility
Thanks for the assist points!
Regarding pivot tables and charts, there are lots of experts here with experience with those.

Good luck!
-RK
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now