Posts by naira

    Re: Code used to work, now takes forever and crashes?

    Quote from stildawn;703186

    Ah ok I was wondering something like this? Would this be happening of lots of different peoples computers though? There are a few other simple lines of code that have also started giving me grief, usually around copying also, those I have fixed by just writing more efficient code. But if this temp folder is the cause then that would explain them all?

    Dont know how I am going to do this on everyones computers though haha.

    Well if the solution I have given helps, you could create a simple .bat file to clear the temp folder and send it out to everyone. Of course, the .bat file will have to be customized at least once for each PC Login ID, but end of the day, each user need not go the folder everyday to clear the temp folder.

    Do let us know what worked?

    Re: Simplify formula to assign unique Ranks to Col range with duplicate values and er

    Quote from NBVC;703194

    or you can change the formula in B2 to:


    copied down. Which makes the arithmetic a bit more precise.

    Setting the decimal places helps. I have been using the Round and Fixed functions quite extensively, but somehow missed the point this time.

    Thanks a lot once again for your prompt reply.

    Re: Simplify formula to assign unique Ranks to Col range with duplicate values and er

    Hi NBVC,

    Not sure if this new issue falls in your domain expertise or not, but since it is related to the solution you gave...

    Anyways, I am attaching another sample file where your formula is not able to take care of the duplicates and shows duplicate ranks. The data in it has been copied from my actual work file so the issue is real.

    The most amazingly interesting thing is, that if I copy the same set of data to a notepad file and then copy it back to the same Col. A from which I took it, the formula works.

    Sample file with duplicates in ranks attached.

    Can you please help me out on this one too?

    - Naira

    Re: 2 Sparklines in a single cell in excel?

    Hi Pc123

    Thanks for you reply. However, what you have done is create a small chart for overlay on the sparkline in the background. I already knew that option but I wont be able to use it.

    Any other ideas?

    Re: Code used to work, now takes forever and crashes?

    See if this helps

    Apart from obviously restarting your PC and checking that there aren't any memory hogging softwares running in the background you could clear the Excel cache folder.

    Using Windows 7 64 Bit and Excel 2010, mine is located in the folder C:\Users\Current User\AppData\Roaming\Microsoft\Excel (replace Current User with the actual logged in username). AppData folder is actually hidden by windows by default so you will have manually type that in the address bar.

    Using windows command prompt change attribute of all hidden files to unhidden inside C:\Users\Current User\AppData\Roaming\Microsoft\Excel and then delete everything inside the Excel folder (making sure Excel is not running at the time).

    Come one folder higher and check that the Folder size of Excel folder is zero bytes.


    I have data in 2 columns as follows:

    Col. ACol. B
    10 15
    10 14
    10 15
    6 13
    12 5
    14 8
    10 10
    13 6
    9 14
    10 9

    Data in Col. A is converted as a line sparkline and data in Col. B is viewed as a Column sparkline.

    I wish to view both the sparklines superimposed on each other in the same cell using excel's native sparkline option (not a third party software).
    Is it possible using some sort of Excel wizardry?

    Sample file attached



    I have data with duplicate values and errors in a Col. range as:


    In order to get a clean list with unique values and without errors (row with errors replaced with blank cells in end result), I am undertaking following steps:

    1. I use


    to remove errors from original list;

    2. I use


    to rank data (this assigns duplicate ranks to same values and gives errors for blank cells);

    3. I use


    to remove errors from rank list;

    4. I use


    to assign unique ranks to duplicate values;

    5. I use


    to remove errors from unique rank list

    I was wondering if the above steps can be reduced/ no. of columns/ calculations reduced to get a clean list without errors with unique ranks assigned to duplicate values

    Sample file attached.



    I have an excel sheet where certain cells in Col. A, B, C need to be highlighted with conditional formatting using colour fill in cells.

    However, the cells in which conditional formatting is to be applied, is to be determined by cell addresses given in Col. E.
    Is it possible to determine the cells to be conditionally formatted, using addresses given in cells in Col. E with Indirect function in Conditional Formatting OR any other method to arrive at the result?

    Sample file attached.



    I have a set of month-wise data from which I need to draw sparklines using native excel sparkline option, in multiple sheets of Excel.


    If A1= Jan, then sparkline corresponding to data in column J to be drawn in cell B1
    If A1= Feb, then sparkline corresponding to data in column K to be drawn in cell B1
    If A1= Mar, then sparkline corresponding to data in column L to be drawn in cell B1

    Above need to be done WITHOUT creating an additional column of hlookups for the dynamic named range

    Formula used for my dynamic range is


    Can this formula be made dynamic (using Indirect or something similar) so that the the named range (press Ctrl+F3 to see named range) can pick values from some other cell and change column references to Col. J, K, L etc. and not just be stuck to J2 and J2:J50

    Sample file attached.

    Note: The thread is a continuation of another thread posted on this forum at: That thread is closed since I feel I was asking the wrong question there.



    Re: How to reduce lookups for drawing Sparklines?

    hi pike!
    Thanks for your reply, but no, using VBA is not an option, since I need to draw about 150 sparklines each in about 5 sheets, so that means about 750 sparklines.

    I am however now of the opinion that a solution could probably come out by using Indirect function with named ranges and referencing the ranges with the name of the month in a cell.

    Can someone give any pointers how to use indirect function with dynamic named ranges to draw the sparklines?


    I have a set of month-wise data of about 5 years from which I need to draw sparklines using native excel sparkline option, in multiple sheets of Excel.
    For example, in case I want to draw spakline for Jan (in cell B1), I will first do a hlookup (in colum R) of the data of the month (Jan entered in cell A1), then assign a dynamic named range to this column and then finally, draw a sparkline for this data.

    The problem is that in case I want to draw sparklines for Jan to Dec in different sequnces in 20 different sheets, I have to create 12 x 20 dynamic named ranges (which translates to 12 x 20 x 31 = 7440 hlookups).

    Is is possible to draw or pull the sparkline for Jan on all 20 different sheets using just one dynamic named range?


    Is there any other method to reduce/ or simple do away with the number of hlookups for drawing these Sparklines?


    Can I just do without the lookups? For example, is it possible to create named ranges / sparklines for all the months in one sheet and then just pull them in cell B1, based on dynamically changing value in Cell A1 without having to create separate columns for hlookups for the named ranges?
    Above is just a simplified version of my problem. In real case scenario, I have RTD data of about 500 rows for 200 different machines, and I need these in different row sequences in 5 sheets, all updating on a real time basis.

    This is translating into 500 x 200 x 5 = 500,000 hlookups for just 200 sparklines.
    This is slowing down my calculations a lot.

    Sample file attached.



    Re: Macro that runs when System Clock changes minute?

    Sorry for late reply guys, but after some extensive testing over 2 days on live data and some minor tweaking, it seems that the Solution suggested by jindon above in #18 is the best available, since it only starts when the system clock minute changes and is also a one line solution.

    Thanks a lot for all your help guys.
    Thread solved.

    PS: Another forum has an option to mark the thread as solved. Can we have the same option here too in the thread tools?

    Re: Macro that runs when System Clock changes minute?

    Quote from holycow;684090

    Hi Naira
    I think I have it working just give me a minute. Blacklist was harsh.

    Thank you for your time and effort Holycow! Yup, blacklist was a bit harsh for a first DUI violence ;) You actually managed to spoil an entire evening here, until I came back to apologize. Anyways, I guess it does come with the territory of people working hard to do some selfless help. Will test your new code and let you know in about 2-3 hours. Thanx once again.

    Re: Macro that runs when System Clock changes minute?

    Request posted on…ges-minute.html?p=3405466 and, since there was no reply for 2 days on this forum. Sorry for irritating people's sensitivities for not indicating the cross-post threads, but if one checked, they would know that each post was done after waiting for atleast a day. Also, this thread was bumped up waiting for a reply. Anyways, I guess no excuses for violating rules for not indicating cross-posts so Sorry once again. PS: Still waiting for a working solution, none has come forward from any of the 3 forums as on Sept-16th 2013.

    Re: Macro that runs when System Clock changes minute?

    Quote from holycow;684019

    apo I wonder if you could download OP's workbook from Post #3 and then apply the code in Post #5 to OP's scenario. It's doing something odd and I can't figure out why. It is producing about 30-40 lines of data at time system clock changes. This is no doubt due to the modifications that I did to the code.

    Hi holycow/apo,

    I am also noticing the same thing as pointed out by holycow.

    It seems that the solution suggested in post # 3 above is actually making the Macro run continuously between 59th second and 60th second. For example macro starts running at 09:00:59 and runs continuosly for 1 second till 09:01:00.

    This can be easily demonstrated if I copy the time in subsequent rows using mm:ss.0 format

    If I use a huge file to run this macro, it shows maybe one or 2 runs, but for a smaller file it can show up to 450 run.
    Below is a more simplified version of holycow's macro (sample file also attached) to show these continuous runs.

    Option Explicit
    Public dTime As Date
    Sub Clock()
        Sheets("Sheet1").Range("C4:C200").Value = Sheets("Sheet1").Range("C3:C200").Value
        dTime = Now + TimeValue("00:00:" & 59 - Right(Format(Time, "hh:mm:ss"), 2))
        Application.OnTime dTime, "Clock"
    End Sub

    Any debugs so that it runs only once?