Working with Tables in Excel

Comments

All comments about this page:

Comment by: Jim Henderson (12-1-2007 07:50:26) deeplink to this comment

Excellent Post Great job of explaining and using the pictures of the ribbon

Comment by: Jan Karel Pieterse (12-1-2007 10:26:07) deeplink to this comment

Hi Jim,

Thanks!

Comment by: Alex J (12-1-2007 11:56:03) deeplink to this comment

Excellent Post. Despite never having looked into Excel 2007 before, the instructions were clear and well explained. The table functionality looks excellent, as well. Are there any VBA changes which deal with the data table?

Comment by: Jan Karel Pieterse (12-1-2007 13:30:56) deeplink to this comment

Hi Alex,

Thanks.
I plan to follow up on this post with a VBA topic on Tables. Not sure when yet though.

Comment by: Dave Johnson (12-1-2007 14:37:16) deeplink to this comment

I was not aware of this Excel 2007 feature. The automatic expansion of the table (including the copying of formulas) is alone worth the price of admission. This will prove useful in many contexts. Thanks for clearly articulating and sharing your knowledge!

Comment by: kanwaljit (18-1-2007 05:07:08) deeplink to this comment

Great...........

Comment by: Zeki Sanal (24-1-2007 05:25:15) deeplink to this comment

Thanks for your help. Information is very timely. It made a diffrence for me in my project. I hope it will make difference to other users too.

Comment by: Adnan (21-2-2007 07:24:29) deeplink to this comment

Owesome post, just learend this feature. Thank you Jan!

Comment by: Alexandre Mironiuc (2-3-2007 07:00:53) deeplink to this comment

Great post! It’s being very useful to me. I’ve got a problem and I can’t solve it. I’ve got 2 tables which are arranged in parallel. Is it possible to create a filter that works just for one table, with no side effects to the other one? Currently when one is filtered, the other one is being supressed (filtered) as well. I wish I’ve made myself clear and hope you could help me on this. Thanks.

Comment by: Jan Karel Pieterse (2-3-2007 07:33:39) deeplink to this comment

Hi Alexandre,

Unfortunately this is not possible, if you want to filter a table, anything next to the table will be affected.

Note that this has been the case for all Excel versions I know of (which goes back to Excel 5).

Comment by: Alexandre Mironiuc (2-3-2007 07:49:47) deeplink to this comment

Thanks Jan

Comment by: Andrea (14-3-2007 21:37:38) deeplink to this comment

If it wasn't for the new format features i think i would have prefered the old list: why Microsoft removed the special line there was at the bottom of list in excel 2003 to add new rows. if you copy and past data from your list that row was great and helped to make the job VERY FAST

Comment by: Jan Karel Pieterse (14-3-2007 22:09:11) deeplink to this comment

Hi Andrea,

Could you elaborate a bit more? What does Excel 2003 do that 2007 doesn't?

Comment by: Andrea (15-3-2007 16:59:57) deeplink to this comment

Try to copy three rows from your table in excel 2007 and copy them at the end of your table (with total row on). Try to do the same with your list in excel 2003 (total row on): you will see that in excel 2007 you have to overwright the total row or add new rows before, in excel 2003 everithing is smooth and automatic due to the special insert row (the one at the bottom of your list with a star)

Comment by: Jan Karel Pieterse (16-3-2007 01:03:19) deeplink to this comment

Hi Andrea,

Well, I tested this both on 2003 and 2007 and both do the same for me: The pasted rows get inserted and the total row moves down, including the new rows in it's total.
I pasted the rows on top of the total row.

Comment by: Andrea (16-3-2007 08:14:51) deeplink to this comment

i am still trying, but if you past just one row it doesn't work. it erase the total row

Comment by: Jan Karel Pieterse (17-3-2007 07:27:10) deeplink to this comment

Hi Andrea,

I see what you mean. One workaround is to NOT hit control-v to paste but to use control-shift-+, that inserts the copied cells. Or rightclick the total row and hit insert, copied cells.

Comment by: Andrea (17-3-2007 15:08:22) deeplink to this comment

A bit tricky! I tried and it works when you get the black arrow on the total. Alright. Any hints how to past a single raw when you filter the table (without removing the filter and without adding a raw)? This will save me lot of time! Hope Microsoft improves copy and past in the tables in the SR1 or at least matches excel 2003 capabilities.
Thanks Jan
Hope to see soon an article about Pivot Tables/Charts

Comment by: Jan Karel Pieterse (18-3-2007 05:37:57) deeplink to this comment

Hi Andrea,

Sorry, no idea's at the moment.

Comment by: Tom (5-4-2007 09:29:06) deeplink to this comment

>Comment by: Jan Karel Pieterse (1/12/2007 1:30:56 PM)
>I plan to follow up on this post with a VBA topic on Tables.

Still planning to do this topic? I am hoping you are, as I have been stumped on being able to create a table, filtering on a specific column for blanks and removing these selected blank rows. I can do it w/ a normal range, but can't figure out once the range is converted to a table in Excel 2007.

Comment by: Jan Karel Pieterse (6-4-2007 02:40:13) deeplink to this comment

Hi Tom,

Yes I am, but business is crazy, so don't hold your breath.

As for your filtering problem: this shouldn't be too hard. You could always NOT format the range as table, then do the filtering and deleting and after that is done convert to a table.

Record a macro whilst doing so should give you a god idea of the code.

Comment by: Tom (6-4-2007 07:23:47) deeplink to this comment

Thanks for the suggestion of NOT formatting as range as a table first, this was my original workaround. Also,tried recording a macro, still couldn't figure it out.

However, I think I figured it out. It appears that with the new tables in 2007 Range.EntireRow.Delete method fails in the range contains more than one area.
Code to explain:
Sub DeleteTableRowsWithBlanksInColumn11()
    Dim table2 As ListObject
    Dim rng2 As Range
    
    Set table2 = ActiveSheet.ListObjects("Table2")
    table2.Range.AutoFilter Field:=11, Criteria1:="="
    
    With table2.Range
        Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
        'Excel 2003 Range works
        'Excel 2007 fails with "1004 - Delete method of Range failed"
        'if rng2 countains more than one area.
        'rng2.EntireRow.Delete
        
        'workaround
        CCount = rng2.Areas.Count
        If rng2.Areas.Count = 1 Then
            rng2.EntireRow.Delete
        Else
            Dim rng3 As Range
            For Each rng3 In rng2.Areas
                rng3.EntireRow.Delete
            Next
        End If
    End With
    table2.Range.AutoFilter Field:=11
End Sub

Maybe you will have a better solution in your VBA topic, I still look forward to learning from it! If not, at least you will not waste your time investing this issue!

Comment by: Avner (9-5-2007 01:20:23) deeplink to this comment

Hi,
Nice new Excel feature, GREAT explanation.
No need for dynamic range names and freezed panes.

Comment by: Misange (5-9-2007 12:25:48) deeplink to this comment

Hi Jan Karel,
clearly explained and easy to read. Good job.
I could not manage to format my table as I would like :
In the first column item names
in the first line dates (months)
the table contents expenses that should be done on a given date.
I would like to have the first column in gey and then alternate 3 white colums with 3 blue ones. Could not figure it out but maybe this is simple not possible ?
Thanks again for the tutorial

Comment by: Jan Karel Pieterse (5-9-2007 21:20:29) deeplink to this comment

Hi Misange,

Well, thank you for the compliments.

I could not get that kind of formatting either.

Comment by: Jan Karel Pieterse (5-9-2007 21:55:32) deeplink to this comment

Hi Misange,

Had another go at it. Of course this works, as long as you remember to check the "First column" box on the Table styles tab!

Comment by: Misange (7-9-2007 04:49:17) deeplink to this comment

Hello Jan Karel,
I did select the first column box but then I have lets say 1 blue, 2 white, 3 green 3 white, 3 green 3 white colums ... and not 1 blue, 3 white (instead of 2) 3 green...

Another pb : when formatting the table, I chose first line font color=white on a dark background. In the "thumb" image (not sure this is the adequate wording !), It appears correctly, white on a dark background. However when I apply this format on unformatted data, the background is OK but the font color does not change... Any idea ?

Comment by: Peter (22-11-2007 16:33:32) deeplink to this comment

Smashing page, I'll have to get xl2007 though to use it. The comments below were also very interesting.

Comment by: Kerry (4-12-2007 00:09:37) deeplink to this comment

Please help. I am putting my own training material together and have found that once I apply Format as Table, I can no longer create Custom Views? Any reason why?

Comment by: Avner (14-2-2008 03:40:34) deeplink to this comment

Hi,
i noticed that you can copy-paste, and fill (right or left), a formula that refers to some table columns But you cannot drag it to the right(or left): the columns names orders changes in the formula. Is there a way to make the column absolute ($ ,f4 ) ?

Comment by: Jan karel Pieterse (14-2-2008 09:40:16) deeplink to this comment

Hi,

Excel Help says that if you hold the control key whilst dragging the columns should stay unchanged. Oddly enough I cannot reproduce what it says in help!

Comment by: Gabor (3-3-2008 01:48:41) deeplink to this comment

Hi, any hints about how to autoformat a table if I don't need the fancy filtering and summarizing option? My other issue is that how it is possible to use this "Format as Table" function when the number of columns in the header differs from that of the data? For example in the header I have one column for "Size" but in the table I use two columns for this as the quantity and the unit (10 ft)?

Comment by: Jan karel Pieterse (3-3-2008 03:52:44) deeplink to this comment

Hi Gabor,

I think there is little choice: with formatting as a table you get the filtering.

Indeed does a "table" need unique headings for each column, there is no way around that I'm afraid.

However, you can have just the formatting, by first converting the range to a table (which demands column headings) and subsequently reverting the table to a range.

Comment by: Gabor (3-3-2008 04:50:30) deeplink to this comment

Thanks Jan karel,
your suggestion is a good one for me. I can have the formatting but no need to deal with the filtering and other stuff. The process is to use the "Format as table" option then convert the table back to range. The filtering and the annoying columnnames are gone.

Comment by: TomasR (31-3-2008 01:30:43) deeplink to this comment

One peculiar functionality:
If you have a cell which is validated using a list one would think using a tables name (and column perhaps) might work. But no.

To make it possible to use the table you must create a common name that in turn refers to the table and then use the name as the validation list source. Simpler than per Excel 2007 but still one step to much.

Comment by: Andrew (10-11-2008 14:13:53) deeplink to this comment

Was there ever an answer about the absolute table reference? I love using tables, but I spend so much time correcting formulas because I can't make a single table reference absolute. I want some to be absolute and some relative, but I can't find a way to make it happen. Any suggestions?

Comment by: Jan Karel Pieterse (10-11-2008 22:57:15) deeplink to this comment

Hi Andrew,

I checked what happens if you drag the fill handle to the right. On a normal drag, the named columns in a formula stay put. But if you hold the control key during the drag, the column names adjust accordingly (Excel help is wrong there!!). Unfortunately this goes for all named references in the formula. If you need mixed behaviour (some stay put, others do not), you must use "old-style" referencing.

Comment by: heather (22-11-2008 20:18:28) deeplink to this comment

When I email the document containing tables to someone else, I get it back without the tables formatting, they convert to ranges. The other person uses Mac office 2008 and I use Office 2007 for windows. How can we stop losing the table functionality?

Comment by: Jan Karel Pieterse (23-11-2008 07:55:04) deeplink to this comment

Hi Heather,

If Excel 2008 (MAC) doesn't support this functionality, then loosing it during a save from MAC Excel is something you cannot avoid. The best you can do is record a macro when setting up the table in 2007 and run that macro when you get it back form your MAC user.

Comment by: Rob (4-12-2008 14:25:20) deeplink to this comment

Hi Andrew,

One way is to use the indirect Function, say we have a sumif formula and we want the first column to remain constant (absolute):

