Excel Sheet - Correction

#1
hi all,

I am attaching an excel sheet which pull up the real time data from nifty about options.

This is a hcltech options chain data sheet.

I want all the cells displaying "-" to changed to 0 automatically.

the sheet is auto refreshing, if I change it manually, they again change back to - after refresh.

file link at 4shared
/file/bKtQdDJcce/hcl_sheet.html

please help

i have attached screenshot highlighting the cells which should change to 0

thax all ,aman
 
Last edited:

manishchan

Well-Known Member
#2
hi all,

I am attaching an excel sheet which pull up the real time data from nifty about options.

This is a hcltech options chain data sheet.

I want all the cells displaying "-" to changed to 0 automatically.

the sheet is auto refreshing, if I change it manually, they again change back to - after refresh.

file link at 4shared
/file/bKtQdDJcce/hcl_sheet.html

please help

i have attached screenshot highlighting the cells which should change to 0

thax all ,aman
Probably you would need some VBA codes to change them to 0 if you want them to be done on the same sheet which fetches data. Easier way would be to pull the data that you need in the 2nd sheet (I guess you need the ones that you put in rectangle) and then just add ISBLANK/IF formula to covert to 0 in case of blank.
 
#3
Probably you would need some VBA codes to change them to 0 if you want them to be done on the same sheet which fetches data. Easier way would be to pull the data that you need in the 2nd sheet (I guess you need the ones that you put in rectangle) and then just add ISBLANK/IF formula to covert to 0 in case of blank.
******************

But that will not work. Because I want the sheet ever refreshing to observe the market movements. Trying to track few things.

Thanx.
 

manishchan

Well-Known Member
#4
******************

But that will not work. Because I want the sheet ever refreshing to observe the market movements. Trying to track few things.

Thanx.
Don't change nything in the 1st sheet (which has the data downloading from site at every refresh)..

1 the 2nd sheet, pull data from the 1st sheet by using vlookup. So when the sheet refreshes, your date will also get updated in the 2nd sheet...
 

mastermind007

Well-Known Member
#5
hi all,

I am attaching an excel sheet which pull up the real time data from nifty about options.

This is a hcltech options chain data sheet.

I want all the cells displaying "-" to changed to 0 automatically.

the sheet is auto refreshing, if I change it manually, they again change back to - after refresh.

file link at 4shared
/file/bKtQdDJcce/hcl_sheet.html

please help

i have attached screenshot highlighting the cells which should change to 0

thax all ,aman
One way to change dash to zero would be to apply a filter between the web download and the Excel.

Another way would be to program dash tolerance in your code after the refresh and leave Excel alone...
 
#6
Don't change nything in the 1st sheet (which has the data downloading from site at every refresh)..

1 the 2nd sheet, pull data from the 1st sheet by using vlookup. So when the sheet refreshes, your date will also get updated in the 2nd sheet...
************

This seems to be a nice option.

Can you please guide me how to work on it.
Thax, aman
 
#8
One way to change dash to zero would be to apply a filter between the web download and the Excel.

Another way would be to program dash tolerance in your code after the refresh and leave Excel alone...
****************

Thanks mastermind007:
Can you please guide me where I can find details about how to apply filter.

many thax, aman
 

Similar threads