Compare Cell Values Between Multiple Columns

  • I have a workbook with multiple sheets. On the different sheets there are multiple columns of data all set up with a column of dates along side them.

    Something like:

    199901 | 3.5
    199902 | 4.2
    199903 | 6.2

    On the first sheet are statistics that I've incorporated into a macro to automate chart creation. On the second sheet there are 3 sets of columns. The first set has the date in annual format, the second in monthly format, and the third in quarterly format. There is a designation for what the format the date is in on the first worksheet.

    I am looking for a way to search a column (C1:C13 usually) on sheet1 to find a cell containing one of the three formats (Monthly, Quarterly, Annual) and then go to the first numerical value in the column and then go through the corresponding column on the second sheet and find that value to start a chart range.

    If anyone can help with this I'd be very grateful, I can't seem to find an example that does this, and when I try to combine code I've seen elsewhere I don't get the results I'm looking for.

  • Re: Compare Cell Values Between Multiple Columns


    I bet that makes perfect sense to you but, I can't quite get it. Can you send a sample sheet that has the expected results? It sounds like you want to identify the data range for a chart based on what format a cell uses. I'm guessing there's a better way to do it though so post a sample please.


  • Re: Compare Cell Values Between Multiple Columns

    Attached a fragment of the workbook.

    Included the formatting (the stats are exported using a database add-in).

    1 column on first sheet

    3 columns on second sheet

    First sheet: in cell C11 it defines the date format as being quarterly. This definition will not always be in the same place (I don't have control of its placement in all conditions).

    In this case the dates on the second sheet match those on the first. But in reality there will be dates from the 1940s to the present on one or both sheets. I need to find a way to match the correct starting date from B13 on sheet 1 to the matching date in the correct format column on the second page. ( I need to do this so that I can apply a recession bar to a line graph made from the figures on the first chart.)

    Hope this helps make it clear, couldn't think of any better way to describe the problem. I could just be over analyzing it though, its been known to happen.

  • Re: Compare Cell Values Between Multiple Columns


    I'm still a little fuzzy but, here's a couple of thoughts anyway.

    The frequency (.FRQ) can be searched in a vlookup formula on the first sheet and the word two columns to the right can be returned like so:

    The result of this formula will tell you whether to look in the Quarterly place the Yearly place or the Monthly place. You can specify the places with a range name and that gets you the ability to refer to the place by finding the word ".FRQ" on the first sheet.

    Does that help?


Participate now!

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