Solved

Dynamic Formatting\updating of cells - excel2003

Posted on 2011-03-07
12
228 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
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 250 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 250 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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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