Help needed for a macro

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
In 1 of the ws (out of 15) in my Excel workbook; B2:B200 generates +ve integers including 0 i.e. 0,1,2,3,4,5,... PLUS some cells in B2:B200 are blank (since no values/formulas being present there).

The corresponding C2:C200 & D2:D200 contains formulas & generates numerical values.
Output required: Formulas in corresponding C2:C200 SHOULD GET REMOVED IMMEDIATELY for corresponding B2:B200 generating 3.

Ex:
B2=1(thr' formula) C2=D2(formula) D2=200.32 (thr' formula)
B2
.
.
Now if:
B2=3 THE FORMULA IN C2 SHOULD GET REMOVED IMMEDIATELY

How to accomplish?
Thanks in advance.
 
Sort by date Sort by votes
On my phone so can't check anything but afraid I can't see how you can have a 3 in column B and the corresponding cell in column C not be a value when the code is run on the activesheet and so can't be of any help.
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Just now checked the vba...The Left hand side shows "(Declarations)" & the right hand side "test". Should it be made to be kept at "(Declarations)" for the code to work 'automatically'?
 
Upvote 0
The code won't work automatically, it is not worksheet event code you put it in a standard module and have to run it as a standard sub.
What you haven't stated is why you need it converted to formulas as it is still a puzzle to me.
 
Upvote 0
MARK858 said:
The code won't work automatically, it is not worksheet event code you put it in a standard module and have to run it as a standard sub. What should I do to make it trigger automatically for any 3 found in column B. Could not understand what is "have to run it as a standard sub". I am using it in a ws of an Excel workbook which has more than 20 ws & out of which 1 ws has real time feed.
What you haven't stated is why you need it converted to formulas as it is still a puzzle to me.
Click to expand...
I'll explain you...but it will require some more terms, so please allow me a day or 2.
 
Upvote 0
I don't know "standard module". I have pasted the code by right clicking the ws's tab name & then 'view code' | after which a window opens.
I am using Excel 2007.
Meanwhile please help me with query in #24
 
Upvote 0
What you haven't stated is why you need it converted to formulas as it is still a puzzle to me.
I'll try to elaborate:

D is constantly getting updated in values say 220.32, 220.38, 222.46, .......using RTD
value 3 occurs in column B ONLY when column D > a defined value else column B generates other than 3 (null, 1,2,4,). I am doing some calculation using D ONLY WHEN B=3. For this I use column C as =D & want C to become formula free as soon as B=3. This calculation requires value D AT THE MOMENT WHEN B=3. So as soon as B=3, calculation is done in some other ws to get a value. But if B becomes 2, than I don't need the 'calculated' values & would wait for B becoming 3 again to START WITH A FRESH CALCULATION. So I needed the FORMULA BACK in C & thereafter to make C again FORMULA FREE if B=3 & so on.....
 
Last edited:
Upvote 0
hsandeep, first of all I only respond with code during spare time I have (which for the next couple of days is a very small amount of time) and virtually never when I am at work.

Secondly to achieve what you want you need to supply a fair bit more information.

1) how is the 3 in column B being entered? manually, by formula, by macro, by import or by some other method?
2) how is the data being up updated i.e. how is the information being imported into the sheet?
3) how often is the data being updated?
4) how many rows and columns are we dealing with?
5) Why can't you pull the information from Column D with a macro as a value into your calculation at the same time as Column B changes to 3 (which brings it back to question number 1)?
6) What calculation is the value being used for? is it a formula calculation or a macro calculation (whichever it is, post it)?
7) what does the end data look like (see question 8)?
8) can you supply usable screenshots of your data, both sheets if the calculations are being done on another sheet (sanitize any sensitive data). See my signature block for ways to do this

Being honest I am a bit concerned about giving you code you don't understand especially if we end up having to use something like application.ontime when you don't understand what the code is doing.

I will have a look at this once you have answered the questions above but like I said it probably won't be for a couple of days but someone might step in with an idea before then.
 
Upvote 0
Secondly to achieve what you want you need to supply a fair bit more information. Note: Your code is required in ws with tab named "D3" & calculation done in ws with tab named "E3"

1) how is the 3 in column B being entered? manually, by formula, by macro, by import or by some other method? By formula screenshot attached
2) how is the data being up updated i.e. how is the information being imported into the sheet? from another ws by simple Excel function "=" screenshot attached
3) how often is the data being updated? The data gets updated every second/milli-seconds thr' real time feed in another ws tab named "7". There is no macros or real time data 'directly' feeded in the current ws which requires your code & which is tab named "D3"
4) how many rows and columns are we dealing with? screen shot attached for ws D3
5) Why can't you pull the information from Column D with a macro as a value into your calculation at the same time as Column B changes to 3 (which brings it back to question number 1)? My 'Result' see.."E3"....'continuous' value of 3 in the cell of column C of "D3" is required. screen shot attached. I want my formula to run on E3 to give me the desired result.
6) What calculation is the value being used for? is it a formula calculation or a macro calculation (whichever it is, post it)? formula calculation shown in "E3". screen shot attached
7) what does the end data look like (see question 8)? End data gives a 'Result' generated thr' formula in "E3" in column C screen shot attached
8) can you supply usable screenshots of your data, both sheets if the calculations are being done on another sheet (sanitize any sensitive data). See my signature block for ways to do this screen shots are attached.

Being honest I am a bit concerned about giving you code you don't understand especially if we end up having to use something like application.ontime when you don't understand what the code is doing.

Lastly MARK858 please understand I also know every time & energy spent should be utilized & your code (if given) would be used for meeting a desired & pre-defined answer which would help me ALWAYS in days ahead. You may ask further queries if required. I would appreciate if the help is provided..of course you may do it as per your time-convenience. Regards & the Screen shots are being attached in next actions.

I will have a look at this once you have answered the questions above but like I said it probably won't be for a couple of days but someone might step in with an idea before then.[/QUOTE]
 
Upvote 0
MARK858, Sorry for some delay...got used to Excel Jeanie courtesy (Peter SS)......
Screen shots attached:
SS1
D3 In this B3=3, C3 to be made FORMULA FREE & again the same formula '=D3' to be re-pushed if B3 is not equal to 3. (Your code needed for this purpose)