=SUMIF(INDIRECT("Table1[[#All],[Headers]]"),$H$1,Table1[[#All],[Headers2]])

Hope that helps.

Comment by: Charlie Hall (12-2-2009 15:27:29) deeplink to this comment

Hi Jan,

I have been avoiding XL07 but finally had to bite the bullet for a new client. I like tables - certainly a nice feature, much improved over lists (which I liked as well).

One question - Vlookup has always had the weakness that the column parameter is hard coded and as the table changes this parameter can easily get out of date. I have many creative solutions to this in XL03. I was hoping this might be solved elegantly with tables, and it is improved, but maybe you have a better suggestion.

I have come up with:
vlookup( lookupvalue, tablename, column(tablename[ column_name]) - column(tablename) + 1, range_lookup)

Is there a shortcut to get the column number based on the column_name - without have to adjust for the first column of the table?

Hope so - thanks in advance

Comment by: Jan Karel Pieterse (13-2-2009 03:16:45) deeplink to this comment

Hi Charles,

I would use a different approach by using a combination of index and match:

Comment by: Neuromancer (19-2-2009 04:25:56) deeplink to this comment

I don't know if anyone else has seen this issue or even if this is an issue. but I doubt it is the normal behaviour

first off I type the numbers 1-9 in The range A2:C4
Add headers Col1 col 2 col3
convert the whole range into a table

in cell d2 i construct a formula which multiplies (c2*b2)+a1
which converts it to
=Table1[[#This Row],[Col3]]*Table1[[#This Row],[Col2]]+Table1[[#This Row],[Col1]]

now if I put a fiter on col1 to show the value 4
and then copy this entire row to another sheet The formula is
or copy the row directly it still shows
=Table1[[#This Row],[Col3]]*Table1[[#This Row],[Col2]]+Table1[[#This Row],[Col1]]

even though it is outside the table it still references it and also in relation to where you paste the row,

so if you paste it into the first row of a new sheet it errors

Personally I don't think Excel should behave like this as it makes it quite annoying to have to ensure that you have to paste the data as just values

When you do this in Excel 2003 with Lists it operates in the fashion you would expect

Comment by: Jan Karel Pieterse (19-2-2009 06:21:55) deeplink to this comment

Hi Neuromancer,

I agree this can be confusing.

Excel 2007 behaves the same way as if you've used range names in your formulas instead of cell references. If you look at it that way, the behaviour is consistent with earlier Excel versions.

Comment by: Curt (24-2-2009 06:25:53) deeplink to this comment

I have a checkbook ledger application in which I use a VLOOKUP function that keys off the MAX(Date) to get the latest check balance. One problem, if I have multiple entries to my ledger on the MAX(Date) column, the VLOOKUP selects the "first" date that matches MAX(DATE) to lookup the balance. I would like the VLOOKUP in my Table to find the LAST_ MAX(DATE) and return that balance. Any ideas?
I would like to find a formula that performs that lookup without using VBA code. Thanks!

Comment by: Luke Brunning (4-3-2009 07:20:39) deeplink to this comment

I've created a new table style in a spreadsheet, is there any way to share this in all my spreadsheets?

Comment by: Jan Karel Pieterse (4-3-2009 08:28:13) deeplink to this comment

Hi Luke,

The simplest way to have it available in all new files is to create a global template:

- Copy the table to a new empty workbook.
- Remove the table entirely
- Make sure the new file is completely empty and contains the number of worksheets you want
- Do a save-as, filetype Excel template (xltx) and save to this location:
C:\Documents and Settings\UserName\Application Data\Microsoft\Excel\XLSTART

Important! use this filename: book.xltx

To get it in an existing file, copy (part of) the table to the new file.

Comment by: Luke Brunning (4-3-2009 10:32:58) deeplink to this comment

Thanks Jan, thats very helpful; especially the tip about getting it into existing files.

Comment by: John Kaye (17-3-2009 04:16:27) deeplink to this comment

Does anyone know how I add another column to a table?. If I can't do this, how do I remove the table function in order to be able to add another column?

Comment by: Jan Karel Pieterse (17-3-2009 07:17:37) deeplink to this comment

Hi John,

You simply type the heading of the new column next to the last column of your table. Excel should expand your table automatically.

Comment by: Drew (24-3-2009 06:49:16) deeplink to this comment

My total row does not sum the numbers it just says 0, I have selected sum from the drop down but it does not recognize the numbers or something. Any thoughts?

Comment by: Jan Karel Pieterse (24-3-2009 10:53:11) deeplink to this comment

Hi Drew,

Excel thinks these are text, not numbers.
Copy an empty cell
Select numbers, select (From hoome tab) paste, paste special, se;ect the Add option.

Comment by: Barbara Bianconi (1-4-2009 20:35:51) deeplink to this comment

I am attempting to create a custom style using Modify Custom Quick Style. I selected the range A1:A8 and clicked the Format as Table button. I clicked Table Style Medium 2 and when the Format As Table dialog box came up I checked “My table has headers” to select it. I clicked OK. Now for the problem: I want to customize my quick style. I would like a bold font style with a black font. I select A2 to activate the table. I click the Format as Table button and right-click Table Style Medium 2 to display the short-cut menu. I click Duplicate to display the Modify Table Quick Style dialog box. I type TableStyleMedium2 – Custom. I select Whole Table then click Format. I change the font to Bold and color to black. I click OK to close the Format Cells dialog box and Click OK to close the Modify Table Quick Style dialog box. I then enter text and it is not bold! Under the Element Formatting in the Modify Table Quick Style dialog box it shows as bold—but it is not! Help! I have also tried checking “Set as default table quick style for this document” but nothing seems to work to bold using the Modify Custom Quick Style. I know I can highlight my table and click bold, but I would like to use the Modify Custom Quick Style. Can you help?

Comment by: Jan Karel Pieterse (3-4-2009 07:27:53) deeplink to this comment

Hi Barbara,

The point is that after creating the new table style, you have to apply the new style to the table separately, using the quick table styles gallery.

Comment by: Barbara Bianconi (6-4-2009 11:41:12) deeplink to this comment

Hi Jan,
Thank you for your reply, I realized after writing the above that the step I was missing was to "Apply (and Maintain Formatting)." This is a terrific site and I'm going to tell my students about it.

Comment by: John (21-4-2009 04:47:01) deeplink to this comment

Hi Jan,

I've got the following question regarding pivottables in MS Excel 2007. I'm trying for hours to find out how I can use a custom defined pivottable format in other documents and other pivottables. According to some other sites it should be possible in the following way:

<Choosing a Default Style for Future Pivot Tables
You can control which style is the default style to use for all future pivot tables on the computer. The default can either be one of the built-in styles or a new custom style that you modified.
In the PivotTable Styles gallery on the Design ribbon, right-click the style and choose Set as Default.>

I tried these steps, but it doesn't work on my laptop. Could there be an issue regarding the fact that I still have got MS Excel 2003 on my laptop or is this a known bug?

Thank you already in advance,
John

Comment by: Ramu (26-5-2009 23:23:03) deeplink to this comment

In Ms excel 2007 i try to create a pivot table. But I found that it does not display the field list. When I select the show/hidden option form the right click and select from the menu it does not show any result. Can anybody help in this regard.

Thanks in Advance


Ramu.A
ramu2k06@yahoo.co.in

Comment by: gabriela (19-7-2009 21:16:01) deeplink to this comment

hey how do i make a graph from a table?

Comment by: Jan Karel Pieterse (19-7-2009 21:21:58) deeplink to this comment

Hi Gabriela,

Just like any other set of cells, you select the columns you want shown on your chart and you go to the insert tab to pick the chart you need from the Charts group.

Comment by: gabrela (19-7-2009 22:55:25) deeplink to this comment

ive tried that but the graph wont show the info i need it to show. it says some random numbers on the side of the graph that go from 0-450 when they should say temperatures from 40-90. how do i change these numbers

Comment by: Jan Karel Pieterse (20-7-2009 00:28:32) deeplink to this comment

Hi Gabriela,

Do you have the right chart type selected? I expect you need an XY-scatter chart.
Also, are your x values recognised by Excel as being numbers, not text?

Select an empty cell and try this formula:
=ISNUMBER(A2)
Where A2 is one of the cells with the x values
The formula should yield TRUE.

Comment by: Dan Ling (30-7-2009 12:13:08) deeplink to this comment

I have a Excel 2007 worksheet named "Tables" with many tables defined and I would like to reference data in some of these tables in other worksheets and formulas. A One-Row table is named "tlCostTypes" and I want a formula in another worksheet to reference the first row of this table by table name and colum name -- how?

I've see some VBA references but how can I do this in a worksheet formula? Formulas are easy, Excel adds the reference automatically in most cases, when you want a whole row or column but how would I find a specific cell in the table usign a formula and not VBA? What about indexing through the Table usign Row,Col numbers or references.

Comment by: Jan Karel Pieterse (31-7-2009 06:11:42) deeplink to this comment

Hi Dan,

If your table contains a single row, then it is simply =TableName[ColumnName]

Look in Excel help for the subject called "Structured references".

Comment by: Abd. Ghani Hashim (7-8-2009 01:58:22) deeplink to this comment

Hi..

I put some cells protection in excel 2007 table, but then i found the table is converted to the normal table automatically. How do i solve this problem? tq

Comment by: clive (8-8-2009 03:10:58) deeplink to this comment

i am a quantity surveyor and i am trying to learn excel 2007
i can only use the very basic elements of it. i have the in depth excel 2007 by bill jelen but i find it difficult to follow his text.I need a book that will suite me and what i want to do. i need to make expanding tables line graphs sort data by value and make a budget table so when i make a claim for my client the value of the claim is deducted away from the budget total.what book would you suggest i get.i live in ireland.

Comment by: Michael (11-8-2009 07:55:02) deeplink to this comment

Hi Guys,
how do i get the 'Column 1' heading that Excel inserts with the drop down menu to disappear? All I want is a quick way to jazz up the table.

Comment by: Melissa Fisher (26-8-2009 09:41:32) deeplink to this comment

I have my data source in a separate Excel workbook and my pivot tables that feed into a dashboard in another workbook. This is designed so that the file size is reduced and I can share the dashboard without the datasource attached. What do I have to do, so that others can just "refresh" the dashboard when I update the datasource. Is it just a matter of storing both the datasource and dashboard in the same network folder? I have tried to have others access the dashboard and refresh it, but they have not been successful. There has to be a way, I just don't know how. Help please?

Comment by: Jan Karel Pieterse (7-9-2009 09:23:32) deeplink to this comment

Dear mr Hashim,

I just tried to reproduce your problem, but my Excel table does not get converted when I protect the sheet.
Do you have any VBA code that may cause this to happen?

Comment by: Jan Karel Pieterse (7-9-2009 09:26:55) deeplink to this comment

Hi Clive,

I'd suggest any book by John Walkenbach. The Microsoft step-by-step series are also quite good for an Excel beginner.

Comment by: Jan Karel Pieterse (7-9-2009 09:34:58) deeplink to this comment

Hi Micheal,

You can tell Excel to omit the table headings in the table tools tab of the ribbon. In the Table style options, remove the checkbox called "Header row"

Comment by: Venus (8-9-2009 13:49:32) deeplink to this comment

Please tell me I'm doing something wrong. If I have a table with 20-columns and I apply the Table Row setting, do I have to go to each and every column to set it to sum? Copy/paste doesn't work since it copies/pastes the original heading.
Thank you, Venus

Comment by: Jan Karel Pieterse (9-9-2009 00:30:51) deeplink to this comment

Hi Venus,

Not sure what you need, do you want the total beneath the table? That is one of the options on the Table Style Options group on the ribbon (called "Total Row").

Comment by: Kjones1997 (23-9-2009 19:46:43) deeplink to this comment

I have a large amount of data in a table and am trying to optimize calculations. When multiplying two columns, is there a difference in saying =[Column A]*[Column B]
vs an explicit structured reference?
=Table1[[#This Row],[ColumnA]]*Table1[[#This Row],[ColumnB]]
Is one faster caclulating?

Comment by: Jan Karel Pieterse (24-9-2009 04:11:12) deeplink to this comment

Hi Kjones1997,

I would not expect a difference, best way of course is to create a test case and time the calculation.

Comment by: kjones1997 (25-9-2009 12:07:37) deeplink to this comment

You are correct. No difference in time of calculation by using #this row.

BTW, I have never used these before now, but here are some great VBA for testing timing of calculations.

http://msdn.microsoft.com/en-us/library/aa730921.aspx

Comment by: Emily (7-10-2009 13:47:22) deeplink to this comment

I've formatted my data as a table, and now I cannot insert new rows or columns. Is there a way around this?

Comment by: Jan Karel Pieterse (7-10-2009 21:22:56) deeplink to this comment

Hi Emily,

You should be able to however. Is there anything next to or underneath the table that might prevent this? Is the worksheet protected?

Comment by: DJ Burgh (11-10-2009 10:55:35) deeplink to this comment

how do I save a table? I have taken my data, created a table and then tried to 'save as' but when I reopen it, it is back in the original worksheet format. What am I missing?

Comment by: Jan Karel Pieterse (12-10-2009 00:10:05) deeplink to this comment

Hi DJ,

Just save the file in any of the Excel 2007 file formats, NOT in Excel 97-2003 file format (those versions do not recognize the new table styles, which is why that gets zapped on save).

Comment by: Hickymanz (12-11-2009 11:00:43) deeplink to this comment

now for something Really useful

How can you write a formula using SUMPRODUCT that references data in the table

This is driving me up the wall,

Comment by: Jan Karel Pieterse (13-11-2009 10:42:49) deeplink to this comment

Hi Hickymanz,

Can you post an example of what you tried?

Comment by: Hickymanz (13-11-2009 11:39:04) deeplink to this comment

I obviously haven't explored the functions of 2007 fully
SUMIFS provide exactly what I need

....
I was trying to do SUMPRODUCT((data[country]="Germany")*data[USD]*(data[Qualified]="Half"))

This does not work in Excel 2007 with Tables
you need to use SUm IFs

Comment by: Misange (28-11-2009 08:29:26) deeplink to this comment

I was asked how we could combine tables with DcountA and other old database formulas. It would allow to have dynamic databases.
DcountA(mytable,field,criteria) cannot work because mytable does not include the reference to the header row.
Any idea to change that Jan Karel ?
I've been reading your pages on tables several times before writing mine for excelabo.net (in french). Thanks a lot for these excellent pages.
Misange

Comment by: Misange (28-11-2009 08:59:18) deeplink to this comment

I answer to my own question!
If you define a name as
mynewtable=offset(mytable[[#headers],[firstcol]],,,countA(mytable[firstcol])+1,countA(mytable[#headers]))

you can use mynewtable in DcountA and use the header of the column as field.

Comment by: Jan Karel Pieterse (28-11-2009 11:05:42) deeplink to this comment

Hi Misange,

Simpler would have been to use:

=Data[#All]

to refer to the entire table with headings.

Comment by: Misange (28-11-2009 11:13:40) deeplink to this comment

!
in french we say "I tried to find noon at 2PM". So simple. Thanks Jan Karel :-)

Comment by: Bob (17-12-2009 04:56:18) deeplink to this comment

Once I found it, I was able to use the consolidated pivot table wizard to create a consolidated pivot table from 2 tables.

But the table headings are not available in the consolidated pivot, so is there a way to fix it? (I tried using including the worksheet name! and the table name and that didn't work.)

Comment by: Jan Karel Pieterse (17-12-2009 12:14:05) deeplink to this comment

Hi Bob,

I honestly have no idea! Try asking on the Microsoft newsgroups, there is bound to be someone there who knows.

Comment by: Shaikh Sajid (27-12-2009 04:09:30) deeplink to this comment

MY REQUEST IS IF ANY CELL CONTENT FIRST LETTER IN CAP'S OTHER LATTER IN SMALL SO HOW TO CONVERT ALL IN SMALL OR IN CAP'S FOR Eg: IN CELL WRITTEN America?

Comment by: Jan Karel Pieterse (27-12-2009 06:43:28) deeplink to this comment

Hi Shaikh,

You can use the LOWER worksheet function for this.

Comment by: RD (28-12-2009 13:57:22) deeplink to this comment

Hi,
I was wondering if you knew how to auto-resize a table?
I have an Excel sheet with a table (ListObject control)
populated from SQL. If I copy several rows and paste to a new row directly below the last table row, the table will not expand to include the copied rows.
Is there an auto-resize method?

Comment by: Jan Karel Pieterse (28-12-2009 22:12:20) deeplink to this comment

Hi RD,

I expect this is because the table is a query table, normally it would expand automatically. Having the query table expand is (IMO) counter intuitive, since the data now is from mixed sources.

Comment by: Minouche (5-1-2010 20:36:54) deeplink to this comment

Hi, i need to know how to remove the table, i can apply the table but cant remove it without removing all the data. please help

Comment by: Jan Karel Pieterse (5-1-2010 23:02:03) deeplink to this comment

Hi Minouche,

Look for the section called "Table Options on the Ribbon"on this page. You need the button called "Convert table to range"

Comment by: YM_Saru (29-1-2010 01:47:50) deeplink to this comment

Hi,

I am trying to resize the tables in a worksheet(excel 2007) from Access vba. It was running successfully at first time. the problem is: if i rerun the code at the same time, getting error 1004 "Method range failed": following is the Code:

Comment by: Jan Karel Pieterse (29-1-2010 05:44:03) deeplink to this comment

Hi YM_Saru,

On which line does the code fail?
On first glance, don't you have to give the Resize command the full (new) range of the listobject, rather than the new part?

Comment by: YM_Saru (29-1-2010 10:00:04) deeplink to this comment

Thanks for the reply!

The error shows on "Range(SZrng1 & ":" & SZrng2)" selecting the range for resizing. it worked out at firstrun. Its not accepting the range selection next rerun process. i tried to make all the varibles declaration as new when it starts the process.

i couldnt really understand the "new part"

Thanks!!!

Comment by: Jan Karel Pieterse (29-1-2010 11:21:18) deeplink to this comment

Hi YM_Saru,

With the new part I mean:
On the second run of your code it appears that you are extending the range of the table, starting from the "cnt" row. You are setting the range to resize starting from that row. I think you need to set the resize range starting from the first row of the table, down to and including the last (new) row of the table.

Comment by: YM_Saru (1-2-2010 06:49:18) deeplink to this comment

I was doing from the first row of the table. I display the entire program. Probably you get an idea. the comment lines are just for random trials (this is only rough example, need to recode, once it works):

Comment by: Jan Karel Pieterse (1-2-2010 08:37:59) deeplink to this comment

Hi YM_Saru

Maybe we can solve this if you send me your file?

Comment by: Marvin (1-2-2010 10:53:52) deeplink to this comment

I'm trying to decide if I need to change my spreadsheet into a table or not. Perhaps there is a formula that could do the same. My spreadsheet has 30 columns and about 45 rows of data. Is where a formula that will search a selected area for data in column A, go across to column H to retrieve data in the cell that intersect the two to be enter on a difference worksheet? Like on a x and y axis. Example the search start in column A it locate the first data on row 16 then finds column H and go down to row 16 and retrieves that cell data. If not can this be done on a table worksheet?

Comment by: Jan Karel Pieterse (2-2-2010 05:29:17) deeplink to this comment

Hi Marvin,

Check out Help for the VLOOKUP function.

Comment by: Max (2-2-2010 16:45:23) deeplink to this comment

Hi,

Thank you for the very useful summary! I would have a question regarding the Export Data function.

I have created a Table and a List (EXCEL 07 and 03, respectively) and exported them both to SharePoint 2007 (SP) via a macro.

The one exported from 2003 works perfectly, any changes made in the EXCEL list are reflected on SP and vice versa.

Unfortunately the table exported to the same SP site works only one way and I loose the ability to make changes to EXCEL. Or at least those are not refeleceted on SP anymore.

The code I used to export the data to SP in both cases was this:

Comment by: Jan Karel Pieterse (3-2-2010 00:10:48) deeplink to this comment

Hi Max,

I'm sorry to say I have no Sharepoint knowledge to help you resolve this issue. what happens if you save the offending workbook in 97/2003 file format?

Comment by: Max (3-2-2010 06:59:08) deeplink to this comment

Hi,

Unfortunately does not help. WHat I did notice (to my surprise) is that XLS03 and XLS07 seem to connecting to SP differently. I exported the connection details and opened them up in notepad.

The connection details in XLS03, which seem to be working perfectly has this line in the odc file
"<odc:ConnectionString>Provider=Microsoft.Office.List.OLEDB.1.0;Data Source=&quot;&quot;;ApplicationName=Excel</odc:ConnectionString>"

The same line in the XLS07 connection, which does not work has this in it:
"<odc:ConnectionString>Provider=Microsoft.Office.List.OLEDB.2.0;Data Source=&quot;&quot;;ApplicationName=Excel;Version=12.0.0.0</odc:ConnectionString>"

So probably the solution would be to force XLS07 to use OLEDB1.0, but I'm not sure how to do that.

Thank you

Max

Comment by: Jan Karel Pieterse (3-2-2010 07:06:20) deeplink to this comment

Well, you might try to change the connection string on a copy of your file like this:

- Select a cell in the list
- Alt-F11 to the VBE
- control-g to open the immediate pane
- Type:

Activecell.ListObject.Connection="PasteYourConnectionStringHere"

If the connection string has any " in it, make sure you replace them with two ", like this: ""

- Then with the cusor on that line hit enter.

Comment by: Max (3-2-2010 07:26:17) deeplink to this comment

I will try it. Does the ListObject have the Connection property?
The only reason I'm asking because in the help (which is far worse than the help in XLS03, might I add) it only lists PivotTables and … well, I can't remember what's the other, but not the ListObject. Also apparently OLEDB has a Connection property, which could be set, but I'm not sure it will let me set it to 1.0. Maybe XLS07 only comes with the 2.0 version.
I'll let you know what will be the result.

Thank you!

Max

Comment by: Jan Karel Pieterse (3-2-2010 08:44:57) deeplink to this comment

Hi Max,

Sorry, I did not check whether a table object has a connection. Probably not.

See if there is a querytable tied to the table:
type this in the immediate window and hit enter:

?Activecell.ListObject.Querytable.Connection

Comment by: Max (3-2-2010 13:19:48) deeplink to this comment

No luck, I get a Run-time error 1004. Let me see if I get anywhere with the OLEDB property.

Comment by: Max (3-2-2010 13:39:01) deeplink to this comment

No luck either. I just can't figure out how to change the connection to use 1.0 instead of the 2.0. I also tried searching for it in the VBA References, but did not get anywhere. It seems that I will just have to switch back to XLS03 for any XLS to SP work.

Thx for the help!

Max

Comment by: Mary (10-2-2010 20:58:53) deeplink to this comment

I accidentally formatted the wrong cells for my table. I am now trying to remove the formatting so that I can format the proper cells, but I can't get rid of the old table formatting. I have tried delete, cut, clear, and even formatting over the table, to no avail. I have already saved it with the incorrect formatting, so I can't go back either. What do I do? Thanks so much - Mary

Comment by: Jan Karel Pieterse (11-2-2010 05:39:25) deeplink to this comment

Hi Mary,

Select the part of your table with the formatting you want to loose.
Then on the Home tab, find the "Editing" group. It has a "Clear" button, click it's arrow and select "Formatting".
Next, select anywhere in your table and use the "Table Tools" tab of the ribbon to change the appearance of your table.

Comment by: Kailash.5270@gmail.com (20-2-2010 01:18:02) deeplink to this comment

Hi Tom,

Yes I am, but business is crazy, so don't hold your breath.

As for your filtering problem: this shouldn't be too hard. You could always NOT format the range as table, then do the filtering and deleting and after that is done convert to a table.

Record a macro whilst doing so should give you a god idea of the code.

Comment by: John K (24-2-2010 10:25:39) deeplink to this comment

I can't figure out how to remove the sort down arrows from the header columns. Is there a way to remove these and still have a header row. Seems odd if you can't do this.

Comment by: Jan Karel Pieterse (25-2-2010 02:11:27) deeplink to this comment

Hi John,

Select any cell in your table, click on the Data tab and in the "Sort & Filter" group, click on the Filter button.

Comment by: Frank (1-3-2010 23:50:27) deeplink to this comment

Hello

I'm using Excel 2007, Please assist to know how I can return a value exactly comparing two colunms pick the value on the same array and return it on different case. I know vlookup only return the value by checking on only one colunm of the table array

Regards

Comment by: Jan Karel Pieterse (2-3-2010 02:13:25) deeplink to this comment

You can use a matrix function to do this (enter the function using control+shift+enter):

Comment by: Raj (8-3-2010 20:51:23) deeplink to this comment

I want to lock some columns in a table while leaving others unlocked for data entry. When I protect the sheet with some columns locked and others unlocked, the table behaviour, viz automatically carrying over formulas to the new row is no longer possible. I love this automated carry over of formulas. Am I missing something or is there a workaround for this? (I am currently using the offset formula to define the dynamic range which I need not do in case I have a Table).

Thanks in advance for the help.

Regards,
Raj

Comment by: Jan Karel Pieterse (9-3-2010 03:51:08) deeplink to this comment

Hi Rai,

You are correct, if part of the table is protected, not all functionality works.

I think al you can do is use VBA to do the inserting/removing of rows after changing the protection of the sheet using the UserInterfaceOnly argument.

Comment by: Jeff (6-4-2010 09:19:31) deeplink to this comment

I like using the tables feature primarily for it's quick formatting capabilities. However, there are many times when the table functionality of auto-filling a formula change in a particular cell to the entire column gets very annoying. I.e.- I JUST want to change the formula in C5, but after I do, the table changes all cells in column C to the same formula, even though I want to leave those unchanged. Hitting Undo once will take care of the issue, but is there a setting to have this turned off completely (either for a particular table, or as an Excel-wide option)?

Thanks!

Comment by: Jan Karel Pieterse (6-4-2010 21:23:49) deeplink to this comment

Hi Jeff,

Unfortunately, I could not find a way to prevent this from happening, apart from keeping an extra empty column between the entered formula and the table.

Comment by: NTOLO (15-4-2010 03:59:54) deeplink to this comment

I am using Excel 2007 so i want to add multiple sheets on one work book.

Comment by: Jan Karel Pieterse (16-4-2010 03:03:21) deeplink to this comment

Hi NTOLO,

I'm not sure I understand your question? You can repeatedly insert a worksheet (rightclick on a sheet tab and select Insert), is that not what you need?

Comment by: Jeff Weir (21-4-2010 14:57:07) deeplink to this comment

Hi Jan. I didn't have any luck when trying to reference a table on another sheet your way. Must be missing something.

However, I found that I could do this if I selected just one cell on my destination sheet, typing = in the formula bar, then selecting the part of the table on the source sheet(i.e. headers, data, or the entire table) that I wanted to suck through to the destination, then pushing Enter.

Note that this returns a #VALUE! error if you are selecting more than one cell, but that's fixed when you copy the formula generated into a range of cells the same size as the original table, then array entering the formula (i.e. selecting all the cells where you want the table to be copied to on your destination sheet, selecting the formula bar, and pressing Ctrl+Shift+Enter).

Note that if you expect your source table to expand, you'll need to select extra rows or columns in your destination sheet when entering this formula.

Comment by: Jan Karel Pieterse (22-4-2010 00:01:34) deeplink to this comment

Hi Jeff,

Thanks for the suggestion!
Not sure why you'd want to duplicate the entire table though?

Comment by: Jeff Weir (22-4-2010 00:52:11) deeplink to this comment

Good question, Jan. Currently I have some graphs that reference another workbook using some dynamic ranges. But these only work when the source workbook is open. So I was considering duplicating the entire table from the source workbook to my destination workbook, and then using the dynamic ranges on the table copy instead. I could also use linked cells instead of referencing a table.

However, today I decided on a different approach...I'm going to suck data from the entire closed source sheet to the destination sheet via some SQL in MS Query, using a SELECT * query. That way, no matter what way the table expands (i.e. new rows or new columns) the query will capture the changes, without me having to guess how big the source table might grow over time.

On this note, perhaps you might be able to help me. Unfortunately in this source spreadsheet the data is arranged by date columns. When new data gets pasted into the spreadsheet, someone adds a new date at the top right of the existing data array and then pastes the correstponding data below. So it's structured like this:

Category 1/6/2009 1/9/2009 1/12/2009
Planes    112     123     52
Trains    52     53     123

That is, it doesn't have a 'date' column header but rather the columns across the top of the spreadsheet are quarters.

If I want to select the categories for a couple of these date columns, then I can use something like:

SELECT Category, `39965`, `40057`, `40148` --note that these numbers are dates
FROM `Worksheet_Name$`

...but what I'd like to be able to do is dynamically select just the most recent column that I want, and I don't want to have to manually update my MS query SQL to pull the latest x columns. (Say the last two for the above example.)

Do you happen to know how I code in a check on column headers so that it only pulls the ones with the largest dates?

Regards

Jeff

Comment by: Jim (22-4-2010 06:31:08) deeplink to this comment

Hi Jan, I want to print multiple tables in excel 2007 with no page breaks after each table and have the appropriate header row repeat at the top of each new page. For example, if table one is 1.5 pages long, the header row for table 1 will be printed at the top of page 1 and 2. If table 2 then starts on page 2 and continues on page 3, the header for table 2 will be displayed at the start of table 2 in the middle of page 2 and on the top of page 3.
Thanks for any suggestions

Comment by: Jan Karel Pieterse (22-4-2010 08:06:08) deeplink to this comment

Hi Jeff,

You'd use:

Comment by: Jan Karel Pieterse (22-4-2010 08:10:15) deeplink to this comment

Hi Jim,

You could use something like this:

Comment by: Jim (22-4-2010 08:24:30) deeplink to this comment

Thanks Jan! I will give it a try.

Comment by: Ketty Israel (12-5-2010 21:06:05) deeplink to this comment

This article is exactly what I needed when I needed it. My search showed your link as the 5th option, but you were the only one of the 5 to answer the question that I had.

Incidently, the question was to find a way to place column totals for tables.

Ketty

Comment by: Phil (17-5-2010 16:48:18) deeplink to this comment

Hi Jan,

I've been using tables for a while (and Lists prevously) with great success, however, I seem to be getting eratic formula updating when I make a copy of a table in another worksheet, that is, highlight the entire table, copy, and then Paste into a new worksheeet. In some cases the formulae update to refer to the new table name, and in somecases they remain referring to the old table name. I've even had a mix, with some references updated to the copy and some remaining with the original. Can you provide any guidance on how table referencing should work when a table is copied?

Comment by: Jan Karel Pieterse (18-5-2010 07:26:46) deeplink to this comment

Hi Phil,

I have not heard of this issue before. If you ask me, copying a table should never change formulas that point to it. Period.

It is a different matter if you do a cut and paste, then I do expect all formulas to point to the new location.

Comment by: Tony Bolton (27-5-2010 08:16:19) deeplink to this comment

Once set how do you remove the table format.
The bottom row in my table won't sort alphabetically so I thought if I remove the format then i could the use a sort.

Comment by: Jan Karel Pieterse (28-5-2010 01:01:30) deeplink to this comment

Hi Tony,

On the table tools tab of the ribbon there is a button "Convert to range" that does that.

Comment by: karen (4-6-2010 15:00:21) deeplink to this comment

I want to add a row into a table but 'insert cells' is grayed out.

Comment by: Jan Karel Pieterse (6-6-2010 23:11:29) deeplink to this comment

Hi Karen,

Maybe the worksheet is protected?

Comment by: Michael Hubbard (23-6-2010 17:17:52) deeplink to this comment

So I inherited an Excel table. It is actually very good and I like the way it works.

My question is that a couple of the columns are effectively comments or details (i.e. sentences) pertaining to each row and when these are included in the table filtering possibilities the whole Excel file becomes VERY LARGE. Is there anyway to remove these columns from the table filtering and yet they would obviously stay as part of the row when other filtering is applied?

Thanks for any assistance.

Comment by: Jan Karel Pieterse (24-6-2010 02:29:10) deeplink to this comment

Hi Michael,

I am quite surprised that it would make a difference on file size whether the texts are within the table or not.

But you can put the texts outside the table, because Excel hides entire rows when you use the filter anyway.

Comment by: lmshow (21-7-2010 10:52:05) deeplink to this comment

can you please help me with this;

What is the essence of having this special bracket "{" starting and ending an excel formular
e.g.

{=IF($B$2="All",LARGE(Table6[Column47],$J5),LARGE(IF(Table6[Column110]=$B$2,Table6[Column47],FALSE),$J5))}

I'm trying to create such formular and if i manually type in the special bracket "{" excel doesn't recognise it as a formular.

Comment by: Gerson (22-7-2010 11:39:55) deeplink to this comment

Hi Jan,

Very Good write-up. I'm hoping you can help me resolve an issue I'm having with relative table references.

I have two table in two different workbooks. One is an Employee List and the other is Employee Sales Activity. I'm using a vlookup function in the sales activity sheet to look up which employee it correlates to by ID and when I have both sheets open, the function works great. But as soon as I close the Employee List all of the values revert to #REF!.

I can get the values to work if I used defined ranges (when the sheet is closed) like A1:G1000, but is there a way I can make this work using table references instead and not lose the value when I close the sheet?

Thank you

Comment by: Mark (15-8-2010 18:34:55) deeplink to this comment

Is it possible to reference a value in the following way

=Table1[[#ThisRow],[HeaderName]]

Where "HeaderName" is a Name in Excel that holds the same text string as one of the headers in Table 1?

Thanks

Mark

Comment by: Jan Karel Pieterse (16-8-2010 02:57:58) deeplink to this comment

Hi Imshow,

The curly braces indicate that you're dealing with an array formula. The curly braces are added by Excel when you edit the formula and confirm your edit by hitting control+shift+enter instead of just enter.

Comment by: Jan Karel Pieterse (16-8-2010 03:06:04) deeplink to this comment

Hi Gerson,

I'm afraid structured refs to external tables do not work when the external workbook is closed. As you've already found out.

Comment by: Jan Karel Pieterse (16-8-2010 04:44:48) deeplink to this comment

Hi Mark,

If the column name is in a cell (say K3), then this formula does that:

=INDIRECT("Table1[[#ThisRow],[" &K3&"]]")

Comment by: graham jones (21-9-2010 19:48:12) deeplink to this comment

I have a user opening a excel 2007 .xlsm workbook that contains an embedded query table. This table has Autofilter on for a date column. The Autofilter drop down does not show any date grouping by Year. This only affects the one user; all other users opening the same sheet see the dates grouped by year.
Excel Options\Advanced\Display Options for this workbook\Group Dates in the Autofilter Menu is Checked.
I can't find out how a User level property can override this setting.
Our user is opening the workbook from 2007; they are not opening a 2007 workbook from 2003 with compatibility pack.

Comment by: Jan Karel Pieterse (21-9-2010 23:43:32) deeplink to this comment

Hi Graham,

I have no idea why that might be the case. Try if toggling that setting off and back on helps (in-between, make sure to close and re-open Excel).

Comment by: graham jones (26-9-2010 15:42:29) deeplink to this comment

re - my autofilter date grouping post - your advice to toggle the setting on and off worked - thank you - much appreciated.

Comment by: Jan Karel Pieterse (26-9-2010 22:58:55) deeplink to this comment

Hi Graham,

Thanks for letting us know!

Comment by: Rich (4-10-2010 13:28:34) deeplink to this comment

Referring to a table from another workbook

You mention the following in your article:

"Normally you would refer to a range name "TableName" in workbook "WorkbookName.xls" using: [WorkbookName.xls]!TableName
But although a table is represented by a range name, you should not use the range name syntax as the source. Rather you must use this:

WorkbookName!TableName

My question is if I have a table created by the name of Test and it is created a workbook called Sales and I want to create pivot table in a new workbook using the table "Test", what is my syntax when I select Insert Pivot Table? Wouln't I need to specify the drive mapping and folder it is in (e.g. C:\desktop\sales.xlsx!test ? Does anything have to be in brackets?

Thank you.

Rich

Comment by: Jan Karel Pieterse (5-10-2010 00:52:02) deeplink to this comment

Hi Rich,

You can only create the Pivot table with the source workbook open. In that case, Excel will add any path information behind the scenes. As soon as you close the source workbook, the full path will be in the Pivot table's source.

Comment by: sreevani (13-10-2010 06:12:45) deeplink to this comment

Useful

Comment by: Leif Holstad (10-11-2010 22:25:48) deeplink to this comment

Hello,

I have created a table of some 3000 rows containing a volume-column. After selecting the wanted rows, I want a accumulated sum of the volumes shown. This does not work with normal accumulation because the formula is refering to rows which are hidden.

Could this accumulation in a selection be done?
In advance thank you very much!

Kind regards,
Leif HOlstad

Comment by: Jan Karel Pieterse (10-11-2010 22:46:07) deeplink to this comment

Hi Leif,

Check out the SUBTOTAL funtion, it ignores filtered rows.

Comment by: Diane Woods (16-11-2010 16:47:56) deeplink to this comment

Hi,

I have created tables as you have decribed in the Excel 2007 article. The tables are not refreshing when I update the data source. I have located the refresh button on the toolbar but nothing happens.

Thanks,
Diane

Comment by: Jan Karel Pieterse (17-11-2010 03:05:06) deeplink to this comment

Hi Diane,

I don't think I understand your problem. If a range is based on an external data source, it normally already is set to be a table (Excel does that automatically during the definition of the query). Looks like the external data connection is lost?

Comment by: Diane Woods (17-11-2010 21:26:38) deeplink to this comment

Jan,

The data source is in the same workbook. Could I have lost the connection with that. (I am new to this)

Diane

Comment by: Jan Karel Pieterse (18-11-2010 03:04:33) deeplink to this comment

Hi Diane,

How did you "reference" to the "data source", did you use formulas?

Comment by: Diane Woods (19-11-2010 16:20:07) deeplink to this comment

Jan,

I followed the steps to create a table format. As in figure 4, I indicated where the data is for my table.
When I add data to that data source, my table does not update.


Diane

Comment by: Jan Karel Pieterse (21-11-2010 22:22:44) deeplink to this comment

Hi Diane,

You mean if you type anything in a cell below or next to your table, Excel does not expand the table automatically?

Comment by: MAUREEN (23-11-2010 16:52:46) deeplink to this comment

Hi
When I export a query to excel it comes up with field names heading.
There used to be a funtion in the previous excel which you could exclude field names in the "External Data Range Properties" why has this been taken out? and can I get it back. I noticed in spreadsheet created before the 2007 upgrade have still kept this function.

Comment by: Jan Karel Pieterse (24-11-2010 00:28:17) deeplink to this comment

Hi Maureen,

Indeed you can no longer set that up in the query properties. You can however set the Table to hide it's header (see the Table tools tab in the ribbon, Table Style Options group).

Comment by: Kami Borisova (24-11-2010 02:59:06) deeplink to this comment

Hi,

When I type a formula, for ex.=vlookup(Table5[[#This Row];[Material]],PriceList$A:$E,2,0) and I want to drag it into the next columnq but to change only 2 with 3, it happens that the header [Material] changes also with the header of the next column. Can I prevent it?

Comment by: Jan Karel Pieterse (24-11-2010 08:40:41) deeplink to this comment

Hi Kami,

The easiest way I can think if right now is to hit F2, select entire formula, hit control+c and then move to the target cell and press control+v. Then edit the target cell to change the 2 to a 3.
Not very simple, I know.

Comment by: Kami Borisova (24-11-2010 23:35:59) deeplink to this comment

Hi Jan,

Thank you very much for the respond.
I thought it might be a simple way, but I guess I'm wrong :).
Anyway, let's hope Microsoft will make it easier.

And you continue doing this because it helps a lot!

Best regards,
Kami

Comment by: Graeme (30-11-2010 03:10:56) deeplink to this comment

hi, i have a table of records with a total, i have a macro in place so that when new data is entered into the input form it adds it to the top of the table as a new record, however the total does not take into acount the new record, it just drops the formula down a row.
thanks, Graeme.

Comment by: Jan Karel Pieterse (1-12-2010 09:40:12) deeplink to this comment

Hi Graeme,

The simplest solution is to have your total formula refer to a range that includes one extra row. So ifyou're inserting on row 3, make sure row 2 is included in the formulas.

Comment by: Leif Holstad (14-12-2010 08:16:00) deeplink to this comment

Hi,

I have been using tables for many years and have had few problems. Now I have got a problem in Excel 2007 concerning autofiltering. The filter is set to select just one of the optional entities. The result is showing the selected entity plus some others which should not show. From my experience this should not be possible. How can this be solved?
Many thanks in advance!

Regards,
Leif

Comment by: Jan Karel Pieterse (14-12-2010 09:25:05) deeplink to this comment

Hi Leif,

All I can think of is that the autofilter range is improperly specified and does not include all rows of your table.

Comment by: Forrest (26-12-2010 14:32:19) deeplink to this comment

Hello Jan - First - let me thank you for your well written tutorial on Tables for Excel 2007 - great stuff.

I have a particular task I need to complete and am wondering if using Tables is the answer.

I have one range (could become a table) that contains hundreds of financial transactions (one per row) - columns include "date" (of transaction), description, amount, currency, etc.

I have another that is a range of dates and currency conversion rates (one row per day for 5 years) - 1st column is the date, columns B-X are the conversion rates for the date for various currencies (e.g. column B is euros, C is yen, D is Australian dollars, etc.)

What I need to figure out is how, for every transaction in the first data set, to go to the second and extract the appropriate currency conversion rate, and convert to the target rate (US dollars in this case)...

I have a sneaking hunch that using Tables might make this task easier - your thoughts?

Comment by: Jan Karel Pieterse (28-12-2010 06:06:35) deeplink to this comment

Hi Forrest,

Tables may help, especially because you don't have to worry about pointing your formulas to enough rows. What you need is the VLOOKUP function.
Rather than trying to describe the function here, I suggest you look up the function in Excel help, it is rather complete on how it works.
Pay special attention to the last argument of the function.

Comment by: Ute Simon (10-1-2011 11:34:51) deeplink to this comment

Hi Jan,
Thank you for this excellent tutorial!

I have a table (TableA) on the first worksheet of a file. It is constantly growing, because PersonA enters new data daily. I insert a new table (TableB) on the second worksheet of this file and reference to the first three columns of TableA, PersonB has to enter data in the other columns (the data cannot be managed in one table for privacy reasons).
If new rows are added in TableA, is there any way to make them automatically appear in TableB, so that TableB is growing automatically, too? Or do I have to look at the last row in TableA and resize TableB manually by dragging the corner or entering a new range? (I tried entering a formula in the "Change size", but it's converted to a fixed cell address.)

Comment by: Jan Karel Pieterse (10-1-2011 23:19:19) deeplink to this comment

Hi Ute,

You'll need some VBA code that copies the new line to the other table. But you probably also need code to update existing lines in table B when the user edits existing material. I cannot show you example code because I do not know your precise setup.

Comment by: Allen (20-1-2011 07:56:47) deeplink to this comment

Hi, this works in the immediate window but not in a function:

Comment by: Jan Karel Pieterse (20-1-2011 08:22:10) deeplink to this comment

Hi Allen,

Maybe in the function there is no range called MtgTable on the active sheet when the code tries to execute the statement?

Comment by: Stefan Kemp (17-2-2011 13:04:18) deeplink to this comment

Hi Jan,

Is there any way that a table can still autoexpand on a protected sheet? I have a table with all it's cells unlocked and the cells immediately below it unlocked, but if I protect the worksheet then the table no longer expands automatically. In fact, even when I unlock ALL the cells in the worksheet, and protect it with ALL the protection options selected, the table won't auto expand. Is there any way around this?

Thanks.

Comment by: Jan Karel Pieterse (20-2-2011 11:12:13) deeplink to this comment

Hi Stefan,

No, tables on protected sheets cannot grow. All you can do is allow the user to insert rows when settin up protection (you'll have to unlock the cels in the tbale too of course). Then if a row is inserted within the table, the table grows. BUt the table does not auto-expand when you add a row below the table or a column to its right or left.

Comment by: tony (3-3-2011 11:24:09) deeplink to this comment

What is the best way to write to a row column intersection within a table using vba code?

Comment by: Jan Karel Pieterse (4-3-2011 02:01:55) deeplink to this comment

Hi Tony,

Depends on what information you know about the column and row: how do you find the row/column you're interested in?

Comment by: Kami Borisova (25-3-2011 04:43:32) deeplink to this comment

Hi Jan,

I have 2 tables in 2 sheets. The first column in the both tables contains numbers - the same numbers. But in the one of the tables the numbers repeat in different rows /not necessary one after another/, in the other - one number=one row. The next columns contain different infos. So, the question is - is there a way to "transpose" the info from any of the columns from the table with the repeated numbers to the table with the unique numbers, but to make the info to go in different columns, depending on the unique number?
Did I manage to explain it clearly? :)
Maybe there is a combination of functions, but I just can't think of some :(

BR,
Kami

Comment by: Jan Karel Pieterse (25-3-2011 05:13:42) deeplink to this comment

Hi Kami,

I advise you to ask this question at www.eileenslounge.com

Comment by: Jacinto (15-4-2011 01:22:39) deeplink to this comment

I need to create a pivot table in excel 2007 using another excel file as the data source.

In excel 2000 this was easy since the pop up menu gave a choice "Microsoft Excel List or Database". This does not seem to be available in 2007. Appreciate your help. thank you.

Comment by: Jan Karel Pieterse (15-4-2011 02:59:05) deeplink to this comment

Hi Jacinto,

Use the old Pivot table wizard. It is not in the ribbon, but you can find it if you right-click the QAT and select customize Quick Access Toolbar. Then the wizard is in the category commands not in the ribbon. STart the wizard and select the external data option in step 1.

Comment by: Jacinto (16-4-2011 00:42:19) deeplink to this comment

Hi Jan, Followed your directions but am unable to find the wizard. I did look into all the sections (Popular, Formulas, Proofing, Save, Advanced, Customize, etc). Please help. Thank you.

Comment by: Jacinto (16-4-2011 00:50:07) deeplink to this comment

Jan, Thanks. Got the tip from this location
http://www.pcreview.co.uk/forums/excel-2007-pivot-table-wizard-t3602763.html

Comment by: David (18-4-2011 12:49:20) deeplink to this comment

Hi Jan, I am trying to create a dynamic chart that will keep changing as we add columns to the data. I converted the data to a table and added some columns as a test. The chart is not expanding when this is done. Also, the new headers show up as a number on the chart instead of a month (text). Thanks for your help!

David

Comment by: Jan Karel Pieterse (19-4-2011 00:08:25) deeplink to this comment

Hi David,

From the top of my head, I don't know whether a chart will reflect additional columns added to a table. It does work with adding rows however, so maybe you can put that to use?.

Comment by: J Rott (22-4-2011 08:49:05) deeplink to this comment

I am pulling data from a database to excel 2007 into a table, the table is named the same name as the database (access 2007). This data is a truck loading schedule and gets updated daily. I have built macros to help format the data a bit, then copy the table to a new worksheet via macro make some additional changes to new table on the new sheet. change to a basic range and use subtotal. I am doing this since subtotal doesn't work with a table. THe problem I have is the name of the table changes, adds a number to the end of the name. I have tried building a macro to change the name to the same name I built the original macros on, but that doesn't work for the same reason. I have searched everywhere for a fix/workaround. I don't change the table I download, because I don't want to mess it up for the next download. Any ideas?

Comment by: Jan Karel Pieterse (26-4-2011 00:33:19) deeplink to this comment

Hi,

Judging from your description I expect your code is recreating the querytable everytime you have it refresh. This causes Excel to add a new named range and a new table each time. Instead, you should be refreshing the existing query. Then the name will no longer change.

Comment by: mohammed ageel (20-5-2011 11:38:07) deeplink to this comment

Hi David,
I have 288 charts in my research and need to change the same things in its some properties did there a maner to make the change in only chart and drag the change to anothers bu one thing.
very thank to you
note i dont have time to make the change one one, therefore i wish and wiat helpful

Comment by: Jan Karel Pieterse (22-5-2011 03:25:10) deeplink to this comment

Hi mohammed,

Maybe this utility helps:
www.jkp-ads.com/officemarketplaceff-en.asp

Comment by: Jerry (3-6-2011 10:18:30) deeplink to this comment

In excel 2003 the list function used a blue border and preempted the next line. In the case of 2007, the table will expand but only with a manual entered number or text on the subsequent line. The validation function does not transfer to the next line until the expansion takes place. Is there a way to activate the blue border with *

Comment by: Jan Karel Pieterse (5-6-2011 23:44:40) deeplink to this comment

Hi Jerry,

Unfortunately they removed that feature, I agree it's a pity!

Comment by: Talat usman (7-6-2011 23:36:01) deeplink to this comment

dear Sir
Thank you very much for teaching me

Talat usman

Comment by: Becky (10-6-2011 10:06:08) deeplink to this comment

I am looking for help on structured referencing- and you have a lot of helpful pages- thank you!

BUT...when I try the example you give above about typing directly to right of a table "=Sum(..." and picking cells in the table...it doesnt give structured references- only D15, A12 etc. What am I missing??? hmmm...

- I have checked setting for formulas to use table names.
- (unrelated) I have checked GetPivotData option
- I have tried saving as .xlsm versus .xls --.xlsx wont save due to macros in my file...fine.
- for both .xls and .xlsm I tried creating a table from scratch and attempting your example- same result - no structured references...

any help appreciated- thanks!

Comment by: Jan Karel Pieterse (14-6-2011 00:23:09) deeplink to this comment

Hi Becky,

Maybe the file was in 97-2003 file format?
In that case, save as 2007 format, close and re-open the file.

Comment by: ribka (23-6-2011 19:25:50) deeplink to this comment

Hi Jan,

Can we customize the color in the table become the same for example every 3 rows instead of every 1 row like the template given?

Thank you.. :)

Comment by: Jan Karel Pieterse (24-6-2011 05:30:05) deeplink to this comment

Hi ribka,

Sure. Rightclick the tablestyle and click duplicate if it is a built-in style, or modify if it is a custom one. Then select "Second row stripe" and change the stripe size.

Comment by: ayca (6-7-2011 06:57:53) deeplink to this comment

Hi,

I would like make up a new table format via "new table style". But I would like to see my new style not only in the current workbook (inwhich I have made the style), but in every excel workbook. how can this be possible?

Thanks in advance,

Comment by: Jan Karel Pieterse (6-7-2011 09:48:52) deeplink to this comment

Hi Ayca,

1. Open an empty workbook
2. Insert a table into it containing some dummy date
3. Create the table style you need
4. Remove the table and all of its contents
5. Save-as your file, choose filetype template (either xltx or xltm, depending on what default file format you want to have)
6. Save the file in your XLSTART folder and call it Book.xlt (x or m)

Comment by: Laura (15-7-2011 03:48:13) deeplink to this comment

Hi,

How do I creat a new table style from an existing table?


Thanks

Comment by: Jan Karel Pieterse (15-7-2011 11:35:07) deeplink to this comment

Hi Laura,

You can right-click any existing table style and select "Duplicate". After that you can edit the duplicated style.

Comment by: Joan Cullemore (18-7-2011 07:31:24) deeplink to this comment

We have "inherited" an excel file with pivot tables created by a previous employee, hardly necessary for this information. Now we need to now how to see rows that appear bewlo the "pivot headings row", We can use either Excel 2003 or 2007. The Unhide rows feature does nothing.

Comment by: Jan Karel Pieterse (19-7-2011 01:34:43) deeplink to this comment

Hi Joan,

Maybe one of the fields of the pivot table has a filter applied? Just click the dropdowns next to each field (in turn) and make sure no filter is applied to any of them.

Comment by: leev (22-7-2011 23:23:43) deeplink to this comment

thankssss~!!!
very nice tricks, with printscreen to help us, thumbs up ! ^^

Comment by: Marie (24-7-2011 12:56:33) deeplink to this comment

What a life saver! Thanks for explaining the new "Table Tools" ribbon. I just spent two hours trying to figure out why that tab appeared on one workbook but not the other. Now I can recreate it effortlessly. (sigh ...)

Comment by: Wilson (10-8-2011 12:28:30) deeplink to this comment

How can I remove an existing table? I tried convert to range but it is taking too long.
is there a vba function to check if there is an existing table on the worksheet.

would it be better if i use resize table instead of removing the table?

Comment by: Rajendra Pai (12-8-2011 21:02:27) deeplink to this comment

Is there a way to protect some parts of a table?
Example:
A table has three columns: Column 1 has Dates, Column 2 has amounts and Column 3 has the cumulative total of amounts.
Is there any way to prevent the user from modifying Column 3 while retaining the Table functionality?

Thanks in advance/

Comment by: haari (16-8-2011 13:46:33) deeplink to this comment

how can i change the row headers to specific names??
i know how to change the A,B,C's in column headers to 1,2,3.I wnat to find out how I can do that for row headers?

thank you

Comment by: Jan Karel Pieterse (22-8-2011 02:06:27) deeplink to this comment

Hi Haari,

I'm afraid that is not possible in Excel.

Comment by: Jan Karel Pieterse (22-8-2011 02:13:06) deeplink to this comment

Hi Rajendra,

You could unlock the cells you want the user to be able to modify and then protect the worksheet, but I seem to recall that does restrict the functionality of the table.

Comment by: Jan Karel Pieterse (22-8-2011 02:35:53) deeplink to this comment

Hi Wilson,

Do you also want to remove the content of the table, or just convert it to a range?

You can check for a table like this:

Comment by: Wilson (22-8-2011 09:30:46) deeplink to this comment

Hi,

actually I would like to remove the table so i can recreate the table. i tried to do a resize but it says table exists. I would need a function to check if a table exists and if there is a table, how can i resize it with VBA.

the above function works well in checking for an existing table but i'm looking forward to a resize with it.

Comment by: Jan Karel Pieterse (23-8-2011 05:54:48) deeplink to this comment

Hi Wilson,

This clears the cells and removes the table:

Comment by: vinutha s hegde (26-8-2011 10:36:05) deeplink to this comment

how can i enable the table name in formulas? I could do this in Excel 2003, Here, it is not taking that formula

Comment by: Jan Karel Pieterse (29-8-2011 01:33:23) deeplink to this comment

Hi Vinutha,

Excel 2003 did not have the structured table referencing that Excel 2007 and 2010 have, so I think I don't understand your question?

Comment by: Staf Laenen (8-9-2011 04:52:25) deeplink to this comment

Hi,

I believe Vinutha refers to the label names (or natural language formulas) that in excel 97-2003 allowed to use a column header in a formula.
This option is no longer supported in Excel 2007-2010.

Comment by: Jan Karel Pieterse (8-9-2011 06:58:10) deeplink to this comment

Hi Staf,

I think you are correct.

Comment by: chetan patil (4-10-2011 08:34:59) deeplink to this comment

how to read table from excel 2010 in vba....can you provide code for the same? or how to combine multiple ranges to table?

Comment by: Jan Karel Pieterse (5-10-2011 00:10:06) deeplink to this comment

Hi chetan,

The next page of this article shows VBA code examples...

Comment by: Paul Cheadle (15-11-2011 03:31:25) deeplink to this comment

I have a table that has refreshable sql data coming in. If I insert a column for manual entries and set the external data properties (right click on table - table - external data properties - advanced) to 'insert a whole line' it doesn't shift my added column data in tandem and then everything goes out of sync.

Comment by: Jan Karel Pieterse (15-11-2011 05:12:31) deeplink to this comment

Hi Paul,

Excel does not insert whole rows when refreshing tables, only in the area below the table. If you need to keep comments in synch with the data, you must use an additional table which refers to the data table, using any unique key in the comments table which ties your comment to the appropriate row in the data table.

Comment by: shereen (17-11-2011 00:34:00) deeplink to this comment

When I try to enter a VLOOKUP formula on a column of data in table - I get an error.

Comment by: gecs (29-11-2011 04:09:09) deeplink to this comment

Because the name of the table points dynamically to the data range of the table it is very helpful to use the data stored in a table column to create data validation lists.

Just a tip: using structured references for creating such lists you can't use a formula like:
=Table_Name[Column_Name]
but it works including the structured reference as the argument for the INDIRECT function:
=INDIRECT("Table_Name[Column_Name]")

Comment by: ripudaman (13-12-2011 23:10:18) deeplink to this comment

i am calculating (Total Hours * Rate per Place)*24) its gives me the wrong answer...

pl help

Comment by: Jan Karel Pieterse (13-12-2011 23:19:46) deeplink to this comment

Hi ripudaman,

What answer do you get and what are you expecting to get?
Tip: is the cells numberformat set up properly?

Comment by: NISHA (21-2-2012 21:06:10) deeplink to this comment

WHEN I SELECT A RANGE FOR MY TABLE IT PROMPTS TO REMOVE AUTOFILTER IN MY SHEET. HOW CAN I DO THIS..PLEASE HELP

Comment by: Jan Karel Pieterse (23-2-2012 03:02:19) deeplink to this comment

Hi Nisha,

Please do not write in all caps, it is hard to read an is considered as if you are shouting.

Just turn off autofilter and then convert the range to the table.

Comment by: zainul (17-3-2012 03:31:29) deeplink to this comment

how i write the numeric into words with rupees in excel sheet

Comment by: Jay (19-3-2012 03:54:17) deeplink to this comment

I created some tables and i want to change the name of the tables because it comes up like table5, table2, etc?

Comment by: Jan Karel Pieterse (19-3-2012 05:50:20) deeplink to this comment

Hi Jay,

Have a look at the section called "Table Options on the Ribbon" near the top of this articlt.

Comment by: Jan Karel Pieterse (19-3-2012 05:51:56) deeplink to this comment

Hi Zainul,

You need a special VBA function for this. Microsoft has an example on their website, but it is English:

http://support.microsoft.com/kb/213360

Comment by: Nguyen Thi Thanh Nhan (12-4-2012 02:15:29) deeplink to this comment

I want to merge 2 cells in a table. But I see in ribbon, this button is disabled. Excel 2007 can not merge 2 cells in a table? If I want to do that, what have I do?

Comment by: Jan Karel Pieterse (12-4-2012 04:43:17) deeplink to this comment

Hi Nguyen,

No, cells in a table cannot be merged.

Comment by: chris (30-4-2012 07:17:24) deeplink to this comment

what is used to add another row to the table!!!!!!!!!!!!!

Comment by: Jan Karel Pieterse (1-5-2012 04:48:09) deeplink to this comment

Hi Chris,

You can just type your info on the first empty row beneath the table, Excel will automatically expand the table.

Comment by: Chris K (16-8-2012 15:52:16) deeplink to this comment

Hi,

Background:

I have a very large sheet which performs multiple calculations using a data range, i currently use find and replace to update the cell references when i add to the range.

My issue: I figured using tables would be much more efficient as the cell references within the formulae update automatically but i have found that copying and pasting these new rows (never more than a few hundred) is taking longer than the way i currently do this. Does anyone know why?

Comment by: Jan Karel Pieterse (16-8-2012 16:13:58) deeplink to this comment

Hi Chris,

This is because Excel does extra work like expanding the table's range when you do a paste.
What helps -if you have to paste more than once- is to paste but leave one empty row between the paste and the table. Then once you're done, you manually expand the size of the table.

Comment by: fas (12-9-2012 07:30:04) deeplink to this comment

when i create a table in excel and protect the sheet with password, new row not insert, though i have checked the option while protectin the sheet

Comment by: Jan Karel Pieterse (12-9-2012 09:49:20) deeplink to this comment

Hi Fas,

Unfortunately, this is "by design": you cannot expand tables on protected worksheets.

Comment by: Annette Hartz (22-9-2012 23:22:16) deeplink to this comment

Hi!

I use some rows underneath the table with a different row height. When I add new rows to the table (with a button/macro), the different row height underneath the table is "moving up", changing the layout of the part underneath the table and - as soon as it "reaches" the bottom part of the table, the table itself. Is there a way to stop that?

Comment by: Jan Karel Pieterse (24-9-2012 11:44:05) deeplink to this comment

Hi Anette,

You mean that the rows beneath the table do not shift downwards when a row is added to the table, correct?
One way around that would be to first insert an entire row immediately beneath the table and then add the row.

Comment by: Pat (26-9-2012 17:47:01) deeplink to this comment

Hi Jan,
Thank you for providing the info and also replying to the questions.
In my case I have 2 tables, 1 table has data and the other just sums each column. It is similar to the total row but it is a different table. Both tables can grow. When Table 1 grows by one column I want to add a column to Table 2 also and then copy the formula from the previous column of Table 2. Is this the best way to do it? How can the new column in Table 2 refer to the new column in Table 1? Is there a way to refer to the table column by the column number instead of the column name? Also if I add a new column in a table and if the last column was a calculated column then how can I automatically copy the formula to the new column added.
Thanks for all your help.

Comment by: Jan Karel Pieterse (26-9-2012 19:18:19) deeplink to this comment

Hi Pat,

I'm sorry, can you ask this question here:

www.eileenslounge.com

I'm tied up at the moment.

Comment by: Jan Karel Pieterse (27-9-2012 13:07:48) deeplink to this comment

Hi Pat,

I guess you'd need a bit of VBA code, as there is no automatic way of doing this.
A simple solution however is to NOT use structured referencing in your sum formulas, but rather simple
=SUM(A2:A10).
If you add a row to the table, the formula will auto-adjust.
Also, you can simply copy the formula to the left or right and the column will adjust because it is a normal cell reference.

Comment by: Pat (27-9-2012 15:39:29) deeplink to this comment

Hi Jan,

Thank you for your inputs. I will use this and try to work out something and let you know if there are more issues.

Thanks again.

Comment by: rosa (2-10-2012 07:12:36) deeplink to this comment

Hi--

Above you state that you can turn the auto-expand off, but where do I make those changes? I do want to control certain things that tables assume, but am going mad looking for where I might actually make such a change. Specifically, i have put dates in the column header, 2 weeks apart. I delete one of the dates and it now auto enters the date to its left, plus one year. Argh!

Comment by: Jan Karel Pieterse (2-10-2012 09:23:17) deeplink to this comment

Hi Rosa,

If you enter a value to the immediate right of a table and it expands, a small rectangular object appears near the new column. Hover your mouse over it and you'll see autocorrect options.

Click the box and you can select "Stop automatically expanding tables".

So this behaviour is in the autocorrect options:
File, Options, Proofing tab, autocorrect options, Atuformat as you type tab.

Comment by: Adrian (3-10-2012 04:19:28) deeplink to this comment

Is there a way to insert a vaiable into the second row number of a summation in oder to use anoter cell to dictate how many rows a sum() cell should use?

Comment by: Jan Karel Pieterse (3-10-2012 09:25:38) deeplink to this comment

Hi Adrian,

I am not sure what you are trying to achieve?

Comment by: Allie C (12-10-2012 01:13:06) deeplink to this comment

Hi,
I was wondering if you could help with creating links to a table. The issue is that I am referencing specific cells of a table, and when the table is resorted the links remain on the cell requested. It seems that this is not a capability because excel tracks the cell description and not the value description.
Thanks in advance,
Allie

Comment by: Jan Karel Pieterse (12-10-2012 12:28:12) deeplink to this comment

Hi Allie,

Perhaps you can use the HYPERLINK worksheet function to create a dynamic link. Suppose you want to refer to this cell:

='Sheet A'!$A$1

Then the hyperlink function would look like this:

=HYPERLINK("#'Sheet A'!$A$1","Hyperlink text")

So the trick is, to find the address of the cell you are looking for.

Suppose the value you need from the table is in cell F1 and the table is named Table1 and you are looking in Column1 of the table. This formula gives a direct link to the found cell:

=HYPERLINK("#"&CELL("Address",OFFSET(Table1[[#Headers],[Column1]],MATCH(F1,Table1[Column1],0),0,1,1)),"Link to: " & F1)

Comment by: Gerry (15-10-2012 03:55:13) deeplink to this comment

Hi, is it possible to create a table which is based off information in another table that updates when the original table is updated; for example, if I add a row in one table, is there a way to have the other table add a row, and calculate based on the original table data as the other rows in the second table do? Thanks.

Comment by: Jan Karel Pieterse (15-10-2012 13:05:20) deeplink to this comment

Hi Gerry,

There is no automatic way to do this, apart from either using a bit of VBA code, or by pre-filling extra rows in the second table which do not display anything as long as the associated row in the first table is empty:

=IF(Table1[Column1]="","",Table1[@Column1])

Comment by: Jan Karel Pieterse (14-11-2012 11:04:27) deeplink to this comment

test message

Comment by: Hasan (15-12-2012 06:55:14) deeplink to this comment

Thanks a lot for the very helpful information. I noticed that you could reference a cell in the same row of a table by typing the column name(example : "=[Column1]" rather than typing "=[@Column1]", however, if the formula contains a need to reference the whole column "=[Column1]*[@[Column1]]" and you use the column name. Are there are performance differences?

Comment by: Jan Karel Pieterse (17-12-2012 09:39:38) deeplink to this comment

Hi Hasan,

I don't think it makes a big difference, but I do think it is better to be explicit on what your formula is using. Some functions WILL use the entire column if you use this referencing technique and others will not, depending on the function.

Comment by: Scott Shanks (16-1-2013 13:40:20) deeplink to this comment

Hi Jan,

As soemone thats used Tables including in VBA since 2007, this is still a really useful resource!

Is there a more elegant/concise way to get the lngRow & lngCol values below?

Public Sub GetTableLocation()

    Dim ACell As Range
    Dim tbl As ListObject
    Dim lngRow As Long, lngCol As Long
    Dim strColumn As String
    
    Set ACell = ActiveCell
    Set tbl = ACell.ListObject
    
    With tbl
        lngRow = Selection.Row - .HeaderRowRange.Row
        lngCol = Selection.Column - .ListColumns(1).Range.Column + 1
        strColumn = .ListColumns(lngCol).Name
Debug.Print .ListColumns(strColumn).DataBodyRange(lngRow)
    End With

End Sub

Comment by: Gwen (16-1-2013 21:30:03) deeplink to this comment

I would like to be able to modify a table and just view that same table in multiple worksheets in the same workbook.

I am really using this so that I can use the table as a constant list, whose size and data (strings only) may change from time to time and display that data in all worksheets. If there is a better way to do this, let me know.

Thanks for any help you can provide.

Comment by: Jan Karel Pieterse (17-1-2013 08:31:34) deeplink to this comment

Hi Gwen,

Why do you want to show that same list on all worksheets?

Comment by: Gwen (17-1-2013 17:34:21) deeplink to this comment

I just "inherited" a workbook where each sheet is an employee of the company, with data for their employee reviews. Each worksheet is printed and given to each employee at review time. But at the bottom, we'd like to put a note (same note for everyone) about benefits or benefit changes so that the information is on each employee's sheet when they get it.     It is all text. It could be a text box, or any other object to display text.     I just don't want to copy and paste for each employee (80+) when there are changes.
I an relatively new to Excel, but am a programmer. I have done a bit of looking into Text Box, Table, List, etc.. I would like for who ever "inherits" this from me to not worry about the number of lines in the text (table or list). I would like them to be able to change the text in one place and have it update on all employee worksheets.

Comment by: Gwen (17-1-2013 18:00:25) deeplink to this comment

*add to last note*

If there is a way to put Carriage Returns in a text field, then I can format the cell for wrap, make it big, and it should work to fit my needs. But I couldn't find a way to put CRs in the text

Comment by: Jan Karel Pieterse (18-1-2013 16:36:27) deeplink to this comment

Hi Gwen,

You could put the note in the footer of the sheets.
If you group all sheets (by clicking the first and shift+clicking the last), you can set up that footer for all sheets in one go.
Same to change the footer text later.
Do not forget to ungroup the sheets (right-click the tab and select ungroup sheets) when you're done.

Comment by: Ziyauddin Kazi (20-1-2013 15:46:17) deeplink to this comment

01)Night Duty is counting from 22:00 To Morning 06:00
02) I am working
A) From 18:10 To 23:20 ( Night Duty will be 1:30 )
B) From 20:30 To 03:15 ( Night Duty will be 5:15 )
C) 22:30 To 04:25 ( Night Duty will be 5:55 )
D) 2:10 To 07:30 ( Night Duty will be 3:50 )
What formula i can use to find above Night Duty.
Waiting for reply by mail.
Thank you

Comment by: Jan Karel Pieterse (20-1-2013 18:59:54) deeplink to this comment

Hi Ziyauddin,

Suppose the starttime is in cell A2 and the end time in B2.
In C2 enter this formula to calculate the total hours worked:

=IF(B2<A2,1,0)+B2-A2

In D2, this formula then gives # night shift hrs:

=IF(A2<TIMEVALUE("22:00"),A2-TIMEVALUE("22:00"),0)+C2

Comment by: sonu (21-1-2013 08:45:31) deeplink to this comment

how to link table in another work sheet by using check box

Comment by: Jan Karel Pieterse (22-1-2013 08:52:06) deeplink to this comment

Hi sonu,

Can you please give more information?

Comment by: Vicky (10-2-2013 23:21:46) deeplink to this comment

Hi,
Thanks for the information above. It really helps.

I am trying to get do a cell reference say cell A1 in a sheet to a table (say R2C3).
When i do so it just shows in RC format.

How can i use the =TABLE[ function to do this referencing.

I need a formula so that i can use it and not to reference to the cells in table manually.
Please help.

Comment by: Jan Karel Pieterse (11-2-2013 11:39:54) deeplink to this comment

Hi Vicky,

Are you trying to add a formula using VBA?

Comment by: Khurram Ali (11-2-2013 20:20:34) deeplink to this comment

Your introduction to data tables deserves my thanks.

My question is how can the data table replace the offset function to create a dynamic range and an interactive range formula. I have heard that since the arrival of Excel 2007, it is considered that table feature of excel is superior to offset function for creating a dynamic/interactive range etc.
Could you please explain, with some easy to advanced examples (better if in flv files) so that I can learn and start making good use of the data table.

Thanks

Comment by: Jan Karel Pieterse (12-2-2013 08:55:45) deeplink to this comment

Hi Khurram,

Anywhere where you would normally use a dynamic range name, you can instead use a direct reference to the table. Either by using the structured table referencing shown in this article, or by using direct cell references as you're used to.
Excel will make sure your range expands and contracts with the size of the table. Dynamic range names using OFFSET are no longer necessary.

Comment by: ALF (19-2-2013 14:05:23) deeplink to this comment

HI,

This feature is great but I have issues seeing tables created in Excel with MS Query (Which would made sens...) Is there a fix around this? I have tried to look on several forums but the only answer is Convert your table into a rang (Not Dynamic range becaus MS Query does not see Dynamic ranges either...) Do you know of any fix or work around for this?

Thanks in advance :-)

ALF

Comment by: Jan Karel Pieterse (19-2-2013 16:31:14) deeplink to this comment

Hi Alf,

If you are trying to use MSQUery to pull in data from a table in another file, simply create a range name that spans the entire table. The range name will auto-expand with the table AND MSQuery will be able to find the range name.

Comment by: Shripad Lale (6-3-2013 06:49:07) deeplink to this comment

Thank you, Jan Karel Pieterse! Your tip saved my life! Funny how such an important piece of information is so hard to find! Now, my custom table styles are available to all worksheets. This was important to me, since I don't care too much about the choice of colors the default styles use. Too loud for my taste! Thanks again.

Comment by: Praveen (30-3-2013 13:25:02) deeplink to this comment

Hello,

I have a Master Database with employee details. I want all employees from one department in individual sheets in the same workbook.
Please help

Comment by: Jan Karel Pieterse (2-4-2013 14:02:52) deeplink to this comment

One thing you can do is create a pivot table using the source data and add the employee name as a page field. Then you select The options tab of the pivot table tools and click the Options button on the left. Select "Show Report Filter pages".

Comment by: Mamadou (26-4-2013 03:19:50) deeplink to this comment

I'm going to have my first finance course next May. Glad to be on this site. Tks for this lessons

Comment by: Klaas Wijbrans (26-4-2013 09:35:17) deeplink to this comment

I have a table with in its header numeric data. I need to interpolate data values for a specific row. I tried to do this by doing a MATCH less than on the header information, but I am getting a #N/A as a result. Is there any way around this?

For example:
headers 400, 1380, 1385, 1400, 1600, 1700
item1 10, 1.0, 100, 1, 1, 100

And then I need to interpolate for example for the value 550, which should find 400, and then interpolate between 400 and 1380.

Is there any way around this, or would I need to fall back to VB?

Comment by: Jan Karel Pieterse (26-4-2013 14:31:28) deeplink to this comment

Hi Klaas,

The MATCH function always returns either an exact match (or NA if not found), or the next higher or lower match(depending on the last argument and sort order). It cannot interpolate.

So if 550 is in cell C3 and your first row starts in cell A1, this finds 400:

=INDEX(A1:F1,MATCH(C3,A1:F1,1))

Not sure how you would want to interpolate, linear?

Comment by: Jonathan Papillo (3-5-2013 23:46:47) deeplink to this comment

I am building a data spreadsheet using tables.

In one of my table columns I have a yes/no as to if this particular row should be reported

I have built a macro that will copy the table to a new spreadsheet and will start walking down the table and deleting rows that are not to be reported, giving me exactly what I am looking to report out.

Unfortunately the only way I was able to make my macro work was to hard code the size of the table (which of course is going to be always changing/growing).

How in the macro language can I reference the table and get the number of rows it contains so my code can get away from the limitation of hard coding.

My table is on a sheet called "AI List" and my table is called "AITable"

I am very new to VBA so code samples for me to start from would be the most helpful.

Thanks,

-J

Comment by: Jan Karel Pieterse (4-5-2013 10:47:30) deeplink to this comment

Hi Jonathan,

The data area of a table can be accessed like so:

Worksheets("AI List").ListObjects(AITable").DataBodyRange

Comment by: Hamid (4-6-2013 10:26:20) deeplink to this comment

Hi there,
I am using tables as you have described, and my question is:
I have 5 different table. there is a 6th table that gathers its information from the first 5 tables.
How can I refer to which table to look at using cells in the 6th table?
another way of putting it is, when looking up I need to specify 1)a cell to be looked up, 2)a range 3)the column/row index number.... how can I identify the range (which can be from different tables) using a value in a cell?
If i'm not making much sense it's probably because I am not making sense; but if you email me I can send my workbook and you can see what I mean...
regards
H

Comment by: Jan Karel Pieterse (4-6-2013 11:53:28) deeplink to this comment

Hi Hamid,

You might be able to use the INDIRECT function to do what you want perhaps? To know the exact argument the INDIRECT function needs, it is best to first create a direct cell reference in the formula and study that syntax closely. Then you can try to recreate that in the INDIRECT function.

E.g. suppose the sheetname is in cell A1 and you want to get cell B1 from that sheet:

=INDIRECT("'" & A1 & "'!B1")

The apostrophe's are needed in case the sheetname contains a space character.

Comment by: HAMID (4-6-2013 15:30:49) deeplink to this comment

Worked perfectly,
Thanks a lot :)

Comment by: Jacob Miller (4-6-2013 19:45:02) deeplink to this comment

Hi,


I was ecstatic when i realized that tables autofill formulas into newly inserted rows, until i ran into the following problem -

I have a table where each row contains three formulas referring to a cell to the left on the same row (an ID number), which pulls data from another worksheet.

When i insert a new row, rather than referring sequentially to the cell in the same row, say A15, the formula referrs to the cell immediately below, A16. What is the fix?

Comment by: Jan Karel Pieterse (4-6-2013 20:14:12) deeplink to this comment

Hi Jacob,

Two questions:

1. How many columns does the table have in total and how many contain a formula?

2. Can you share the formula?

Comment by: Jacob Miller (5-6-2013 19:58:46) deeplink to this comment

1. 17 columns, three of which contain a formula, all of which should refer to the same values in column A.

2. The formula is a bit convoluted, because it refers to a separate workbook but here it is (from row 6): =VLOOKUP(A6,CHOOSE({1,2},'[DO NOT COPY - CACIQUE IDS.xls]Day Center Clients'!$B$2:$B$18000,'[DO NOT COPY - CACIQUE IDS.xls]Day Center Clients'!$A$2:$A$22000).

The key issue here is, the row 6 VLOOKUP function correctly refers to A6, just all the other formulas in the table refer to the column value in the same row as they are. But when i insert a new row, the formula autofills incorrectly. rather than VLOOKUPing "A7", it pulls from "A8", the row immediately below.

Comment by: Annie (6-6-2013 05:21:17) deeplink to this comment

What is required to be able to resize tables when worksheet protection is applied for locked cells and the columns the table uses are unlocked?

Comment by: Jan Karel Pieterse (6-6-2013 08:10:21) deeplink to this comment

Hi Jacob,

The formula looks incomplete to me, as it does not have the column argument of the VLOOKUP function. Also, it is unclear to me what the CHOOSE function is doing, given it has a fixed array argument between brackets.

If I try a "normal" vlookup formula pointing to a different workbook, all is fine, it keeps pointing to the same row as it did after inserting a row in the table.

I have heard of this situation before, but alas cannot reproduce it.

Perhaps you can send a copy of your file to me by email?

Comment by: Jan Karel Pieterse (6-6-2013 08:12:14) deeplink to this comment

Hi Annie,

The only way I know is to have a bit of VBA code that does the insertion (after first unprotecting and reprotecting afterwards.

Comment by: Jacob Miller (6-6-2013 19:16:32) deeplink to this comment

My fault, I cut off the formula when pasting it over - the actual formula includes the column argument as follows: =VLOOKUP(A8,CHOOSE({1,2},'G-drive:Users:Reception:CACIQUE ID NUMBERS:[DO NOT COPY - CACIQUE IDS.xls]Day Center Clients'!$B$2:$B$18000,'G-drive:Users:Reception:CACIQUE ID NUMBERS:[DO NOT COPY -K12 CACIQUE IDS.xls]Day Center Clients'!$A$2:$A$22000),2,0)

The Choose formula is a workaround, because VLOOKUP only flows left to right, and the workbook i'm referencing went right to left.


I would be most grateful if you could take a look at the file - i'm emailing it to you.

Comment by: Jan Karel Pieterse (7-6-2013 14:06:03) deeplink to this comment

Hi Jacob,

Ah, that explains things. Never saw that workaround before.
I'd advise to use INDEX and MATCH:

=INDEX(,'G-drive:Users:Reception:CACIQUE ID NUMBERS:[DO NOT COPY -K12 CACIQUE IDS.xls]Day Center Clients'!$A$2:$A$22000),MATCH(A8,'G-drive:Users:Reception:CACIQUE ID NUMBERS:[DO NOT COPY - CACIQUE IDS.xls]Day Center Clients'!$B$2:$B$18000,0))

Comment by: Jacob Miller (7-6-2013 20:06:13) deeplink to this comment

Switched all the formulas to index and match and the problem cleared right up. Thank you so much!!

Comment by: Jan Karel Pieterse (7-6-2013 21:14:00) deeplink to this comment

Hi Jacob,

Excellent!

Comment by: Kevin (19-6-2013 07:13:23) deeplink to this comment

Hi There,
In table reference, how would you specify a row instead of the current row? For example .. this =Table1[@Discount] uses the current target cell row number to find that item in the Discount Field/Column in the table. But what if I wanted a different row number .. say the the target row is 2 but I want row 4 from the Table?

Thx .. Kevin..

Comment by: Jan Karel Pieterse (19-6-2013 07:36:28) deeplink to this comment

Hi Kevin,

You can't do that using table references, you'd have to use a normal direct cell ref to do that. Simplest is to just click on the cell, if it isn't on the same row Excel will automatically adjust to a normal reference.

Comment by: sowmyalillyput@gmail.com (19-6-2013 14:13:05) deeplink to this comment

in excel how to remove default name column1 ,column2,if like this appears when we remove format table in excel please give me steps to remove default

Comment by: Jan Karel Pieterse (19-6-2013 15:48:42) deeplink to this comment

Hi sowmyalillyput,

Not sure what you need, but can't you just select the header cells and hit the Delete button?

Comment by: Asoka (24-6-2013 08:28:56) deeplink to this comment

Hi
This is a fantastic article. Pls tell me a way or protecting some columns of a table. When I lock the cells & protect the sheet the table feature goes off. Is there any way to protect the columns with formulas in an Excel Table?

Comment by: Jan Karel Pieterse (24-6-2013 13:35:13) deeplink to this comment

Hi Asoka,

You are correct, protecting a worksheet with a table disables quite some functionality of the Table.
If you really need the sheet to be protected, then the only way around this is to write VBA code that does the actions that you do want to allow the user to do.

Comment by: kirubakaran (25-6-2013 14:30:59) deeplink to this comment

Hi Pieterse,

I need a solution for tis if we work n excel we select any column and select the option means its automatically closed. we reinstall tat but it shows tat same error oly.if thr is any solution for tat means pls help us.

Comment by: Jan Karel Pieterse (25-6-2013 17:28:18) deeplink to this comment

Hi kirubakaran,

Check out this page, perhaps it helps:

https://jkp-ads.com/articles/corruptfiles.asp

Comment by: Daniel Rosenqvist (11-7-2013 20:01:31) deeplink to this comment

I work with Excel 2010 and the problem I'm having is that I have two tables with a common header.
I want to take out specific values from table1 and make an average of those values in table2.
The order of the names is not the same and with the table1[@col2] just puts the values in the same order as table1.
table1 includes all people and table2 includes a section of people from table1.

If someone knows how I could solve this it I would be greatful.

Comment by: Jan Karel Pieterse (11-7-2013 21:17:00) deeplink to this comment

Hi Daniel,

I kindly request you to ask this question at Eileens lounge:
www.eileenslounge.com

Comment by: Ed Grubbs (14-7-2013 22:02:18) deeplink to this comment

I still have a black and white printer. This will be great when I move up in equipment.

Comment by: Mandi Hanson (18-7-2013 18:35:48) deeplink to this comment

I have formatted cells within my table but the formatting does not follow through when I expand my table. Any thoughts?

Thanks

Comment by: Jan Karel Pieterse (8-8-2013 21:13:18) deeplink to this comment

Hi Mandy,

I think only the table style automatically expands.

Comment by: Pritu gupta (12-8-2013 18:45:48) deeplink to this comment

Hi,
I hope that it is only used in making report cards . It is because the usage of the tables cannot be written.

Comment by: Jan Karel Pieterse (13-8-2013 07:35:39) deeplink to this comment

Hi Pritu,

Can you please explain what you mean?

Comment by: Mathias Richard (16-8-2013 04:16:47) deeplink to this comment

I have 2 tables that refer to the same columns in tables in an external Excel worksheet with tabs for the tables (i.e., an Excel SS of datasource).

I link to that Excel datasource via an ODBC driver (Actual Technologies), and query refers to the tables very nicely in the designer, and SQL.

I can update each on the tables in my workbook, one at a time using refresh data; but, if I try just refresh on the data tab on the ribbon, excel crashes.

Any explanation, or work around?

I'd also like to know exactly which program is compiling the SQL so I can find out it's limitations, and deviations from the standard.

Thanks so much -- I've spent a whole day on this so far.

Comment by: Jan Karel Pieterse (16-8-2013 15:41:12) deeplink to this comment

Hi Mathias,

Perhaps the crash does not occur if you refer to the tables using range names?

Comment by: Mathias Richard (17-8-2013 03:29:14) deeplink to this comment

I checked, and both are simple range names.

The queries are on two woorksheets in the same workbook. The 2nd one is linked to a cell in the 1st via the Parameter setting. The 1st sheet's query has it's parameter set from a field outside the query range that receives input from the user -- a number entered into a cell. In effect two queries are linked, like thru a customer number. If the 1st one is updated, and then the 2nd one, then they stay in sync be the customer number.

This is all a test for a real application. I wrote a macro that updates the queries in order, and it runs just fine.

The big question is all about selecting "Refresh Data" on each works just fine; however, choosing "Refresh All" on the data banner, or the button on the banner crashes. BTW: the main menu only has Refresh in it; but, I think it used to include the All button.

Thanks...Matt

Comment by: Jan Karel Pieterse (17-8-2013 20:52:39) deeplink to this comment

Hi Matthias,

Hmm. I've not seen that one before, but perhaps this is a timing problem because the refreshes are done asynchronously and hence may be executed more or less at the same time.
You could of course add customUI to the file to change the behaviour of the refreshall button.

Comment by: Mathias Richard (19-8-2013 03:22:04) deeplink to this comment

I'd be happy if someone verified my findings. Should be pretty simple; 2 queries on same db on same sheet, press update on each, should work; now press the update all. Let me know it your crashes.

Comment by: Jan Karel Pieterse (19-8-2013 13:19:01) deeplink to this comment

Hi Matthias,

I have had this happen before, but only with Excel 2007 and with code that updates the CommandText of the queries and then refreshes them. The behaviour was so bad that I moved everything away from querytables (for that workbook) and used VBA (ADO) to write the recordsets to Excel.

Comment by: Kristine Goris (25-10-2013 16:08:00) deeplink to this comment

Hi,

How do I change de order of de columns once I've created the table?

Thx,
Kristine

Comment by: Jan Karel Pieterse (25-10-2013 16:55:06) deeplink to this comment

Hi Kristine,

You can simply cut a column and then right-click on the column immediately to the *Right* of where you want to move that column to and select "Insert cut cells".

Comment by: Per Inge (21-11-2013 18:04:57) deeplink to this comment

We are using tables in Excel a lot and we like the functionality. For relatively small tables it works great, but when the number of rows increases the time it takes to populate the table (programatically with C# code) increases quite a lot.

Are there some size limits where tables are not practical to use ?
Are there any calculations or update features that we can switch off during the population to speed thing up ?

PS JKP. Thanks for so many great postings on the net

Comment by: Jan Karel Pieterse (22-11-2013 11:20:47) deeplink to this comment

Hi Per,

One thing you can try is to add the information below the table whilst leaving one blank row. Then after adding the data, delete the row and change the size of the table. Not sure if it will work, but it is worth the try.

Comment by: Kris Wright (5-2-2014 15:01:20) deeplink to this comment

Hi

I am trying to create a new pivot table in a new workbook who's Data Source is a table in the workbook, which contains the VBA code.

I can create a Pivot Table as you describe above, but it will only refresh when the source workbook is open.

Is there any setting that needs to be altered to allow refresh when source is closed?

Thanks

Kris

Comment by: Jan Karel Pieterse (5-2-2014 19:25:39) deeplink to this comment

Hi Kris,

Perhaps if you use MSQuery to tie the pivot table to the data rather than direcly to the Excel table?

Comment by: Carsten (26-2-2014 13:33:26) deeplink to this comment

I'm trying to use the TIME function in a table:

=TIME([@[*Start_hh]];[@[*Start_mm]];[@[*Start_ss]])

It returns zero (0). When I use the function outside the table it works just fine, so the question is, does anyone know if the TIME function works within a table?

Thank you
Carsten

Comment by: Carsten (26-2-2014 13:35:27) deeplink to this comment

Just posted a question about the TIME function. I forgot to mention that I use Excel 2010.

Comment by: Jan Karel Pieterse (26-2-2014 16:22:11) deeplink to this comment

Hi Karsten,

Works fine for me.

Comment by: Mitchell (28-2-2014 02:04:13) deeplink to this comment

Can I protect the data in 2010 Excel tables, but still give users the ability to sort and filter the tables?

Comment by: Jan Karel Pieterse (28-2-2014 14:09:37) deeplink to this comment

Hi Mitchell,

Odd enough, if you protect the sheet with the Sort and Autofilter boxes checked, filtering is allowed, but sorting isn't. (Excel 2010). Must be a bug.

Comment by: Zack Barresse (11-3-2014 09:15:49) deeplink to this comment

Oddly enough, if the table data body range cells have the locked protection property set to false (not locked) you will be able to sort on a protected worksheet. I'm with Jan though, I don't think this should be 'normal' behavior, although I doubt we'll get it fixed.

Please note you MUST ALSO unprotect the header row. No need to worry about the total row, but it won't work if the header cells are locked also.

Comment by: Jan Karel Pieterse (11-3-2014 10:38:03) deeplink to this comment

Hi Zack,

Thanks, that is good to know!

Comment by: Zack Barresse (11-3-2014 10:40:36) deeplink to this comment

Very welcome. :) I should also mention this has the same behavior in 2010 as well as 2013, even after SP1 (haven't tested 2007).

Comment by: Todd (3-4-2014 13:20:58) deeplink to this comment

LOVE THE ARTICLE!

My question is this... If I do the format as table option for my table, I lose data when a column is sorted. Example: In Column C I have my name TODD and in Column D I also have my name TODD. If I select the sort on column C to be TODD, I lose the ability to sort column D with TODD.

Comment by: Jan Karel Pieterse (3-4-2014 13:21:52) deeplink to this comment

Hi Todd,

Thanks!

You cannot have two columns with the same name in a table unfortunately.

Comment by: Jim (7-4-2014 19:20:18) deeplink to this comment

In Excel 2010, am trying to make single & 2-value data tables using what-if.. Follow tutorials, but all values are the same in the tables---either across or down rows. What could I be doing wrong? Thanks!

Comment by: Jan Karel Pieterse (9-4-2014 07:24:07) deeplink to this comment

Hi Jim,

I suggest to go to www.eileenslounge.com to ask your question, more people are there to look at your problem.

Comment by: Peggy (28-4-2014 18:52:13) deeplink to this comment

How do you "un-do" the table? I know how to un-do the table style but I want to restore the data to pre-table status.

Comment by: morgan harding (29-4-2014 15:21:22) deeplink to this comment

Each 'page' in a spreadsheet is called a _____.

what is the answer

Comment by: Jan Karel Pieterse (8-5-2014 07:47:51) deeplink to this comment

Hi Morgan,

A worksheet?

Comment by: Jan Karel Pieterse (8-5-2014 07:55:10) deeplink to this comment

Hi Peggy,

Use the Convert to Range button on the Table tools tab of the ribbon.

Comment by: Robert, Plumstead (5-6-2014 12:54:46) deeplink to this comment

Hi Jan
I have: excel 2013, a worksheet with many rows and cols of data. I have changed the data area into a table = after selecting the entire range (and reverted back to a range then table etc many times - I am ok with this working correctly). I have selected a table style - while the cursor covers the entire table = heading row changes colour as expected. With cursor in the table I selected check box "banded rows (and unselected) But no banded rows appear (ditto banded columns - no banding). Question: what could be going wrong?
Thanks
Robert

Comment by: Jan Karel Pieterse (5-6-2014 14:36:43) deeplink to this comment

Hi Robert,

All I can think of is that your table has a table style applied that has no alternating row coloring.

Comment by: ronald omegna (21-7-2014 13:41:34) deeplink to this comment

jan,
excellent overview.
can i take advantage of tables in the following way?

1. column 1 of the table includes unique key field
2. column 2-10 of the table use lookups to a db and do calculations specific to the unique key field
3. each row is driven from the key field in column 1 of the specific row
so then:
4. suppose i want to refresh the table weekly?
5. and the refresh is simply importing to the table a new list of key fields in column 1 [there may be some new additional key fields and some existing key fields may no longer be in the import

will the table expand/contract based on the import of key fields in column 1? [i think yes? correct?]
since the remaining columns are formula driven from the values in column 1, will the table re-generate itself when the import is done? [i'm not sure on this?]
can table features do this? or do i need some code to walk through each column 1 key field and force the remaining columns to re-calculate?

i get how powerful the table concept is, i'm just not sure if what i am asking is outside its' feature set.

thanks,
ron

Comment by: Jan Karel Pieterse (21-7-2014 20:56:09) deeplink to this comment

Hi Ronald,

Yes you should be able to do that. If your keyfield is -for example- tied to a database connection, refresshing the connection will automatically expand or contract the table including formulas.

Comment by: Darren (17-8-2014 00:07:00) deeplink to this comment

How do I lock the table on my work sheet so no one can change it?

Comment by: Jan Karel Pieterse (17-8-2014 11:11:03) deeplink to this comment

Hi Darren,

By protecting the worksheet itself. Review tab, Protect sheet.

Comment by: Ray (16-9-2014 22:09:16) deeplink to this comment

Hello,

I have a workbook with multiple worksheets(individual salesperson commission by customer ISCC)which calculate off a single worksheet(gross profit by customer GPC). I set up a data range in GPC, and in each individual sheet used a data import from other sources/connection wizard/OBDC DSN/Excel Files/range name. I can then update GPC, including adding rows, and when I select Refresh All, my ISCC sheets are updated perfectly. The problem is I need to calculate commissions monthly, as well as creating/updating quarterly and yearly forecasts. When I rename the file to create one of the new files, all data updates continue to be attached to the original file. I cant seem to figure out how to have the data stay with the renamed file. Thank you.

Comment by: Jan Karel Pieterse (17-9-2014 09:23:56) deeplink to this comment

Hi Ray,

You should be able to change the sourcefile from the Data tab of the ribbon, "Connections" button. Then click the properties button for each connection in turn and look at the Definition tab. Make your changes there.

Comment by: Ray (17-9-2014 16:48:00) deeplink to this comment

Thanks Jan,

That work's but now I realize maybe I should be handling this data differently since I'd be constantly updating the ISCC sheets in multiple workbooks. As mentioned the issue is that I have actual commission data, which believe it or not most times changes right up to the due date, I have quarterly forecasts , which I actualize each month, and yearly forecasts, which are actualized as well. Any suggestions on how to effectively compartmentalize data so it travels with the appropriate workbook? Thanks,

Ray

Comment by: Jan Karel Pieterse (18-9-2014 07:16:03) deeplink to this comment

Hi Ray,

Depends. Do the old copies really need to stay "connected" to their sources, or is it OK for them to become static? In that case, you can keep using the same set of files which shows the most up-to-date information and save copies at times you need a snapshot. In these copies you just remove the connections.

Comment by: Ray (18-9-2014 14:53:21) deeplink to this comment

Hi Jan,

Yes the files need to stay connected, at least until the point where I can make them final, and then remove the connections. The issue is that I'm doing the actuals, quarterly and yearly forecasts simultaneously. I guess I can set up a template for each but would like to avoid duplicity if I could.

Ray

Comment by: Jan Karel Pieterse (19-9-2014 12:11:45) deeplink to this comment

Hi Ray,

I guess I fail to understand how exactly you work with those connections.

Comment by: Ray (19-9-2014 13:36:38) deeplink to this comment

Hi Jan,

No problem, I've decided to make an individual template for each commission type.

Ray

Comment by: Robert Galik (7-10-2014 12:51:51) deeplink to this comment

I am using a table that continually expands, requiring 5-10 additional rows per week. I needed to protect the worksheet to preserve several formulas, and the ability to tab from the last active cell and create a new row disappeared. I believe this is due to the sheet protection. I recorded a macro that first unprotects the sheet, adds 10 rows to the table, sorts the table by date, the turns protection back on. I was pleased it worked as well as it did, but even with the new (blank) rows created, when I tab forward from the last cell in the last column, instead of wrapping, the active cell leaves the table to the right. (There is no data adjacent to the table)
Thanks for any suggestions

Comment by: Jan Karel Pieterse (8-10-2014 10:28:18) deeplink to this comment

Hi Robert,

If you protect the sheet and disallow selecting of locked cells and unlock the appropriate cells in the table, you should stay in the table.

Comment by: Robert Galik (8-10-2014 19:03:30) deeplink to this comment

With the sheet protected and the column adjacent to my table locked, when I tab from within the table, I still exit the table. I cannot enter data in the new active cell because of the protection. I am still stumped, but thank your for your suggestion.

Comment by: Jan Karel Pieterse (9-10-2014 09:45:48) deeplink to this comment

Hi Robert,

When protecting the worksheet, do you ensure locked cells cannot be selected? That is key to make this work.

Comment by: Robert Galik (10-10-2014 03:44:58) deeplink to this comment

Through trial and error, the table is working properly if both 'users can select unprotected cells' and 'users can select protected cells' are checked in the protect worksheet dialog box. It may be off topic, but I had a second issue. After running my macro which unprotects, add rows, sorts by date and reprotects, I am always returned to the top of the table, when I would prefer to be in the first empty cell at the bottom. Can you explain how to accomplish this?

Comment by: Jan Karel Pieterse (10-10-2014 17:06:18) deeplink to this comment

Hi Robert,

Code like this selects the last row of the table:

Comment by: Quyen Nguyen (21-10-2014 09:18:48) deeplink to this comment

Hello,
Could anyone help me? I want to convert a table, like a first table, to other type, like the second one.
Thanks!

Table 1
DG A B C D
1 500 460 450 420
2 450 440 430 410
...

table 2
A1 500
A2 450
B1 460
B2 440
C1 450
C2 430
D1 420
D2 410
...

Comment by: Jan Karel Pieterse (21-10-2014 15:38:33) deeplink to this comment

Hi Quyen,

This is what is called "unpivoting" your data.
- Right-click the quick access toolbar and select "Customize quick access toolbar".
- in the "Choose Commands from" dropdown, select "Commands not in the ribbon".
- Find the entry "PivotTable and PivotChart Wizard
- Click the Add button
- Click OK

- Click the new Pivottable wizard button
- Select "Multiple consolidation ranges"
- Click Next
- Select "I will create the page fields"
- Click Next
- Select your data
- Click Add
- Click Finish
- Double-click the grand total of the newly inserted pivottable.

Comment by: Quyen Nguyen (23-10-2014 16:07:15) deeplink to this comment

It works perfectly :D. Thank you very much Jan Karel Pieterse!

Comment by: Brian (2-12-2014 19:02:16) deeplink to this comment

I am trying to use the table syntax to map data from one table to another table. My goal is to aggregate data from tables on multiple worksheets within the same workbook.

Table 1
Name    Date     Amount
Car     11/15/14 $50.00

I then want to map rows from the "Name" column in Table 1 to a Name column in Table 2. I have tried several different options but always end up with the #VALUE! error.

I have tried:
=Table1[Name]
=Table1[@Name]
=Table1[@[Name]]
=Table[[#Data][Name]]
=Table[[#Data],[Name]]

Is there anyway for me to accomplish this and make it work?
Brian

Comment by: Jan Karel Pieterse (3-12-2014 06:27:17) deeplink to this comment

Hi Brian,

In theory the second syntax looks OK, but note that it will pull the data from the same row as the table itself. So if your formula is on cell B3, the result will come from the data on row 3 of the sheet the table is on.
As soon as your formula row is a row that is outside the table rows, #VALUE! will appear.

Comment by: Pedro (5-12-2014 18:24:35) deeplink to this comment

Hi.

I'm using this functionality but i would like to know if it possible the following:

I have Table2 on a different sheet, that is for the first 3 columns, depending on table1. What i would like to have is every time i add more lines on table1, it automatically will update the rage of lines for table 2, without me having to go change the range of table2.
Any sugestions?

Many thanks in advance.
Pedro

Comment by: BRUNO HECQUARD (5-12-2014 19:43:59) deeplink to this comment

Hi Brian,

if you want cell 2 in column Name of tblTarget=content of cell 2 in column Name of tblSource, you will enter =tblSource[@Name] in line 2 of column name of tblTarget.

Another option is to go to the target cell and type = plus click the source cell.

The column will automatically be filled in with the above formula.

Tables may be in different sheets. It is advised to got to the table menu and give a suitable name to each table.

I usually uses the tbl prefix, and then the name of what's in the table starting with a capital letter.

Goof luck with tables, a wonderful tool.

Comment by: Jan Karel Pieterse (8-12-2014 08:38:31) deeplink to this comment

Hi Pedro,

That would require some VBA code, either tied to a button you would click or to a worksheet_Change event. Why do you need this table twice?

Comment by: Pedro (8-12-2014 22:34:13) deeplink to this comment

Thanks for your feedback Jan.

The main issue is that i need to keep the order of lines on both tables. Ex: Table 1 is vendor/quantities sold, table 2 is Vendor/ amount of sales.
The purpose is to every time i add a vendor and the quantities sold, the table of amount of sales automatically updates the range.
I need to work independently the data concerning quantities and amounts of sales.

Any suggestion on the VBA code for that button?
Thanks in advance.
Pedro

Comment by: Jan Karel Pieterse (9-12-2014 09:42:23) deeplink to this comment

Hi Pedro,

Seems to me your second worksheet should simply be a pivot tabled based off of the first, summing the sales numbers on the first sheet, grouped per Vendor.

Comment by: Pedro Cerca (11-12-2014 12:10:11) deeplink to this comment

Hello

I'm facing a problem using sumif at the same time as left function.
Resuming, i have three tables, where for both column A is contract. Table 1 is ammount of sales, table 2 is quantities the area occupied for generating such sales, and table 3 need to do the calculation Sales/Area.

The problem is that some contract might have some exceptions and therefore, either for sales and area they are treated independently, but for table 3 they such be considered as the total ammount for the area occupied.

Example:
Table1 - Sales
Contract|Sales
1000 2500
1000_1 3000

Table2 - Area
Contract|Area
1000 800m2
1000_1 0

Table3 - Sales per Area
Contract|SalesArea
1000 Should be (2500+3000) / 800m2

I was tryng to achieve the result by using (sumif(table1Contract;left(table3contract;4);Table1Sales)/(sumif(table2Contract;left(table3contract;Table2Area) but i'm not getting the desired result.

I looked on the forum and they're recomending sumifs/sumproduct but i did not get how to use it.

Either 3 tables are to be expanded, so i would need something that was not "hardcoded" for a string of contract.

Can anyone help me?

Many thanks in advance
Pedro

Comment by: Jan Karel Pieterse (12-12-2014 06:43:09) deeplink to this comment

Hi Pedro,

You could do it like this:

(sumif(table1Contract;table3contract&"*";Table1Sales)/(sumif(table2Contract;table3contract&"*";Table2Area)

Comment by: Jeroen Burgmans (8-1-2015 08:46:33) deeplink to this comment

I have a vertical lookup function in a sheet (file A) referring to a table in another file (file B). When I open file A the vertical lookup function cannot find a value. Only upon opening B the formula's are updated. When converting the table to a normal range the values are updated offline. It seems to me this should also be the case with tables. What is going wrong?

Comment by: Jan Karel Pieterse (8-1-2015 13:15:37) deeplink to this comment

Hi Jeroen,

Table references indeed not always work as expected when the table is in another file.
The way around this is to define a range name in the sourcefile that spans the table's data. Then use that rangename in the VLOOKUP function. By using the range name you ensure the VLOOKUP always uses the right range of cells.

Comment by: Rene DR (23-1-2015 19:14:59) deeplink to this comment

Hi Jan,

Thanks for the Post to Nguyen about "unpivoting". In the past, I have done this using a complicated series of operations, and I can't believe "unpivoting" is this easy to do!

I have a very annoying problem with Excel 2013. I use tables on different sheets as source data for several Pivot Tables for many years, but recently, whenever I create a Pivot Table, the Data Source path of the Pivot Table changes to an absolute path.

Then if I save the file with a new name, the Data Source still uses the old Absolute path so refresh does not work unless I open the original file. I am then forced to edit all the Absolute paths for the data for over a dozen pivot tables I have in my Excel file.

Same thing happens if I email the file. The absolute file is embedded in the sent email file so If save to a different subdirectory, the Pivot refresh also doesn't work.

Excel 2010 does not have the problem. Lately, I have been editing my Excel 2013 files in Excel 2010 if I want to save the file under a different name.

Please help.

Thanks,

Rene

Comment by: Jan Karel Pieterse (27-1-2015 07:33:22) deeplink to this comment

Hi Rene,

That seems to be a know problem with Excel 2013 indeed:

https://social.technet.microsoft.com/Forums/office/en-US/43bf5110-dfad-40e5-a71c-e9736da6fbc2/data-source-path-in-pivot-table-changes-to-absolute-on-its-own?forum=excel

Comment by: Lisa Green (7-2-2015 16:51:36) deeplink to this comment

Hi Jan Karel... Please don't get annoyed!!!
I've spotted a typo on this page I think...

https://jkp-ads.com/Articles/Excel2007Tables.asp


Referring to a table from another workbook

Even tough I mentioned that a table is also stored as a range name there is a peculiarity.

I think that "tough" should read "though".

Hugs
Lisa

Comment by: Jan Karel Pieterse (7-2-2015 18:20:41) deeplink to this comment

Hi Lisa,

Thanks!

Comment by: Ian (9-3-2015 20:53:41) deeplink to this comment

I am using tables in a customer data submission form. At the right side I have some validation columns to make sure that if the customer copies and pastes data over the in-cell validation that I can capture the error and identify it.

In one of the tables the table functionality works great - if the customer inserts rows the formulas are copied into the new cells. In another table the formulas aren't copied at all and in another old versions of formulas are copied into the new rows. Can anyone explain how this works and how I can configure it to copy the current formulas every time

Thanks

Comment by: Jan Karel Pieterse (10-3-2015 11:32:37) deeplink to this comment

Hi Ian,

Excel stores a copy of the formula in a hidden place (the user interface does not give any way to access that hidden place). However, if you select all the "data" cells (meaning everything except the header row) in a column and update the formula, Excel should overwrite this hidden formula with the new one.

If any cell in a column has a different formula (or no formula), the automatic insertion of the formula no longer works. Excel does not like any exceptions in a column.

Comment by: Zack Barresse (16-3-2015 19:54:17) deeplink to this comment

Ian, to further Jan's comment, take a look at a blog post I wrote on how Table formulas are remembered: http://exceltables.com/remembering-table-formulas/

They're not directly accessible, but it's a way to tell if a formula in a Table column is truly a "calculated column" or just formulas in cells. A true calculated column formula will be in the XML of the [Table XML] file.

Comment by: Andrew Garlutzo (13-4-2015 19:53:39) deeplink to this comment

Hi Jan,

I am trying to copy information from one document formatted as a table into the next. The lookup formulas retrieve the data for every row, but when I attempt a Copy/Paste Values the in the destination spreadsheet, the table removes one row of data every time. I've unprotected the table, and expanded the number of rows in the table, and still no luck. Is there a way to correct this?

Thanks,
Andrew

Comment by: Jan Karel Pieterse (13-4-2015 23:40:05) deeplink to this comment

Hi Andrew,

Hmm, can you give a bit more detail, I don't think I quite grasp what you're doing.

Comment by: Kalu singh kharte (3-5-2015 18:15:49) deeplink to this comment

How to design excel sheet in plane paper

Comment by: Jan Karel Pieterse (4-5-2015 12:00:05) deeplink to this comment

Hi Kalu singh kharte,

I would like to advise you to go here to ask your questions:

www.eileenslounge.com

Comment by: Chris (4-6-2015 18:20:03) deeplink to this comment

I'm trying to use a Table as a source for a Pivot table in another workbook. I can set it up okay but the Pivot table cannot be refreshed unless the source book is open. However if I were to set this up with a named range rather than a table as the source it works fine, any ideas?

Comment by: Jan Karel Pieterse (4-6-2015 18:27:10) deeplink to this comment

Hi Chris,

This is unfortunately a limitation of tables. What solves this is defining a name that spans the entire table. The name will expand with the size of the table.

Comment by: Stan (10-6-2015 23:02:40) deeplink to this comment

4. Automatic expansion of table
If you type anything next to a table, Excel assumes you want to expand the table and automatically increases the table size to include your new entry. Of course you can undo this expansion too, or switch off this behavior entirely.

Typing next to the last column and inserting a header will not add a column to my table.
Where can I switch this behavior on/off.

Thanks

Comment by: Jan Karel Pieterse (11-6-2015 11:44:32) deeplink to this comment

Hi Stan,

I'm not sure I follow?
The option to expand tables is in:
File, Options, Proofing, Autoformat as you type, see "Apply as you work"

Comment by: Emily (15-6-2015 15:01:01) deeplink to this comment

Hi there,
I've had some recent trouble with cell referencing after a sort/filter and hope you can help me.

It appears that after some combination of filtering and sorting, my cell references within my table have changed.

For example, Column A is a date and Column B is the month of that date, using the =month() formula. After a filter or sort on my table, it appears that the formulas in Column B are referencing the date a few rows below (not the same row, as before).

Can you help me to understand why this is happening?
Thanks,
Emily

Comment by: Jan Karel Pieterse (15-6-2015 16:19:44) deeplink to this comment

Hi Emily,

I've heard that happening when formulas contain mixed absolute and relative cell references (with and/or without $). Another possible cause might be a reference to another sheet within the formula.

Other than that, I don't really know!

Comment by: Evans (7-9-2015 13:12:09) deeplink to this comment

quite helpful thanks

Comment by: Danny SAville (29-10-2015 08:42:56) deeplink to this comment

I found that I can not paste values in cells within a Data Table- whether they are copied from within the table or outside it (although the cells in the Data Table have NO foumulas!).
I hope someone has an answer to this (I’ve been searching for a long time).

Comment by: Jan Karel Pieterse (29-10-2015 10:13:55) deeplink to this comment

Hi Danny,

Check out this answers post, it may contain the cause of your problem:

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/paste-special-in-excel-2010-bug/24e84564-4e65-4190-abf7-94bec9b32a78

Comment by: Brian Canes (16-12-2015 23:17:38) deeplink to this comment

It seems that certain featurenalities are not available in Tables. For example
"Multi-cell array formulas are not allowed in tables"
"You cannot copy or move a group of sheets that contain a table."
and no absolute referencing, etc., etc.,
Do you know of a comprehensive list of such limitations relative to Excel Tables and their usage?
Regards
Brian

Comment by: stephan (18-12-2015 00:55:14) deeplink to this comment

the most powerful feature of tables is that their source can be an external database, and yet they can be customized within Excel, eg. with additional columns, or fewer columns (yes you can delete columns you don't need). I set up bi-directional feeds where you update the database via their tables in excel (i.e. link Access's update query(s) to its own select query data in an Excel table. You can run the update queries from Excel, then execute a refresh to see your updates are now captured)

Comment by: Jan Karel Pieterse (18-12-2015 11:51:12) deeplink to this comment

Hi Stefan,

Can you share a bit more about how you made the table updateable?

Comment by: WILLIAM FREUND (18-10-2016 18:38:00) deeplink to this comment

The names of these 'tables' DO appear in the "Name Box" and in Excel's built-in 'Name Manager' [Formulas tab; Defined names group] not terribly useful.

These table names DO NOT show up when I press [F3] when building a formula in which I wish to include a reference to a table. I am using Excel 365 ProPlus 64-bit running under Windows 7 HP.

I tried using the latest version of your excellent Add-in "Name Manager 2007.xlam" (Ver 4.3, Build 652) but received the message "No names in active workbook". Will this omission be addressed in the near future?

Thank you,
Bill Freund

Comment by: Jan Karel Pieterse (19-10-2016 08:13:11) deeplink to this comment

Hi Bill,

Tablenames are only shown in NameManager as part of FastExcel: http://sites.fastspring.com/decisionmodels/product/fastexcelspeedtoolsproducts?source=jkpads

Comment by: zhi (8-12-2016 20:56:08) deeplink to this comment

Can you batch update an excel table using VBA or SQL?

e.g., I want to update all values in the [email] column of which contains "@" to "email:" & [cell value]. How to do that?

Thanks!

Comment by: Jan Karel Pieterse (9-12-2016 18:47:51) deeplink to this comment

Hi Zhi,

You can add a column and use a formula like this perhaps:

=IF(ISERROR(FIND("@",[@email])),"","email:")&[@email]

Comment by: Warren Dickson (28-7-2017 20:31:56) deeplink to this comment

When I add columns to a table, those columns are not available for a pivot table - EVEN if I re-select the table and begin a NEW pivot. In order to get the new columns to show in the pivot table I must convert the table to a range (Design tab) and THEN create the pivot table from the range.

Can you explain why? It seems that tables are stored as an object in their original version and adding columns does not add them to that object. Does that make sense?

Comment by: Jan Karel Pieterse (13-8-2017 17:13:32) deeplink to this comment

Hi Warren,

That is not my experience, as long as the pivot source is the name of the table, NOT the cell address.

Comment by: Mithun Nair (20-8-2017 18:08:02) deeplink to this comment

Warren - Just need to Refresh the pivot table after adding the column in the named table, it will be available. Right click the pivot table -> Refresh.

Comment by: Rabin Das (7-10-2017 07:40:57) deeplink to this comment

Excellent .
I use excel applications in my profession very much , I want to learn many more things related to various Excel Applications .
I will first go through some of the Excel Applications you nicely described and then I will come back to you if I face any problems to understand properly .

regards
Rabin Das

Comment by: Gerard Leuchter (29-11-2018 11:31:14) deeplink to this comment

You are probably aware that tables "remember" the first entered formula in a column. Even if this formula doesn't exist anymore in any cell of the column. When inserting a new row this "original" formula will be present in these new cells. With sometimes disastrous results.
I found references to change this in XLM but wouldn't this be a swell topic to add to your table tool?

Comment by: Jan Karel Pieterse (29-11-2018 11:58:59) deeplink to this comment

Hi Gerard,

Yes I'm aware of that problem. I have already written some rudimentary code to check tables for formula inconsistencies, but it isn't ready for release yet.

Comment by: Lukas (12-12-2018 02:44:42) deeplink to this comment

Tables in Excel 2007 are the successor of Excel 2003's "List" feature, with added functionality.

Comment by: Jeff C (2-8-2019 12:53:00) deeplink to this comment

What a wonderful approach to the problem. Touch the header, shoot to the bottom; one row below, back to the top! I never would have thought of that. It works perfectly. Thank you so much!!

One small thing, when I press Ctrl-A to select the whole table so I can copy it, the whole data area changes color. If there's a simple fix for that it would be great but I can certainly work around it. Thanks again!

Comment by: Del Simcox (2-2-2020 22:42:00) deeplink to this comment

There must be a syntax to get the Max or Min values in a table column - but I'm unable to get it to work on Excel (Office 365). Can you help please?

Comment by: Jan Karel Pieterse (4-2-2020 11:21:00) deeplink to this comment

Hi Dell,

You mean using a formula? Just type =MIN( and then select all cells in a single column of the table, Excel should use the correct syntax automatically. Something like

Comment by: Hasan (22-2-2021 13:01:00) deeplink to this comment

I prepared an INVENTORY MANAGEMENT SYSTEM in excel but i want that a person cant able to enter more sales than purchases, means
On Feb 20, 2021 Purchase 50 pcs of rose
On Feb 22, 2021 sale 51 pcs of rose (there should be a restriction but i am unable to do it)
So kindly guide me that how can i restrict because if a have 50 roses so it will take time to sold, so sometimes some clients will take 5 or some will take 10 roses. I did use data validation but not any good result to restrict the sales not more than purchases.

Comment by: Jan Karel Pieterse (23-2-2021 10:19:00) deeplink to this comment

Hi Hasan,

I would suggest to look at the Data, Validation options. You can set a maximum value for the sales cell which you can depend on another cell's value (the purchase cell)

Comment by: gerdami (2-8-2021 16:55:00) deeplink to this comment

Hi Jan Karel, I get this error message whenever I want to create a table from a range:

VBA compile error, variable not defined: tbxAddress.
in Private Sub UserForm_Initialize()

Table Tools 1.0 build 008
Excel for Msft 365 MSO (16.0.13...) 64-bit

Best,
gerdami

Comment by: Jan Karel Pieterse (4-8-2021 10:54:00) deeplink to this comment

Hi gerdami,

Which version are you using (please click the About button)?
I do not get that particular error on my system.

Comment by: Kedar Kulkarni (25-8-2021 19:37:00) deeplink to this comment

Great utility as always. I get one runtime error when I just open excel and no open workbook and switch to the table tools tab. (it should be consistently reproducible).

runtime error 1004 - it is in the following method because there is no active workbook. We might want to check for an active workbook before accessing the worksheets.


the row for error is
For Each oSh In Worksheets

You already know the answer but just for others -
we should just add below after Public Function getTableCount() As Long
If ActiveWorkbook Is Nothing Then Exit Function

Comment by: Jan Karel Pieterse (25-8-2021 20:27:00) deeplink to this comment

Hi Kedar,

Thanks for letting me know!

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.