3-D Reference

K

Kevin

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
 
G

Guest

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
 
R

RagDyeR

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 !
-----------------------------------------------------------------------------------------------

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
 
K

Kevin

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
 
R

RagDyeR

XL is not too good with 3D references.

Try working with this type of procedure:

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!
=====================================================


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
 
H

Harlan Grove

Kevin said:
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"
Click to expand...
....

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")
 
K

Kevin

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
 
K

Kevin

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"),15))
Click to expand...
Returns the number of instances that "15" appears in the "Tag_Number" range.
=SUMPRODUCT(SUMIF(INDIRECT(Tag_Number&"!C:C"),15))
Click to expand...
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
 
H

Harlan Grove

Kevin said:
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? ....
Click to expand...

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")
 
K

Kevin

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")
Click to expand...
Appears to be working perfectly.
"<10^15"?
Click to expand...

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
 
R

Ragdyer

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")
?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Invalid References in formula 3
HOW DO I MAKE VALIDATION LIST CONTAING DATA FROM A DIFFERENT BO 1
Reference Formula Help 1
How do i set up a 3d reference where one cell reference is dynamic 15
Excel VBA 1
Lookup data 3
How to refer to tab names? 1
3-D worksheet references? 2

Top