ABCDEF
Ktk
YES
UBI
BOI
ICICI
AXIS
SBI
Indus
BOB
PNB
Canara
Rcap
Rcom
Tatamo
IBR
DLF
TVS
BHEL
JSPL
HDIL
Fed
Ran
AdEnt
AdPo
IDFC
CG
Infy
TCS
MS
SKS
Tatastl
Hindal
UPL
SSLT
Coal
REC
PFC
N
BN
Ori
ONGC
Ktk
YES
UBI
BOI
ICICI
AXIS
SBI
Indus
BOB
PNB
Canara
Rcap
Rcom
Tatamo
IBR
DLF
TVS
BHEL
JSPL
HDIL
Fed
Ran
AdEnt
AdPo
IDFC
CG
Infy
TCS
MS
SKS
Tatastl
Hindal
UPL
SSLT
Coal
REC
PFC
N
BN
Ori
ONGC

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 62px"><col style="WIDTH: 115px"><col style="WIDTH: 115px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 110px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: center"]Time[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]125.15[/TD]
[TD="bgcolor: #ffff00, align: right"]125.15[/TD]
[TD="bgcolor: #ccffcc, align: right"]1752716[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #ffcc99, align: right"]3[/TD]
[TD="bgcolor: #ffcc99, align: right"]591.23[/TD]
[TD="bgcolor: #ffff00, align: right"]590.30[/TD]
[TD="bgcolor: #ccffcc, align: right"]2868279[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]218.67[/TD]
[TD="bgcolor: #ffff00, align: right"]218.67[/TD]
[TD="bgcolor: #ccffcc, align: right"]4081693[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]292.95[/TD]
[TD="bgcolor: #ffff00, align: right"]292.95[/TD]
[TD="bgcolor: #ccffcc, align: right"]2332353[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #ffcc99, align: right"]1[/TD]
[TD="bgcolor: #ffcc99, align: right"]1553.76[/TD]
[TD="bgcolor: #ffff00, align: right"]1553.76[/TD]
[TD="bgcolor: #ccffcc, align: right"]1219288[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]402.24[/TD]
[TD="bgcolor: #ffff00, align: right"]402.24[/TD]
[TD="bgcolor: #ccffcc, align: right"]3458236[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]2608.38[/TD]
[TD="bgcolor: #ffff00, align: right"]2608.38[/TD]
[TD="bgcolor: #ccffcc, align: right"]2012120[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #ffcc99, align: right"]1[/TD]
[TD="bgcolor: #ffcc99, align: right"]624.80[/TD]
[TD="bgcolor: #ffff00, align: right"]624.80[/TD]
[TD="bgcolor: #ccffcc, align: right"]870506[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]948.38[/TD]
[TD="bgcolor: #ffff00, align: right"]948.38[/TD]
[TD="bgcolor: #ccffcc, align: right"]1048066[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]987.84[/TD]
[TD="bgcolor: #ffff00, align: right"]987.84[/TD]
[TD="bgcolor: #ccffcc, align: right"]949759[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]398.31[/TD]
[TD="bgcolor: #ffff00, align: right"]398.31[/TD]
[TD="bgcolor: #ccffcc, align: right"]2141765[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]527.91[/TD]
[TD="bgcolor: #ffff00, align: right"]527.91[/TD]
[TD="bgcolor: #ccffcc, align: right"]2795009[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]104.62[/TD]
[TD="bgcolor: #ffff00, align: right"]104.62[/TD]
[TD="bgcolor: #ccffcc, align: right"]5946117[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #ffcc99, align: right"]1[/TD]
[TD="bgcolor: #ffcc99, align: right"]517.04[/TD]
[TD="bgcolor: #ffff00, align: right"]517.04[/TD]
[TD="bgcolor: #ccffcc, align: right"]7051279[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #ffcc99, align: right"]0[/TD]
[TD="bgcolor: #ffcc99, align: right"]67.63[/TD]
[TD="bgcolor: #ffff00, align: right"]67.63[/TD]
[TD="bgcolor: #ccffcc, align: right"]10467233[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #ffcc99, align: right"]1[/TD]
[TD="bgcolor: #ffcc99, align: right"]173.88[/TD]
[TD="bgcolor: #ffff00, align: right"]173.88[/TD]
[TD="bgcolor: #ccffcc, align: right"]5788175[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]218.77[/TD]
[TD="bgcolor: #ffff00, align: right"]218.77[/TD]
[TD="bgcolor: #ccffcc, align: right"]4759894[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="bgcolor: #ffcc99, align: right"]1[/TD]
[TD="bgcolor: #ffcc99, align: right"]225.79[/TD]
[TD="bgcolor: #ffff00, align: right"]225.79[/TD]
[TD="bgcolor: #ccffcc, align: right"]5472760[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]218.91[/TD]
[TD="bgcolor: #ffff00, align: right"]218.91[/TD]
[TD="bgcolor: #ccffcc, align: right"]4499908[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="bgcolor: #ffcc99, align: right"]0[/TD]
[TD="bgcolor: #ffcc99, align: right"]90.82[/TD]
[TD="bgcolor: #ffff00, align: right"]90.82[/TD]
[TD="bgcolor: #ccffcc, align: right"]25952319[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]125.03[/TD]
[TD="bgcolor: #ffff00, align: right"]125.03[/TD]
[TD="bgcolor: #ccffcc, align: right"]2569815[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]603.39[/TD]
[TD="bgcolor: #ffff00, align: right"]603.39[/TD]
[TD="bgcolor: #ccffcc, align: right"]802286[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="bgcolor: #ffcc99, align: right"]1[/TD]
[TD="bgcolor: #ffcc99, align: right"]505.21[/TD]
[TD="bgcolor: #ffff00, align: right"]505.21[/TD]
[TD="bgcolor: #ccffcc, align: right"]2656415[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="bgcolor: #ffcc99, align: right"]0[/TD]
[TD="bgcolor: #ffcc99, align: right"]289.33[/TD]
[TD="bgcolor: #ffff00, align: right"]289.33[/TD]
[TD="bgcolor: #ccffcc, align: right"]3210047[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]147.04[/TD]
[TD="bgcolor: #ffff00, align: right"]147.04[/TD]
[TD="bgcolor: #ccffcc, align: right"]6636775[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]218.60[/TD]
[TD="bgcolor: #ffff00, align: right"]218.60[/TD]
[TD="bgcolor: #ccffcc, align: right"]2490536[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="bgcolor: #ffcc99, align: right"]1[/TD]
[TD="bgcolor: #ffcc99, align: right"]3695.90[/TD]
[TD="bgcolor: #ffff00, align: right"]3695.90[/TD]
[TD="bgcolor: #ccffcc, align: right"]1138097[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]

[TD="bgcolor: #ffcc99, align: right"]1[/TD]
[TD="bgcolor: #ffcc99, align: right"]2627.38[/TD]
[TD="bgcolor: #ffff00, align: right"]2627.38[/TD]
[TD="bgcolor: #ccffcc, align: right"]741539[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]425.82[/TD]
[TD="bgcolor: #ffff00, align: right"]425.82[/TD]
[TD="bgcolor: #ccffcc, align: right"]2397198[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]315.33[/TD]
[TD="bgcolor: #ffff00, align: right"]315.33[/TD]
[TD="bgcolor: #ccffcc, align: right"]1543035[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]505.30[/TD]
[TD="bgcolor: #ffff00, align: right"]505.30[/TD]
[TD="bgcolor: #ccffcc, align: right"]4870457[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]163.16[/TD]
[TD="bgcolor: #ffff00, align: right"]163.16[/TD]
[TD="bgcolor: #ccffcc, align: right"]4836865[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]356.96[/TD]
[TD="bgcolor: #ffff00, align: right"]356.96[/TD]
[TD="bgcolor: #ccffcc, align: right"]2330658[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]35[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]285.02[/TD]
[TD="bgcolor: #ffff00, align: right"]285.02[/TD]
[TD="bgcolor: #ccffcc, align: right"]4220101[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]36[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]345.07[/TD]
[TD="bgcolor: #ffff00, align: right"]345.07[/TD]
[TD="bgcolor: #ccffcc, align: right"]2647582[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]37[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]266.46[/TD]
[TD="bgcolor: #ffff00, align: right"]266.46[/TD]
[TD="bgcolor: #ccffcc, align: right"]2353054[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]38[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]246.70[/TD]
[TD="bgcolor: #ffff00, align: right"]246.70[/TD]
[TD="bgcolor: #ccffcc, align: right"]4084793[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]39[/TD]

[TD="bgcolor: #ffcc99, align: right"]1[/TD]
[TD="bgcolor: #ffcc99, align: right"]8082.27[/TD]
[TD="bgcolor: #ffff00, align: right"]8082.27[/TD]
[TD="bgcolor: #ccffcc, align: right"]15907850[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]40[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]16076.80[/TD]
[TD="bgcolor: #ffff00, align: right"]16076.80[/TD]
[TD="bgcolor: #ccffcc, align: right"]3197900[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]41[/TD]

[TD="bgcolor: #ffcc99, align: right"]4[/TD]
[TD="bgcolor: #ffcc99, align: right"]288.50[/TD]
[TD="bgcolor: #ffff00, align: right"]288.50[/TD]
[TD="bgcolor: #ccffcc, align: right"]1544928[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]42[/TD]

[TD="bgcolor: #ffcc99, align: right"]2[/TD]
[TD="bgcolor: #ffcc99, align: right"]413.22[/TD]
[TD="bgcolor: #ffff00, align: right"]413.22[/TD]
[TD="bgcolor: #ccffcc, align: right"]4280174[/TD]
[TD="align: right"]9/18/2014 19:02[/TD]

[TD="bgcolor: #cacaca, align: center"]43[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]1752716[/TD]

[TD="bgcolor: #cacaca, align: center"]44[/TD]

[TD="align: right"]3[/TD]
[TD="bgcolor: #ccffcc, align: right"]3127014[/TD]

[TD="bgcolor: #cacaca, align: center"]45[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]4081693[/TD]

[TD="bgcolor: #cacaca, align: center"]46[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]2332353[/TD]

[TD="bgcolor: #cacaca, align: center"]47[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #ccffcc, align: right"]1219288[/TD]

[TD="bgcolor: #cacaca, align: center"]48[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]3458236[/TD]

[TD="bgcolor: #cacaca, align: center"]49[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]2012120[/TD]

[TD="bgcolor: #cacaca, align: center"]50[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #ccffcc, align: right"]870506[/TD]

[TD="bgcolor: #cacaca, align: center"]51[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]1048066[/TD]

[TD="bgcolor: #cacaca, align: center"]52[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]949759[/TD]

[TD="bgcolor: #cacaca, align: center"]53[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]2141765[/TD]

[TD="bgcolor: #cacaca, align: center"]54[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]2795009[/TD]

[TD="bgcolor: #cacaca, align: center"]55[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]5946117[/TD]

[TD="bgcolor: #cacaca, align: center"]56[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #ccffcc, align: right"]7051279[/TD]

[TD="bgcolor: #cacaca, align: center"]57[/TD]

[TD="align: right"]0[/TD]
[TD="bgcolor: #ccffcc, align: right"]10467233[/TD]

[TD="bgcolor: #cacaca, align: center"]58[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #ccffcc, align: right"]5788175[/TD]

[TD="bgcolor: #cacaca, align: center"]59[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]4759894[/TD]

[TD="bgcolor: #cacaca, align: center"]60[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #ccffcc, align: right"]5472760[/TD]

[TD="bgcolor: #cacaca, align: center"]61[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]4499908[/TD]

[TD="bgcolor: #cacaca, align: center"]62[/TD]

[TD="align: right"]0[/TD]
[TD="bgcolor: #ccffcc, align: right"]25952319[/TD]

[TD="bgcolor: #cacaca, align: center"]63[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]2569815[/TD]

[TD="bgcolor: #cacaca, align: center"]64[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]802286[/TD]

[TD="bgcolor: #cacaca, align: center"]65[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #ccffcc, align: right"]2656415[/TD]

[TD="bgcolor: #cacaca, align: center"]66[/TD]

[TD="align: right"]0[/TD]
[TD="bgcolor: #ccffcc, align: right"]3210047[/TD]

[TD="bgcolor: #cacaca, align: center"]67[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]6636775[/TD]

[TD="bgcolor: #cacaca, align: center"]68[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]2490536[/TD]

[TD="bgcolor: #cacaca, align: center"]69[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #ccffcc, align: right"]1138097[/TD]

[TD="bgcolor: #cacaca, align: center"]70[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #ccffcc, align: right"]741539[/TD]

[TD="bgcolor: #cacaca, align: center"]71[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]2397198[/TD]

[TD="bgcolor: #cacaca, align: center"]72[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]1543035[/TD]

[TD="bgcolor: #cacaca, align: center"]73[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]4870457[/TD]

[TD="bgcolor: #cacaca, align: center"]74[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]4836865[/TD]

[TD="bgcolor: #cacaca, align: center"]75[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]2330658[/TD]

[TD="bgcolor: #cacaca, align: center"]76[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]4220101[/TD]

[TD="bgcolor: #cacaca, align: center"]77[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]2647582[/TD]

[TD="bgcolor: #cacaca, align: center"]78[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]2353054[/TD]

[TD="bgcolor: #cacaca, align: center"]79[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]4084793[/TD]

[TD="bgcolor: #cacaca, align: center"]80[/TD]

[TD="align: right"]1[/TD]
[TD="bgcolor: #ccffcc, align: right"]15907850[/TD]

[TD="bgcolor: #cacaca, align: center"]81[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]3197900[/TD]

[TD="bgcolor: #cacaca, align: center"]82[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #ccffcc, align: right"]1544928[/TD]

[TD="bgcolor: #cacaca, align: center"]83[/TD]

[TD="align: right"]2[/TD]
[TD="bgcolor: #ccffcc, align: right"]4280174[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B2=V!X12
C2=D2
D2='7'!L22
E2='7'!M22
F2=NOW()
B3=V!X16
D3='7'!L16
E3='7'!M16
F3=NOW()
B4=V!X15
C4=D4
D4='7'!L18
E4='7'!M18
F4=NOW()
B5=V!X17
C5=D5
D5='7'!L14
E5='7'!M14
F5=NOW()
B6=V!X9
C6=D6
D6='7'!L26
E6='7'!M26
F6=NOW()
B7=V!X10
C7=D7
D7='7'!L28
E7='7'!M28
F7=NOW()
B8=V!X11
C8=D8
D8='7'!L30
E8='7'!M30
F8=NOW()
B9=V!X13
C9=D9
D9='7'!L20
E9='7'!M20
F9=NOW()
B10=V!X14
C10=D10
D10='7'!L24
E10='7'!M24
F10=NOW()
B11=V!X61
C11=D11
D11='7'!L58
E11='7'!M58
F11=NOW()
B12=V!X79
C12=D12
D12='7'!L75
E12='7'!M75
F12=NOW()
B13=V!X41
C13=D13
D13='7'!L46
E13='7'!M46
F13=NOW()
B14=V!X42
C14=D14
D14='7'!L48
E14='7'!M48
F14=NOW()
B15=V!X45
C15=D15
D15='7'!L50
E15='7'!M50
F15=NOW()
B16=V!X43
C16=D16
D16='7'!L54
E16='7'!M54
F16=NOW()
B17=V!X44
C17=D17
D17='7'!L52
E17='7'!M52
F17=NOW()
B18=V!X67
C18=D18
D18='7'!L64
E18='7'!M64
F18=NOW()
B19=V!X68
C19=D19
D19='7'!L66
E19='7'!M66
F19=NOW()
B20=V!X73
C20=D20
D20='7'!L69
E20='7'!M69
F20=NOW()
B21=V!X76
C21=D21
D21='7'!L72
E21='7'!M72
F21=NOW()
B22=V!X85
C22=D22
D22='7'!L81
E22='7'!M81
F22=NOW()
B23=V!X88
C23=D23
D23='7'!L84
E23='7'!M84
F23=NOW()
B24=V!X91
C24=D24
D24='7'!L87
E24='7'!M87
F24=NOW()
B25=V!X94
C25=D25
D25='7'!L90
E25='7'!M90
F25=NOW()
B26=V!X112
C26=D26
D26='7'!L108
E26='7'!M108
F26=NOW()
B27=V!X97
C27=D27
D27='7'!L93
E27='7'!M93
F27=NOW()
B28=V!X100
C28=D28
D28='7'!L96
E28='7'!M96
F28=NOW()
B29=V!X103
C29=D29
D29='7'!L99
E29='7'!M99
F29=NOW()
B30=V!X106
C30=D30
D30='7'!L102
E30='7'!M102
F30=NOW()
B31=V!X109
C31=D31
D31='7'!L105
E31='7'!M105
F31=NOW()
B32=V!X38
C32=D32
D32='7'!L42
E32='7'!M42
F32=NOW()
B33=V!X39
C33=D33
D33='7'!L44
E33='7'!M44
F33=NOW()
B34=V!X115
C34=D34
D34='7'!L111
E34='7'!M111
F34=NOW()
B35=V!X119
C35=D35
D35='7'!L119
E35='7'!M119
F35=NOW()
B36=V!X118
C36=D36
D36='7'!L117
E36='7'!M117
F36=NOW()
B37=V!X120
C37=D37
D37='7'!L121
E37='7'!M121
F37=NOW()
B38=V!X121
C38=D38
D38='7'!L123
E38='7'!M123
F38=NOW()
B39=V!X7
C39=D39
D39='7'!L2
E39='7'!M2
F39=NOW()
B40=V!X8
C40=D40
D40='7'!L3
E40='7'!M3
F40=NOW()
B41=V!X130
C41=D41
D41='7'!L126
E41='7'!M126
F41=NOW()
B42=V!X133
C42=D42
D42='7'!L129
E42='7'!M129
F42=NOW()
B43=B2
C43=E2
B45=B4
C45=E4
B46=B5
C46=E5
B47=B6
C47=E6
B48=B7
C48=E7
B49=B8
C49=E8
B50=B9
C50=E9
B51=B10
C51=E10
B52=B11
C52=E11
B53=B12
C53=E12
B54=B13
C54=E13
B55=B14
C55=E14
B56=B15
C56=E15
B57=B16
C57=E16
B58=B17
C58=E17
B59=B18
C59=E18
B60=B19
C60=E19
B61=B20
C61=E20
B62=B21
C62=E21
B63=B22
C63=E22
B64=B23
C64=E23
B65=B24
C65=E24
B66=B25
C66=E25
B67=B26
C67=E26
B68=B27
C68=E27
B69=B28
C69=E28
B70=B29
C70=E29
B71=B30
C71=E30
B72=B31
C72=E31
B73=B32
C73=E32
B74=B33
C74=E33
B75=B34
C75=E34
B76=B35
C76=E35
B77=B36
C77=E36
B78=B37
C78=E37
B79=B38
C79=E38
B80=B39
C80=E39
B81=B40
C81=E40
B82=B41
C82=E41
B83=B42
C83=E42

<tbody>
</tbody>

<tbody>
</tbody>

SS2:
E3 The ws being used by me for calculation purpose.

BCDEFGHIJ
CM LTPAvg_NSEAvg_NSE
Ktk
YES
UBI
BOI
ICICI
AXIS
SBI
Indus
BOB
PNB
Canara
Rcap
Rcom
Tatamo
IBR
DLF
TVS
BHEL
JSPL
HDIL
Fed
Ran
AdEnt
AdPo
IDFC
CG
Infy
TCS
MS
SKS
Tatastl
Hindal
UPL
SSLT
Coal
REC
PFC
N
BN
Ori
ONGC

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 83px"><col style="WIDTH: 114px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 81px"><col style="WIDTH: 81px"><col style="WIDTH: 81px"><col style="WIDTH: 81px"><col style="WIDTH: 84px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #ffff00, align: center"]Result[/TD]

[TD="align: center"]Vol[/TD]
[TD="align: center"]TO[/TD]

[TD="align: center"]Vol[/TD]
[TD="align: center"]TO[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]126.35[/TD]
[TD="align: right"]125.15[/TD]
[TD="align: right"]1752716[/TD]
[TD="align: right"]219352407[/TD]
[TD="bgcolor: #ffcc99, align: right"]125.15[/TD]
[TD="bgcolor: #ccffcc, align: right"]1752716[/TD]
[TD="align: right"]219352407[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]601.54[/TD]
[TD="align: right"]594.80[/TD]
[TD="align: right"]590.30[/TD]
[TD="align: right"]2868279[/TD]
[TD="align: right"]1693145094[/TD]
[TD="bgcolor: #ffcc99, align: right"]591.23[/TD]
[TD="bgcolor: #ccffcc, align: right"]3127014[/TD]
[TD="align: right"]1848784487[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]221.95[/TD]
[TD="align: right"]218.67[/TD]
[TD="align: right"]4081693[/TD]
[TD="align: right"]892543808[/TD]
[TD="bgcolor: #ffcc99, align: right"]218.67[/TD]
[TD="bgcolor: #ccffcc, align: right"]4081693[/TD]
[TD="align: right"]892543808[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]296.20[/TD]
[TD="align: right"]292.95[/TD]
[TD="align: right"]2332353[/TD]
[TD="align: right"]683262811[/TD]
[TD="bgcolor: #ffcc99, align: right"]292.95[/TD]
[TD="bgcolor: #ccffcc, align: right"]2332353[/TD]
[TD="align: right"]683262811[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]1565.00[/TD]
[TD="align: right"]1553.76[/TD]
[TD="align: right"]1219288[/TD]
[TD="align: right"]1894480923[/TD]
[TD="bgcolor: #ffcc99, align: right"]1553.76[/TD]
[TD="bgcolor: #ccffcc, align: right"]1219288[/TD]
[TD="align: right"]1894480923[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]405.30[/TD]
[TD="align: right"]402.24[/TD]
[TD="align: right"]3458236[/TD]
[TD="align: right"]1391040849[/TD]
[TD="bgcolor: #ffcc99, align: right"]402.24[/TD]
[TD="bgcolor: #ccffcc, align: right"]3458236[/TD]
[TD="align: right"]1391040849[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]2620.40[/TD]
[TD="align: right"]2608.38[/TD]
[TD="align: right"]2012120[/TD]
[TD="align: right"]5248373566[/TD]
[TD="bgcolor: #ffcc99, align: right"]2608.38[/TD]
[TD="bgcolor: #ccffcc, align: right"]2012120[/TD]
[TD="align: right"]5248373566[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]629.55[/TD]
[TD="align: right"]624.80[/TD]
[TD="align: right"]870506[/TD]
[TD="align: right"]543892149[/TD]
[TD="bgcolor: #ffcc99, align: right"]624.80[/TD]
[TD="bgcolor: #ccffcc, align: right"]870506[/TD]
[TD="align: right"]543892149[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]957.25[/TD]
[TD="align: right"]948.38[/TD]
[TD="align: right"]1048066[/TD]
[TD="align: right"]993964833[/TD]
[TD="bgcolor: #ffcc99, align: right"]948.38[/TD]
[TD="bgcolor: #ccffcc, align: right"]1048066[/TD]
[TD="align: right"]993964833[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]990.95[/TD]
[TD="align: right"]987.84[/TD]
[TD="align: right"]949759[/TD]
[TD="align: right"]938209931[/TD]
[TD="bgcolor: #ffcc99, align: right"]987.84[/TD]
[TD="bgcolor: #ccffcc, align: right"]949759[/TD]
[TD="align: right"]938209931[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]402.15[/TD]
[TD="align: right"]398.31[/TD]
[TD="align: right"]2141765[/TD]
[TD="align: right"]853086417[/TD]
[TD="bgcolor: #ffcc99, align: right"]398.31[/TD]
[TD="bgcolor: #ccffcc, align: right"]2141765[/TD]
[TD="align: right"]853086417[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]530.75[/TD]
[TD="align: right"]527.91[/TD]
[TD="align: right"]2795009[/TD]
[TD="align: right"]1475513201[/TD]
[TD="bgcolor: #ffcc99, align: right"]527.91[/TD]
[TD="bgcolor: #ccffcc, align: right"]2795009[/TD]
[TD="align: right"]1475513201[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]105.20[/TD]
[TD="align: right"]104.62[/TD]
[TD="align: right"]5946117[/TD]
[TD="align: right"]622082761[/TD]
[TD="bgcolor: #ffcc99, align: right"]104.62[/TD]
[TD="bgcolor: #ccffcc, align: right"]5946117[/TD]
[TD="align: right"]622082761[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]525.40[/TD]
[TD="align: right"]517.04[/TD]
[TD="align: right"]7051279[/TD]
[TD="align: right"]3645793294[/TD]
[TD="bgcolor: #ffcc99, align: right"]517.04[/TD]
[TD="bgcolor: #ccffcc, align: right"]7051279[/TD]
[TD="align: right"]3645793294[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]67.60[/TD]
[TD="align: right"]67.63[/TD]
[TD="align: right"]10467233[/TD]
[TD="align: right"]707898968[/TD]
[TD="bgcolor: #ffcc99, align: right"]67.63[/TD]
[TD="bgcolor: #ccffcc, align: right"]10467233[/TD]
[TD="align: right"]707898968[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]175.30[/TD]
[TD="align: right"]173.88[/TD]
[TD="align: right"]5788175[/TD]
[TD="align: right"]1006447869[/TD]
[TD="bgcolor: #ffcc99, align: right"]173.88[/TD]
[TD="bgcolor: #ccffcc, align: right"]5788175[/TD]
[TD="align: right"]1006447869[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]221.70[/TD]
[TD="align: right"]218.77[/TD]
[TD="align: right"]4759894[/TD]
[TD="align: right"]1041322010[/TD]
[TD="bgcolor: #ffcc99, align: right"]218.77[/TD]
[TD="bgcolor: #ccffcc, align: right"]4759894[/TD]
[TD="align: right"]1041322010[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]228.60[/TD]
[TD="align: right"]225.79[/TD]
[TD="align: right"]5472760[/TD]
[TD="align: right"]1235694480[/TD]
[TD="bgcolor: #ffcc99, align: right"]225.79[/TD]
[TD="bgcolor: #ccffcc, align: right"]5472760[/TD]
[TD="align: right"]1235694480[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]220.95[/TD]
[TD="align: right"]218.91[/TD]
[TD="align: right"]4499908[/TD]
[TD="align: right"]985074860[/TD]
[TD="bgcolor: #ffcc99, align: right"]218.91[/TD]
[TD="bgcolor: #ccffcc, align: right"]4499908[/TD]
[TD="align: right"]985074860[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]93.50[/TD]
[TD="align: right"]90.82[/TD]
[TD="align: right"]25952319[/TD]
[TD="align: right"]2356989612[/TD]
[TD="bgcolor: #ffcc99, align: right"]90.82[/TD]
[TD="bgcolor: #ccffcc, align: right"]25952319[/TD]
[TD="align: right"]2356989612[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]125.35[/TD]
[TD="align: right"]125.03[/TD]
[TD="align: right"]2569815[/TD]
[TD="align: right"]321303969[/TD]
[TD="bgcolor: #ffcc99, align: right"]125.03[/TD]
[TD="bgcolor: #ccffcc, align: right"]2569815[/TD]
[TD="align: right"]321303969[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]606.55[/TD]
[TD="align: right"]603.39[/TD]
[TD="align: right"]802286[/TD]
[TD="align: right"]484091350[/TD]
[TD="bgcolor: #ffcc99, align: right"]603.39[/TD]
[TD="bgcolor: #ccffcc, align: right"]802286[/TD]
[TD="align: right"]484091350[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]509.40[/TD]
[TD="align: right"]505.21[/TD]
[TD="align: right"]2656415[/TD]
[TD="align: right"]1342047422[/TD]
[TD="bgcolor: #ffcc99, align: right"]505.21[/TD]
[TD="bgcolor: #ccffcc, align: right"]2656415[/TD]
[TD="align: right"]1342047422[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]294.60[/TD]
[TD="align: right"]289.33[/TD]
[TD="align: right"]3210047[/TD]
[TD="align: right"]928762899[/TD]
[TD="bgcolor: #ffcc99, align: right"]289.33[/TD]
[TD="bgcolor: #ccffcc, align: right"]3210047[/TD]
[TD="align: right"]928762899[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]147.40[/TD]
[TD="align: right"]147.04[/TD]
[TD="align: right"]6636775[/TD]
[TD="align: right"]975871396[/TD]
[TD="bgcolor: #ffcc99, align: right"]147.04[/TD]
[TD="bgcolor: #ccffcc, align: right"]6636775[/TD]
[TD="align: right"]975871396[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]218.80[/TD]
[TD="align: right"]218.60[/TD]
[TD="align: right"]2490536[/TD]
[TD="align: right"]544431170[/TD]
[TD="bgcolor: #ffcc99, align: right"]218.60[/TD]
[TD="bgcolor: #ccffcc, align: right"]2490536[/TD]
[TD="align: right"]544431170[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]3697.00[/TD]
[TD="align: right"]3695.90[/TD]
[TD="align: right"]1138097[/TD]
[TD="align: right"]4206292702[/TD]
[TD="bgcolor: #ffcc99, align: right"]3695.90[/TD]
[TD="bgcolor: #ccffcc, align: right"]1138097[/TD]
[TD="align: right"]4206292702[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]2642.30[/TD]
[TD="align: right"]2627.38[/TD]
[TD="align: right"]741539[/TD]
[TD="align: right"]1948304738[/TD]
[TD="bgcolor: #ffcc99, align: right"]2627.38[/TD]
[TD="bgcolor: #ccffcc, align: right"]741539[/TD]
[TD="align: right"]1948304738[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]422.70[/TD]
[TD="align: right"]425.82[/TD]
[TD="align: right"]2397198[/TD]
[TD="align: right"]1020774852[/TD]
[TD="bgcolor: #ffcc99, align: right"]425.82[/TD]
[TD="bgcolor: #ccffcc, align: right"]2397198[/TD]
[TD="align: right"]1020774852[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]315.15[/TD]
[TD="align: right"]315.33[/TD]
[TD="align: right"]1543035[/TD]
[TD="align: right"]486565227[/TD]
[TD="bgcolor: #ffcc99, align: right"]315.33[/TD]
[TD="bgcolor: #ccffcc, align: right"]1543035[/TD]
[TD="align: right"]486565227[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]505.45[/TD]
[TD="align: right"]505.30[/TD]
[TD="align: right"]4870457[/TD]
[TD="align: right"]2461041922[/TD]
[TD="bgcolor: #ffcc99, align: right"]505.30[/TD]
[TD="bgcolor: #ccffcc, align: right"]4870457[/TD]
[TD="align: right"]2461041922[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]163.00[/TD]
[TD="align: right"]163.16[/TD]
[TD="align: right"]4836865[/TD]
[TD="align: right"]789182893[/TD]
[TD="bgcolor: #ffcc99, align: right"]163.16[/TD]
[TD="bgcolor: #ccffcc, align: right"]4836865[/TD]
[TD="align: right"]789182893[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]359.40[/TD]
[TD="align: right"]356.96[/TD]
[TD="align: right"]2330658[/TD]
[TD="align: right"]831951680[/TD]
[TD="bgcolor: #ffcc99, align: right"]356.96[/TD]
[TD="bgcolor: #ccffcc, align: right"]2330658[/TD]
[TD="align: right"]831951680[/TD]

[TD="bgcolor: #cacaca, align: center"]35[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]284.80[/TD]
[TD="align: right"]285.02[/TD]
[TD="align: right"]4220101[/TD]
[TD="align: right"]1202813187[/TD]
[TD="bgcolor: #ffcc99, align: right"]285.02[/TD]
[TD="bgcolor: #ccffcc, align: right"]4220101[/TD]
[TD="align: right"]1202813187[/TD]

[TD="bgcolor: #cacaca, align: center"]36[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]344.90[/TD]
[TD="align: right"]345.07[/TD]
[TD="align: right"]2647582[/TD]
[TD="align: right"]913601121[/TD]
[TD="bgcolor: #ffcc99, align: right"]345.07[/TD]
[TD="bgcolor: #ccffcc, align: right"]2647582[/TD]
[TD="align: right"]913601121[/TD]

[TD="bgcolor: #cacaca, align: center"]37[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]269.40[/TD]
[TD="align: right"]266.46[/TD]
[TD="align: right"]2353054[/TD]
[TD="align: right"]626994769[/TD]
[TD="bgcolor: #ffcc99, align: right"]266.46[/TD]
[TD="bgcolor: #ccffcc, align: right"]2353054[/TD]
[TD="align: right"]626994769[/TD]

[TD="bgcolor: #cacaca, align: center"]38[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]248.30[/TD]
[TD="align: right"]246.70[/TD]
[TD="align: right"]4084793[/TD]
[TD="align: right"]1007718433[/TD]
[TD="bgcolor: #ffcc99, align: right"]246.70[/TD]
[TD="bgcolor: #ccffcc, align: right"]4084793[/TD]
[TD="align: right"]1007718433[/TD]

[TD="bgcolor: #cacaca, align: center"]39[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]8132.75[/TD]
[TD="align: right"]8082.27[/TD]
[TD="align: right"]15907850[/TD]
[TD="align: right"]##########[/TD]
[TD="bgcolor: #ffcc99, align: right"]8082.27[/TD]
[TD="bgcolor: #ccffcc, align: right"]15907850[/TD]
[TD="align: right"]###########[/TD]

[TD="bgcolor: #cacaca, align: center"]40[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]16203.00[/TD]
[TD="align: right"]16076.80[/TD]
[TD="align: right"]3197900[/TD]
[TD="align: right"]##########[/TD]
[TD="bgcolor: #ffcc99, align: right"]16076.80[/TD]
[TD="bgcolor: #ccffcc, align: right"]3197900[/TD]
[TD="align: right"]51411998720[/TD]

[TD="bgcolor: #cacaca, align: center"]41[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]292.20[/TD]
[TD="align: right"]288.50[/TD]
[TD="align: right"]1544928[/TD]
[TD="align: right"]445711728[/TD]
[TD="bgcolor: #ffcc99, align: right"]288.50[/TD]
[TD="bgcolor: #ccffcc, align: right"]1544928[/TD]
[TD="align: right"]445711728[/TD]

[TD="bgcolor: #cacaca, align: center"]42[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]413.90[/TD]
[TD="align: right"]413.22[/TD]
[TD="align: right"]4280174[/TD]
[TD="align: right"]1768653500[/TD]
[TD="bgcolor: #ffcc99, align: right"]413.22[/TD]
[TD="bgcolor: #ccffcc, align: right"]4280174[/TD]
[TD="align: right"]1768653500[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
C2=IF((F2-I2)=0,0,((G2-J2)/(F2-I2)))
D2='7'!H22
E2='7'!L22
F2='7'!M22
G2=F2*E2
H2='D3'!C2
I2='D3'!C43
J2=I2*H2
C3=IF((F3-I3)=0,0,((G3-J3)/(F3-I3)))
D3='7'!H16
E3='7'!L16
F3='7'!M16
G3=F3*E3
H3='D3'!C3
I3='D3'!C44
J3=I3*H3
C4=IF((F4-I4)=0,0,((G4-J4)/(F4-I4)))
D4='7'!H18
E4='7'!L18
F4='7'!M18
G4=F4*E4
H4='D3'!C4
I4='D3'!C45
J4=I4*H4
C5=IF((F5-I5)=0,0,((G5-J5)/(F5-I5)))
D5='7'!H14
E5='7'!L14
F5='7'!M14
G5=F5*E5
H5='D3'!C5
I5='D3'!C46
J5=I5*H5
C6=IF((F6-I6)=0,0,((G6-J6)/(F6-I6)))
D6='7'!H26
E6='7'!L26
F6='7'!M26
G6=F6*E6
H6='D3'!C6
I6='D3'!C47
J6=I6*H6
C7=IF((F7-I7)=0,0,((G7-J7)/(F7-I7)))
D7='7'!H28
E7='7'!L28
F7='7'!M28
G7=F7*E7
H7='D3'!C7
I7='D3'!C48
J7=I7*H7
C8=IF((F8-I8)=0,0,((G8-J8)/(F8-I8)))
D8='7'!H30
E8='7'!L30
F8='7'!M30
G8=F8*E8
H8='D3'!C8
I8='D3'!C49
J8=I8*H8
C9=IF((F9-I9)=0,0,((G9-J9)/(F9-I9)))
D9='7'!H20
E9='7'!L20
F9='7'!M20
G9=F9*E9
H9='D3'!C9
I9='D3'!C50
J9=I9*H9
C10=IF((F10-I10)=0,0,((G10-J10)/(F10-I10)))
D10='7'!H24
E10='7'!L24
F10='7'!M24
G10=F10*E10
H10='D3'!C10
I10='D3'!C51
J10=I10*H10
C11=IF((F11-I11)=0,0,((G11-J11)/(F11-I11)))
D11='7'!H58
E11='7'!L58
F11='7'!M58
G11=F11*E11
H11='D3'!C11
I11='D3'!C52
J11=I11*H11
C12=IF((F12-I12)=0,0,((G12-J12)/(F12-I12)))
D12='7'!H75
E12='7'!L75
F12='7'!M75
G12=F12*E12
H12='D3'!C12
I12='D3'!C53
J12=I12*H12
C13=IF((F13-I13)=0,0,((G13-J13)/(F13-I13)))
D13='7'!H46
E13='7'!L46
F13='7'!M46
G13=F13*E13
H13='D3'!C13
I13='D3'!C54
J13=I13*H13
C14=IF((F14-I14)=0,0,((G14-J14)/(F14-I14)))
D14='7'!H48
E14='7'!L48
F14='7'!M48
G14=F14*E14
H14='D3'!C14
I14='D3'!C55
J14=I14*H14
C15=IF((F15-I15)=0,0,((G15-J15)/(F15-I15)))
D15='7'!H50
E15='7'!L50
F15='7'!M50
G15=F15*E15
H15='D3'!C15
I15='D3'!C56
J15=I15*H15
C16=IF((F16-I16)=0,0,((G16-J16)/(F16-I16)))
D16='7'!H54
E16='7'!L54
F16='7'!M54
G16=F16*E16
H16='D3'!C16
I16='D3'!C57
J16=I16*H16
C17=IF((F17-I17)=0,0,((G17-J17)/(F17-I17)))
D17='7'!H52
E17='7'!L52
F17='7'!M52
G17=F17*E17
H17='D3'!C17
I17='D3'!C58
J17=I17*H17
C18=IF((F18-I18)=0,0,((G18-J18)/(F18-I18)))
D18='7'!H64
E18='7'!L64
F18='7'!M64
G18=F18*E18
H18='D3'!C18
I18='D3'!C59
J18=I18*H18
C19=IF((F19-I19)=0,0,((G19-J19)/(F19-I19)))
D19='7'!H66
E19='7'!L66
F19='7'!M66
G19=F19*E19
H19='D3'!C19
I19='D3'!C60
J19=I19*H19
C20=IF((F20-I20)=0,0,((G20-J20)/(F20-I20)))
D20='7'!H69
E20='7'!L69
F20='7'!M69
G20=F20*E20
H20='D3'!C20
I20='D3'!C61
J20=I20*H20
C21=IF((F21-I21)=0,0,((G21-J21)/(F21-I21)))
D21='7'!H72
E21='7'!L72
F21='7'!M72
G21=F21*E21
H21='D3'!C21
I21='D3'!C62
J21=I21*H21
C22=IF((F22-I22)=0,0,((G22-J22)/(F22-I22)))
D22='7'!H81
E22='7'!L81
F22='7'!M81
G22=F22*E22
H22='D3'!C22
I22='D3'!C63
J22=I22*H22
C23=IF((F23-I23)=0,0,((G23-J23)/(F23-I23)))
D23='7'!H84
E23='7'!L84
F23='7'!M84
G23=F23*E23
H23='D3'!C23
I23='D3'!C64
J23=I23*H23
C24=IF((F24-I24)=0,0,((G24-J24)/(F24-I24)))
D24='7'!H87
E24='7'!L87
F24='7'!M87
G24=F24*E24
H24='D3'!C24
I24='D3'!C65
J24=I24*H24
C25=IF((F25-I25)=0,0,((G25-J25)/(F25-I25)))
D25='7'!H90
E25='7'!L90
F25='7'!M90
G25=F25*E25
H25='D3'!C25
I25='D3'!C66
J25=I25*H25
C26=IF((F26-I26)=0,0,((G26-J26)/(F26-I26)))
D26='7'!H108
E26='7'!L108
F26='7'!M108
G26=F26*E26
H26='D3'!C26
I26='D3'!C67
J26=I26*H26
C27=IF((F27-I27)=0,0,((G27-J27)/(F27-I27)))
D27='7'!H93
E27='7'!L93
F27='7'!M93
G27=F27*E27
H27='D3'!C27
I27='D3'!C68
J27=I27*H27
C28=IF((F28-I28)=0,0,((G28-J28)/(F28-I28)))
D28='7'!H96
E28='7'!L96
F28='7'!M96
G28=F28*E28
H28='D3'!C28
I28='D3'!C69
J28=I28*H28
C29=IF((F29-I29)=0,0,((G29-J29)/(F29-I29)))
D29='7'!H99
E29='7'!L99
F29='7'!M99
G29=F29*E29
H29='D3'!C29
I29='D3'!C70
J29=I29*H29
C30=IF((F30-I30)=0,0,((G30-J30)/(F30-I30)))
D30='7'!H102
E30='7'!L102
F30='7'!M102
G30=F30*E30
H30='D3'!C30
I30='D3'!C71
J30=I30*H30
C31=IF((F31-I31)=0,0,((G31-J31)/(F31-I31)))
D31='7'!H105
E31='7'!L105
F31='7'!M105
G31=F31*E31
H31='D3'!C31
I31='D3'!C72
J31=I31*H31
C32=IF((F32-I32)=0,0,((G32-J32)/(F32-I32)))
D32='7'!H42
E32='7'!L42
F32='7'!M42
G32=F32*E32
H32='D3'!C32
I32='D3'!C73
J32=I32*H32
C33=IF((F33-I33)=0,0,((G33-J33)/(F33-I33)))
D33='7'!H44
E33='7'!L44
F33='7'!M44
G33=F33*E33
H33='D3'!C33
I33='D3'!C74
J33=I33*H33
C34=IF((F34-I34)=0,0,((G34-J34)/(F34-I34)))
D34='7'!H111
E34='7'!L111
F34='7'!M111
G34=F34*E34
H34='D3'!C34
I34='D3'!C75
J34=I34*H34
C35=IF((F35-I35)=0,0,((G35-J35)/(F35-I35)))
D35='7'!H119
E35='7'!L119
F35='7'!M119
G35=F35*E35
H35='D3'!C35
I35='D3'!C76
J35=I35*H35
C36=IF((F36-I36)=0,0,((G36-J36)/(F36-I36)))
D36='7'!H117
E36='7'!L117
F36='7'!M117
G36=F36*E36
H36='D3'!C36
I36='D3'!C77
J36=I36*H36
C37=IF((F37-I37)=0,0,((G37-J37)/(F37-I37)))
D37='7'!H121
E37='7'!L121
F37='7'!M121
G37=F37*E37
H37='D3'!C37
I37='D3'!C78
J37=I37*H37
C38=IF((F38-I38)=0,0,((G38-J38)/(F38-I38)))
D38='7'!H123
E38='7'!L123
F38='7'!M123
G38=F38*E38
H38='D3'!C38
I38='D3'!C79
J38=I38*H38
C39=IF((F39-I39)=0,0,((G39-J39)/(F39-I39)))
D39='7'!H2
E39='7'!L2
F39='7'!M2
G39=F39*E39
H39='D3'!C39
I39='D3'!C80
J39=I39*H39
C40=IF((F40-I40)=0,0,((G40-J40)/(F40-I40)))
D40='7'!H3
E40='7'!L3
F40='7'!M3
G40=F40*E40
H40='D3'!C40
I40='D3'!C81
J40=I40*H40
C41=IF((F41-I41)=0,0,((G41-J41)/(F41-I41)))
D41='7'!H126
E41='7'!L126
F41='7'!M126
G41=F41*E41
H41='D3'!C41
I41='D3'!C82
J41=I41*H41
C42=IF((F42-I42)=0,0,((G42-J42)/(F42-I42)))
D42='7'!H129
E42='7'!L129
F42='7'!M129
G42=F42*E42
H42='D3'!C42
I42='D3'!C83
J42=I42*H42

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML

Excel tables to the web >> Excel
 
Upvote 0
Hi hansdeep I've had a quick look at this and the obvious issue is
1) how is the 3 in column B being entered? manually, by formula, by macro, by import or by some other method? By formula screenshot attached
Click to expand...

I am sure you have seen in other posts that you can't trigger a macro by a change in a formula result. You can use the Calculate event, application ontime etc. but if your sheet is being updated every "second/milli-seconds" (actually it is either one or the other and it is a big difference) then either method could kill your sheet performance.

This is why I am interested in how the data is being pulled in.
There is no macros or real time data 'directly' feeded in the current ws which requires your code & which is tab named "D3
Click to expand...
It doesn't matter what sheet the data is being pulled/fed into, what matters is the method being used and whether we can use the feed to trigger the macro (if it is being pulled in by a macro it will make life easier :)).

Basically I need you have a think about how you want the macro to be triggered
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Combining formulae for UK postcodes
2
Replies
10
Views
666
Fluff
Fluff
M
  • Question Question
Macro to send data from "Input" sheet to "Data" sheet
2
Replies
10
Views
1K
HaHoBe
HaHoBe
R
  • Question Question
Help! VBA to loop through a list containing data references to update forecast sheets automatically
Replies
3
Views
461
Domenic
D
T
  • Solved
Formula Causes Memory Leak...Sometimes?!? Help me diagnose
Replies
5
Views
228
Tarkemelion
T
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
232
dshtesy
D

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top