Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dynamic Formatting\updating of cells - excel2003

Posted on 2011-03-07
12
Medium Priority
?
240 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

604 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