How to reduce lookups for drawing Sparklines?

  • hi!

    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: How to reduce lookups for drawing Sparklines?

    Hi naira

    Can you automate with vba to select the worksheet and range to graph?

  • 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?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!