Welcome to MSDN Blogs Sign in | Join | Help

Referencing Tables From RefEdit Controls

SamRad is back with another guest post.  Today Sam is writing about another feature he worked on - supporting table referencing from places other than the formula bar.  Enjoy.

Dave posted about Tables and the new "structure referencing" feature in previous posts ... see here for the details.  You’ll be happy to know that the formula bar isn’t the only area that gets these new table referencing capabilities, but some special RefEdits as well.  This is a short post that will describe table referencing in RefEdits, and call out which areas of Excel got this new functionality.

Table references in RefEdits works similarly to the formula bar - as you select cells in a table, the RefEdit will put the table reference into the RefEdit, and the feature this RefEdit is hooked up to (i.e. charting) will then take advantage of the the reference to the table.  Below is a picture of the RefEdit in the Name Manager dialog:


In the name manager RefEdit, I’ve selected the Player column for Table2.  As you can see, with that selection the RefEdit creates the formula “=Table2[Player]”.  This provides all the benefits of table referencing - readibility, automatic grow and shrink, etc.

You’ll be able to see this new RefEdit functionality in the following features:

  • Page Setup
  • Pivot tables
  • Defining Names
  • Function wizard
  • Charts

You can also simply type the reference, of course, if you are not the type of user that uses the mouse a lot.

Also note that these RefEdits have had their limit bumped up.  Before they were 255 (or so) characters; now some support 2k characters and others support 8k characters, pending the technology and feature behind the RefEdit.

… and if table referencing in the RefEdits doesn’t sell you, check out our new stunning RefEdit icon:


Published Tuesday, September 05, 2006 11:02 PM by David Gainer

Comments

# re: Referencing Tables From RefEdit Controls

Wednesday, September 06, 2006 5:42 AM by Paul
A new RefEdit icon?!!?!! Now that's something I can get excited about!!!!!

# re: Referencing Tables From RefEdit Controls

Wednesday, September 06, 2006 9:21 AM by Steve
I can see plenty of applications for RefEdits in my own work.  However, one of the problems I'm having is combining tables with Custom Views. The Custom View button seems to be broken in B2 if the workbook contains tables.  Will this be fixed in B2TR?  Otherwise it makes my workbook strategy unworkable

# Stunning icon

Wednesday, September 06, 2006 1:25 PM by Bob
1. muddy - the old one is much more clear
2. sketchy - the arrow in the old one looks like arrow
3. asymmetric - the bottom border is narrower than the other 3
4. less informative - cells that reference those in the upper left corner are not visible

Why?!

# re: Referencing Tables From RefEdit Controls

Wednesday, September 06, 2006 6:36 PM by Orion Adrian
One major concern I have with the new structured table references is I haven't yet found a way to distinguish between relative references (e.g. A2) and fixed references ($A$2). Is there a way to do it? I'm using Beta 2.

# re: Referencing Tables From RefEdit Controls

Wednesday, September 06, 2006 7:30 PM by Rob van Gelder
Could you please indicate whether the new RefEdit control is supported on a Modeless userform?

# re: Referencing Tables From RefEdit Controls

Thursday, September 07, 2006 2:04 PM by Harlan Grove
Bob, clearly you're failing to appreciate the manifold advantages of the professionally designed new color scheme.

# re: Referencing Tables From RefEdit Controls

Friday, September 08, 2006 7:00 PM by Sam Rad
Steve:
Currently custom views and tables do not work together.  We hope to fix this in a future release.  I can think of no workarounds for this behavior.

Bob:
Good points.  New one matches our default theme.  The "fuzzyness" is partly due to the increased color depth which means there is AA and gradients, and in such a small image I can see what you mean.  Looking at it w/o a compressed jpg it is a bit clearer IMO.

Orion:
By and large structured refs behave exactly like absolute refs and you should treat them as such.  The only case I know of where this isn't true is when filling cells.  For example:

- Create a table in A1:D3
- In A7 enter a structured ref that points to the header in A1
- Now fill that cell to the right

Result: the structured ref adjusts like a relative ref to display the other header values.

In all other cases (copy/paste, etc.) a structured ref is no different than an absolute ref.  Which is why there isn't a "$Table"- we don't really have a concept of relative vs. absolute structured refs.

Rob:
changes haven't been made to the activex version of the control, so I'm guessing no.

# re: Referencing Tables From RefEdit Controls

Tuesday, September 19, 2006 2:18 PM by Scott
These table references don't seem to work everywhere.  If I put this into a cell, I get wrong data:

=SUMIFS(Table1[Spent],Table1[Category],"="&$AR3)

If I change the second parameter to not be a table reference, I get proper data

=SUMIFS(Table1[Spent],$O$2:$O$71,"="&$AR3)

# re: Referencing Tables From RefEdit Controls

Tuesday, September 19, 2006 2:32 PM by David Gainer
Scott, could you send me the file you are working with?  I tried to reproduce this problem in current builds, but everthing works fine.  I would like to make 100% sure, though.

# re: Referencing Tables From RefEdit Controls

Tuesday, September 19, 2006 3:07 PM by Scott
Unfortunately no, as it has quite a bit of IP in it.  In trying to break it down to something I can send I can't reproduce now.

Basically I'm seeing that sometimes it works, and sometimes it doesn't when I use column refs like this.  I haven't been able to pin down exactly why yet.  I'm using B2TR on Windows 2003.

If I can find a reproducable case I'll repost.

# re: Referencing Tables From RefEdit Controls

Tuesday, September 19, 2006 3:24 PM by Scott
OK, I was able to reproduce now.  If you give me an email I can send, but I started excel and on a sheet made a quick table:

Spent  Left
1      9
2      10
3      11
4      12
5      13
6      14
7      15

Turn that into a table via Ctrl+t.  The word 'Spent' is in A1, Left in B1

Then I went to F3 and put this formula:
=SUMIFS(Table1[Left],$C2:$C71,"=SPENT",Table1[Left],">0")

Now go back to C4 and put the word SPENT.  Excel tries to make column C part of the table, which I unded as I don't want the table to expand to column C.

F3 just says '#VALUE!'

I can put SPENT on any of the rows and nothing happens.  It's kind of like F3 just doesn't know to go recalculate the value.

Hopefully you're just going to tell me I'm doing it wrong :)

# re: Referencing Tables From RefEdit Controls

Tuesday, September 19, 2006 4:26 PM by David Gainer
Hi Scott, thanks for the info.  I didn't get any further than entering the formula in F3 before I hit #VALUE.  THe #VALUE results from the fact that the sum range and criteria range are not the same size - if you change $C2:$C71 to $C2:$C8, the problem goes away.

Here is the example I tried, in case it helps further explain anything.

A table with three columns:

Category1 Category2 Amount
One Fish 100
One Cars 110
One Fish 120
One Cars 130
Two Fish 140
Two Cars 150
Two Fish 160
Two Cars 170

To find out the sum of the numbers that are "Two" and "Fish", enter the following in any cell:

=SUMIFS(Table3[Amount], Table3[Category1], "=Two", Table3[Category2], "=Fish")

# re: Referencing Tables From RefEdit Controls

Tuesday, September 19, 2006 5:21 PM by Scott
That solves the #VALUE problem, but I'm still having issues that it doesn't update the value as things change.  I'm leaning towards it being a SUMIFS() thing more than the table references thing.  What's your email?  I'll send you the sheet that doesn't update.

# re: Referencing Tables From RefEdit Controls

Tuesday, September 19, 2006 5:53 PM by David Gainer
Scott, you can send the file to xlfiles@microsoft.com.  That will reach me directly.
New Comments to this post are disabled
 
Page view tracker