3-D Reference
 

Dear NG:

Has anyone been able to get this 3-D Reference to work?

This is an excerpt from the Help file telling you what to do:

Name cells on more than one worksheet by using a 3-D reference
1.. On the Insert menu, point to Name, and then click Define.
2.. In the Names in workbook box, type the name.
3.. If the Refers to box contains a reference, select the equal sign (=)
and the reference and press BACKSPACE.
4.. In the Refers to box, type = (an equal sign).
5.. Click the tab for the first worksheet to be referenced.
6.. Hold down SHIFT and click the tab for the last worksheet to be
referenced.
7.. Select the cell or range of cells to be referenced.
I've followed the directions and the range (Tag_Number) IS added to the list
of "Names in workbook"
in the "Define Name" dialog box. In the refers to box is:
=January:December!$C:$C

The range however is not listed in the drop down box in the upper left
corner of the worksheets
and it does not work in formulas.

Anyone had any success with these types of ranges?

Any help you could pass along would be appreciated.

Thanks,

-Kevin

3-D Reference
 

In , Kevin
spake thusly:

Name cells on more than one worksheet by using a 3-D reference
[snip]
Anyone had any success with these types of ranges?

Yes, they do work. I strongly recommend you find and download
the free Add-in, Name Manager, which will help you wonderfully
with this sort of stuff.

http://www.decisionmodels.com/downloads.htm
http://www.jkp-ads.com/OfficeMarketPlacenm-en.asp

--
dman

3-D Reference
 

Kevin,

To do it the Mcrosoft way try this

Inset|name|define

in the the 'refer to bar' click the coloured box on the right.
navigate to your first cell
click the coloured box again
Navigate to the next worksheet and note that the worksheet name has been added
in the the 'refer to bar' click the coloured box on the right.
navigate to your second cell

repeat this remembering whenever you selecy a cell the refers to bar is
minimised. whenever you change sheets it's maximised.


Finally type a name and add.

Mike







"Kevin" wrote:

Dear NG:

Has anyone been able to get this 3-D Reference to work?

This is an excerpt from the Help file telling you what to do:

Name cells on more than one worksheet by using a 3-D reference
1.. On the Insert menu, point to Name, and then click Define.
2.. In the Names in workbook box, type the name.
3.. If the Refers to box contains a reference, select the equal sign (=)
and the reference and press BACKSPACE.
4.. In the Refers to box, type = (an equal sign).
5.. Click the tab for the first worksheet to be referenced.
6.. Hold down SHIFT and click the tab for the last worksheet to be
referenced.
7.. Select the cell or range of cells to be referenced.
I've followed the directions and the range (Tag_Number) IS added to the list
of "Names in workbook"
in the "Define Name" dialog box. In the refers to box is:
=January:December!$C:$C

The range however is not listed in the drop down box in the upper left
corner of the worksheets
and it does not work in formulas.

Anyone had any success with these types of ranges?

Any help you could pass along would be appreciated.

Thanks,

-Kevin

3-D Reference
 

Put some numbers in Column C of your January to December sheets.

What does

=Sum(Tag_Number)

return?

Works for me!

What you have here is actually a named *formula*.

Revise your formula by clicking on "Tag_Number" in the Define Name Window.

Then click in the "Refers To" box, and hit <F2 to enter the edit mode.

Revise the formula to this:

=SUM(Jan:Dec!C:C)

Then OK out.

Now, try this in any cell:

=Tag_Number

You should get the total of values in all your Column C's, just as you got
with using:

=Sum(Tag_Number)
--

HTH,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Kevin" wrote in message
...
Dear NG:

Has anyone been able to get this 3-D Reference to work?

This is an excerpt from the Help file telling you what to do:

Name cells on more than one worksheet by using a 3-D reference
1.. On the Insert menu, point to Name, and then click Define.
2.. In the Names in workbook box, type the name.
3.. If the Refers to box contains a reference, select the equal sign (=)
and the reference and press BACKSPACE.
4.. In the Refers to box, type = (an equal sign).
5.. Click the tab for the first worksheet to be referenced.
6.. Hold down SHIFT and click the tab for the last worksheet to be
referenced.
7.. Select the cell or range of cells to be referenced.
I've followed the directions and the range (Tag_Number) IS added to the list
of "Names in workbook"
in the "Define Name" dialog box. In the refers to box is:
=January:December!$C:$C

The range however is not listed in the drop down box in the upper left
corner of the worksheets
and it does not work in formulas.

