?
Solved

Dynamic Formatting\updating of cells - excel2003

Posted on 2011-03-07
12
Medium Priority
?
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35055252
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 1000 total points
ID: 35055290
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 1000 total points
ID: 35055357
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:paulpen
ID: 35068948
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
ID: 35069031
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
ID: 35077374
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
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35079962
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
ID: 35081999
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
ID: 35106582
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
ID: 35106594
It's been great getting advice from people who clearly know what they are talking about
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35107096
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
ID: 35116266
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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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