Defining Name Range Across Multiple Sheets

  • Good morning all.


    Not sure where to look for help on this issue as no matter what I type in to help files, nothing seems to pop up.


    I have a statistics page in a file that captures data from 12 data sheets.
    I want to be able to look for a value over the entire 12 sheets.


    Column D on all 12 sheets have dates.
    I would like to return a count if any cell in column D across the 12 sheets has a specific value.


    ie .. how many cells have a value of 1 Sept.


    Any ideas?
    Have tried VLOOKUP and COUNTIF .. but cant seem to get them to work across multiple sheets.


    HELP !!

    Tayler [the blue fox] :thanx:

  • Re: Defining Name Range Across Multiple Sheets


    HI


    AFAIK the standard functions of this type will only work on the one sheet. Some thoughts.


    1) Build the function for each sheet, and add (countif(sheet2!a:A,"xx") + countif(Sheet3!a:a,"xx") +.....
    2) I've not used them, but I believe there are functions in the Morefunc addins that will work across multiple sheets.
    3) Build your own User Defined Function (UDF) that will work for your specific set of circumstances.



    HTH


    Tony

  • Re: Defining Name Range Across Multiple Sheets


    You can try this

    Code
    1. =COUNTA(Sheet1:Sheet12!D:D,Sheet1!F1)-1

    Sheet1!F1 would be where your value you are looking for would be, so if you were looking for the word "fred" type that in Sheet1 F1 and it will count all instances on sheets 1 - 12 column D.


    Hope it helps!


    Regards,
    Simon