Anyone had any success with these types of ranges?

Any help you could pass along would be appreciated.

Thanks,

-Kevin

3-D Reference
 

RagDyeR,

Well it's a start. It DOES work as you say with =SUM but so far I still
can't get it to work
as part of an =IF function.

=IF(COUNTIF(Tag_Number,15),15,"No Match"

I'll keep plugging at it.

Thanks for your help.

-Kevin

"RagDyeR" wrote in message
...
Put some numbers in Column C of your January to December sheets.

What does

=Sum(Tag_Number)

return?

Works for me!

What you have here is actually a named *formula*.

Revise your formula by clicking on "Tag_Number" in the Define Name Window.

Then click in the "Refers To" box, and hit <F2 to enter the edit mode.

Revise the formula to this:

=SUM(Jan:Dec!C:C)

Then OK out.

Now, try this in any cell:

=Tag_Number

You should get the total of values in all your Column C's, just as you got
with using:

=Sum(Tag_Number)
--

HTH,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Kevin" wrote in message
...
Dear NG:

Has anyone been able to get this 3-D Reference to work?

This is an excerpt from the Help file telling you what to do:

Name cells on more than one worksheet by using a 3-D reference
1.. On the Insert menu, point to Name, and then click Define.
2.. In the Names in workbook box, type the name.
3.. If the Refers to box contains a reference, select the equal sign (=)
and the reference and press BACKSPACE.
4.. In the Refers to box, type = (an equal sign).
5.. Click the tab for the first worksheet to be referenced.
6.. Hold down SHIFT and click the tab for the last worksheet to be
referenced.
7.. Select the cell or range of cells to be referenced.
I've followed the directions and the range (Tag_Number) IS added to the
list
of "Names in workbook"
in the "Define Name" dialog box. In the refers to box is:
=January:December!$C:$C

The range however is not listed in the drop down box in the upper left
corner of the worksheets
and it does not work in formulas.

Anyone had any success with these types of ranges?

Any help you could pass along would be appreciated.

Thanks,

-Kevin

3-D Reference
 

XL is not too good with 3D references.

Try working with this type of procedu

Create a list of your WS names in an out-of-the-way location, say Z1 to Z12.
Make sure that the spelling and/or spacing is *exact* to what's on the tabs.

Select this range and click in the name box and enter a name, say
"Tag_Number", (no quotes), and hit enter.

Then, try this:

=SUMPRODUCT(COUNTIF(INDIRECT(Tag_Number&"!C:C"),15 ))

If your WS names might contain spaces, this addition of apostrophes is
necessary, and can be used with names without spaces, "just in case".

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Tag_Number&"'!C:C "),15))

Play around and see what you come up with, for example:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Tag_Number&"'!C:C") ,15))

=SUMPRODUCT(SUMIF(INDIRECT("'"&Tag_Number&"'!C:C") ,"15"))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Kevin" wrote in message
...
RagDyeR,

Well it's a start. It DOES work as you say with =SUM but so far I still
can't get it to work
as part of an =IF function.

