Defining Names

[kenjohnson_342]

I know how to define names ie. a1:a100 named Employee.

But I'll have different tabs for the different months, I can't seem to find a way to define Employee as january!a1:a100, february!a1:a100, march!a1:a100 etc.....

Any pointers on that or is that a no go?

 

[Richard Schollar]

Hi Ken

Do you mean you want to define a name "Employee" on each individual sheet? In which case, in Insert>Name>Define you need to give the name in the form of SheetName!Name. ie select the range on the individual sheet (say January) and go Insert>Name>Define and type the name as:

January!Employee

and do the same for the other sheets.

 

[kenjohnson_342]

No, actually what I want is the "name" to equal column A from all 12 sheets together, not 12 seperate names. I'm using a sumproduct formula to generate a count of referrals given by various employees.
I have to have each month listed seperately, but I also need to have a summary page which will tabulate a YTD type total.

In order to accomplish this I would have to use 12 SUMPRODUCT formulas. I was hoping that if I renamed A1:a100 for all 12 sheets that would streamline it. Also, is Excel even doing to let me use 12 SUMPRODUCT formulas? Isn't there a limit of 7 you can have?

Any of this make sense?
Thanks for the help.

 

[Richard Schollar]

I'm using a sumproduct formula to generate a count of referrals given by various employees.

What is the construction of the sumproduct formula you are using (please post a sample formula)? You can't directly use 3D references in Sumproduct, but in some cases there is a relatively straightforward workaround (with an embedded SUMIF)

 

[kenjohnson_342]

On monthy sheet:
Column A "Employee"..........Column B "Referral"
Jane.................product1
Jane.................product2
Jane.................product1
john.................product2

Summary Sheet:
Employee......Product1......Product2
Jane.............=count.........=count
John.............=count.........=count

Again, each month would be entered on a seperate sheet, but the summary needs to pull the info from all 12 sheets as a total.

Currently the people who created this report manually entered info on the month sheet, then MANUALLY calculated the totals to enter on the summary sheets. No formulas, no automation at all. You can see there wasa a lot of extra manual entry and human error calculations.
Thanks.

Forgot the formula: =SUMPRODUCT((January!$A$4:$A$20="Jane")*(January!$F$4:$F$20="Product1"))

Ideally it would read =SUMPRODUCT((Employee="Jane")*(Product=Product1))
Ideally it would again pull all 12 months with something this simple.

 

[Richard Schollar]

Ken

I don't think the workaround I had in mind will work with this. There is a way to do this though if you can download the morefunc add-in (see the Recommended Links and Downloads link at the top of the forum) and then you can use the THREED function. Is this a possibility for you?

 

[skahilu]

Hi,
There is a way to put same named range on different sheets but I cant configure out how.

 

[Domenic]

Try the following...

Insert > Name > Define

Name: SheetList

Refers to:

=TEXT(DATE(2007,TRANSPOSE(ROW(INDIRECT("1:12"))),1),"mmmm")

Click Ok

On your summary page, let A2 and A3 contain Jane and John, and let B1 and C1 contain Product1 and Product2. Then try...

B2, copied down and across:

=SUMPRODUCT(--(T(OFFSET(INDIRECT(SheetList&"!A4:A20"),ROW(INDIRECT("4:20"))-4,,1))=$A2),--(T(OFFSET(INDIRECT(SheetList&"!F4:F20"),ROW(INDIRECT("4:20"))-4,,1))=B$1))

Note that I've assumed that the product name is a text value. If it's in fact a numerical value, replace the second T() with N()...

--(N(OFFSET(INDIRECT(.....))))

Note, however, it would be much more efficient to use the THREED function, as Richard has suggested.

Hope this helps!