=IF(COUNTIF(Tag_Number,15),15,"No Match"

I'll keep plugging at it.

Thanks for your help.

-Kevin

"RagDyeR" wrote in message
...
Put some numbers in Column C of your January to December sheets.

What does

=Sum(Tag_Number)

return?

Works for me!

What you have here is actually a named *formula*.

Revise your formula by clicking on "Tag_Number" in the Define Name Window.

Then click in the "Refers To" box, and hit <F2 to enter the edit mode.

Revise the formula to this:

=SUM(Jan:Dec!C:C)

Then OK out.

Now, try this in any cell:

=Tag_Number

You should get the total of values in all your Column C's, just as you got
with using:

=Sum(Tag_Number)
--

HTH,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Kevin" wrote in message
...
Dear NG:

Has anyone been able to get this 3-D Reference to work?

This is an excerpt from the Help file telling you what to do:

Name cells on more than one worksheet by using a 3-D reference
1.. On the Insert menu, point to Name, and then click Define.
2.. In the Names in workbook box, type the name.
3.. If the Refers to box contains a reference, select the equal sign (=)
and the reference and press BACKSPACE.
4.. In the Refers to box, type = (an equal sign).
5.. Click the tab for the first worksheet to be referenced.
6.. Hold down SHIFT and click the tab for the last worksheet to be
referenced.
7.. Select the cell or range of cells to be referenced.
I've followed the directions and the range (Tag_Number) IS added to the
list
of "Names in workbook"
in the "Define Name" dialog box. In the refers to box is:
=January:December!$C:$C

The range however is not listed in the drop down box in the upper left
corner of the worksheets
and it does not work in formulas.

Anyone had any success with these types of ranges?

Any help you could pass along would be appreciated.

Thanks,

-Kevin

3-D Reference
 

"Kevin" wrote...
Well it's a start. It DOES work as you say with =SUM but so far I still
can't get it to work as part of an =IF function.

=IF(COUNTIF(Tag_Number,15),15,"No Match"
....

3D references may only be used in a limited set of functions: COUNT, COUNTA,
SUM, AVERAGE, MIN, MAX, VAR, STDEV, NPV, FREQUENCY and their variants (e.g.,
AVERAGEA, VARP, STDEVPA). They can't be used in COUNTIF or SUMIF (though
that's due to overly narrow design on Microsoft's part).

For conditional counting, you could try this hack,

=IF(INDEX(FREQUENCY(Tag_number,15-{0.000000000000001;0}),2),15,"No Match")

3-D Reference
 

Harlan Grove,

Wow! that's quite a formula. I don't know how you came up with it but I'm
glad you did.

I'm working with it now and I've reached something of a road block. The
formula
works fine with tag numbers 0 thru 16. 17 and higher return "No Match".

Can the formula be altered to handle higher numbers? Four digit numbers?

Thanks for your help, I really appreciate it.

-Kevin


"Harlan Grove" wrote in message
...
"Kevin" wrote...
Well it's a start. It DOES work as you say with =SUM but so far I still
can't get it to work as part of an =IF function.

=IF(COUNTIF(Tag_Number,15),15,"No Match"
...

3D references may only be used in a limited set of functions: COUNT,
COUNTA, SUM, AVERAGE, MIN, MAX, VAR, STDEV, NPV, FREQUENCY and their
variants (e.g., AVERAGEA, VARP, STDEVPA). They can't be used in COUNTIF or
SUMIF (though that's due to overly narrow design on Microsoft's part).

For conditional counting, you could try this hack,

=IF(INDEX(FREQUENCY(Tag_number,15-{0.000000000000001;0}),2),15,"No Match")

3-D Reference
 

RagDyeR,

I think your on to something here. These formulas, in my instance,
do seem to circumvent Excels less than stellar handling of named ranges.

=SUMPRODUCT(COUNTIF(INDIRECT(Tag_Number&"!C:C"),1 5))
Returns the number of instances that "15" appears in the "Tag_Number" range.

=SUMPRODUCT(SUMIF(INDIRECT(Tag_Number&"!C:C"),15))
Returns the SUM of the number of instances of "15"

What I am trying to accomplish is to have Excel examine "Tag_Number" for
the number 15 and if it exists in "Tag_Number" have it return the value
"15".
If it does not exist in "Tag_Number", then have it return "No Match"

I'm using 15 to keep things simple, the actual tag numbers are 9 digits.

Thanks again for your help on this.

-Kevin

"RagDyeR" wrote in message
...
XL is not too good with 3D references.

Try working with this type of procedu

Create a list of your WS names in an out-of-the-way location, say Z1 to
Z12.
Make sure that the spelling and/or spacing is *exact* to what's on the
tabs.

Select this range and click in the name box and enter a name, say
"Tag_Number", (no quotes), and hit enter.

Then, try this:

=SUMPRODUCT(COUNTIF(INDIRECT(Tag_Number&"!C:C"),15 ))

If your WS names might contain spaces, this addition of apostrophes is
necessary, and can be used with names without spaces, "just in case".

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Tag_Number&"'!C:C "),15))

Play around and see what you come up with, for example:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Tag_Number&"'!C:C") ,15))

=SUMPRODUCT(SUMIF(INDIRECT("'"&Tag_Number&"'!C:C") ,"15"))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Kevin" wrote in message
...
RagDyeR,

Well it's a start. It DOES work as you say with =SUM but so far I still
can't get it to work
as part of an =IF function.

=IF(COUNTIF(Tag_Number,15),15,"No Match"

I'll keep plugging at it.

Thanks for your help.

-Kevin

"RagDyeR" wrote in message
...
Put some numbers in Column C of your January to December sheets.

What does

=Sum(Tag_Number)

return?

Works for me!

What you have here is actually a named *formula*.

Revise your formula by clicking on "Tag_Number" in the Define Name
Window.

Then click in the "Refers To" box, and hit <F2 to enter the edit mode.

Revise the formula to this:

=SUM(Jan:Dec!C:C)

Then OK out.

Now, try this in any cell:

=Tag_Number

You should get the total of values in all your Column C's, just as you
got
with using:

=Sum(Tag_Number)
--

HTH,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Kevin" wrote in message
...
Dear NG:

Has anyone been able to get this 3-D Reference to work?

This is an excerpt from the Help file telling you what to do:

Name cells on more than one worksheet by using a 3-D reference
1.. On the Insert menu, point to Name, and then click Define.
2.. In the Names in workbook box, type the name.
3.. If the Refers to box contains a reference, select the equal sign (=)
and the reference and press BACKSPACE.
4.. In the Refers to box, type = (an equal sign).
5.. Click the tab for the first worksheet to be referenced.
6.. Hold down SHIFT and click the tab for the last worksheet to be
referenced.
7.. Select the cell or range of cells to be referenced.
I've followed the directions and the range (Tag_Number) IS added to the
list
of "Names in workbook"
in the "Define Name" dialog box. In the refers to box is:
=January:December!$C:$C

The range however is not listed in the drop down box in the upper left
corner of the worksheets
and it does not work in formulas.

Anyone had any success with these types of ranges?

Any help you could pass along would be appreciated.

Thanks,

-Kevin

3-D Reference
 

"Kevin" wrote...
....
I'm working with it now and I've reached something of a road block. The
formula works fine with tag numbers 0 thru 16. 17 and higher return "No
Match".

Can the formula be altered to handle higher numbers? Four digit numbers?
....
"Harlan Grove" wrote in message
....
=IF(INDEX(FREQUENCY(Tag_number,15-{0.000000000000001;0}),2),15,
"No Match")

If all the numbers for which you'd be searching were integers < 10^15,
change the formula to

=IF(INDEX(FREQUENCY(Tag_number,NumberSought-{0.5;0}),2),NumberSought,
"No Match")

Otherwise, you'll need to make the small number not as small, e.g.,

=IF(INDEX(FREQUENCY(Tag_number,NumberSought-{0.000001;0}),2),NumberSought,
"No Match")

3-D Reference
 

Harlan Grove,

Thanks so much for your help with this.
The formula:
=IF(INDEX(FREQUENCY(Tag_number,NumberSought-{0.5;0}),2),NumberSought,"No
Match")
Appears to be working perfectly.

"<10^15"?

I fear it's been longer than I care to think about since I aced my algebra
regents exam.

That would be 10 to the 15th power? Correct?

-Kevin

"Harlan Grove" wrote in message
...
"Kevin" wrote...
...
I'm working with it now and I've reached something of a road block. The
formula works fine with tag numbers 0 thru 16. 17 and higher return "No
Match".

Can the formula be altered to handle higher numbers? Four digit numbers?
...
"Harlan Grove" wrote in message
...
=IF(INDEX(FREQUENCY(Tag_number,15-{0.000000000000001;0}),2),15,
"No Match")

If all the numbers for which you'd be searching were integers < 10^15,
change the formula to

=IF(INDEX(FREQUENCY(Tag_number,NumberSought-{0.5;0}),2),NumberSought,
"No Match")

Otherwise, you'll need to make the small number not as small, e.g.,

=IF(INDEX(FREQUENCY(Tag_number,NumberSought-{0.000001;0}),2),NumberSought,
"No Match")

3-D Reference
 

"<10^15"?
Make that LESS THAN 10 to the 15th power. Correct?

-Kevin


"Kevin" wrote in message
...
Harlan Grove,

Thanks so much for your help with this.
The formula:
=IF(INDEX(FREQUENCY(Tag_number,NumberSought-{0.5;0}),2),NumberSought,"No
Match")
Appears to be working perfectly.

"<10^15"?

I fear it's been longer than I care to think about since I aced my algebra
regents exam.

That would be 10 to the 15th power? Correct?

-Kevin

"Harlan Grove" wrote in message
...
"Kevin" wrote...
...
I'm working with it now and I've reached something of a road block. The
formula works fine with tag numbers 0 thru 16. 17 and higher return "No
Match".

Can the formula be altered to handle higher numbers? Four digit numbers?
...
"Harlan Grove" wrote in message
...
=IF(INDEX(FREQUENCY(Tag_number,15-{0.000000000000001;0}),2),15,
"No Match")

If all the numbers for which you'd be searching were integers < 10^15,
change the formula to

=IF(INDEX(FREQUENCY(Tag_number,NumberSought-{0.5;0}),2),NumberSought,
"No Match")

Otherwise, you'll need to make the small number not as small, e.g.,

=IF(INDEX(FREQUENCY(Tag_number,NumberSought-{0.000001;0}),2),NumberSought,
"No Match")

3-D Reference
 

I figured that you could build around my examples by yourself.

Is this what you're looking for:

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&Tag_Number&"'! C:C"),15)),15,"No Match")
?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Kevin" wrote in message
...
RagDyeR,

I think your on to something here. These formulas, in my instance,
do seem to circumvent Excels less than stellar handling of named ranges.

=SUMPRODUCT(COUNTIF(INDIRECT(Tag_Number&"!C:C"),1 5))
Returns the number of instances that "15" appears in the "Tag_Number"
range.

=SUMPRODUCT(SUMIF(INDIRECT(Tag_Number&"!C:C"),15))
Returns the SUM of the number of instances of "15"

What I am trying to accomplish is to have Excel examine "Tag_Number" for
the number 15 and if it exists in "Tag_Number" have it return the value
"15".
If it does not exist in "Tag_Number", then have it return "No Match"

I'm using 15 to keep things simple, the actual tag numbers are 9 digits.

Thanks again for your help on this.

-Kevin

"RagDyeR" wrote in message
...
XL is not too good with 3D references.

Try working with this type of procedu

Create a list of your WS names in an out-of-the-way location, say Z1 to
Z12.
Make sure that the spelling and/or spacing is *exact* to what's on the
tabs.

Select this range and click in the name box and enter a name, say
"Tag_Number", (no quotes), and hit enter.

Then, try this:

=SUMPRODUCT(COUNTIF(INDIRECT(Tag_Number&"!C:C"),15 ))

If your WS names might contain spaces, this addition of apostrophes is
necessary, and can be used with names without spaces, "just in case".

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Tag_Number&"'!C:C "),15))

Play around and see what you come up with, for example:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Tag_Number&"'!C:C") ,15))

=SUMPRODUCT(SUMIF(INDIRECT("'"&Tag_Number&"'!C:C") ,"15"))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Kevin" wrote in message
...
RagDyeR,

Well it's a start. It DOES work as you say with =SUM but so far I still
can't get it to work
as part of an =IF function.

=IF(COUNTIF(Tag_Number,15),15,"No Match"

I'll keep plugging at it.

Thanks for your help.

-Kevin

"RagDyeR" wrote in message
...
Put some numbers in Column C of your January to December sheets.

What does

=Sum(Tag_Number)

return?

Works for me!

What you have here is actually a named *formula*.

Revise your formula by clicking on "Tag_Number" in the Define Name
Window.

Then click in the "Refers To" box, and hit <F2 to enter the edit mode.

Revise the formula to this:

=SUM(Jan:Dec!C:C)

Then OK out.

Now, try this in any cell:

=Tag_Number

You should get the total of values in all your Column C's, just as you
got
with using:

=Sum(Tag_Number)
--

HTH,

RD

-------------------------------------------------------------------------
----------------------
Please keep all correspondence within the Group, so all may benefit !

-------------------------------------------------------------------------
----------------------

"Kevin" wrote in message
...
Dear NG:

Has anyone been able to get this 3-D Reference to work?

This is an excerpt from the Help file telling you what to do:

Name cells on more than one worksheet by using a 3-D reference
1.. On the Insert menu, point to Name, and then click Define.
2.. In the Names in workbook box, type the name.
3.. If the Refers to box contains a reference, select the equal sign
(=)
and the reference and press BACKSPACE.
4.. In the Refers to box, type = (an equal sign).
5.. Click the tab for the first worksheet to be referenced.
6.. Hold down SHIFT and click the tab for the last worksheet to be
referenced.
7.. Select the cell or range of cells to be referenced.
I've followed the directions and the range (Tag_Number) IS added to the
list
of "Names in workbook"
in the "Define Name" dialog box. In the refers to box is:
=January:December!$C:$C

The range however is not listed in the drop down box in the upper left
corner of the worksheets
and it does not work in formulas.

Anyone had any success with these types of ranges?

Any help you could pass along would be appreciated.

Thanks,

-Kevin

3-D Reference
 

"Kevin" wrote...
"<10^15"?
Make that LESS THAN 10 to the 15th power. Correct?
....

Yup.

Online help can prove useful answering this kind of question.