+ Reply to Thread
Results 1 to 21 of 21

How do I delete a {=Table(A1)} function

  1. #1
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,470

    How do I delete a {=Table(A1)} function

    Hi,

    I've inherited a 2003 workbook in which someone has used the {=Table(A1)} function, which is presumably addressing a range somewhere.

    Excel won't let me delete it because it's part of a data table.

    If I'm in 2007, how do I delete this function, or at least discover the range on which it's based so that I can get rid of that range and then presumably delete the formula?

    Must admit this =Table() function is not one I've used much.

    Usual TIA
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  2. #2
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: How do I delete a {=Table(A1)} function

    Hi Richard

    Because it's an array, you need to select all elements of the array and then you should be able to delete them all in one go using the delete key.

    Richard
    Richard Schollar
    Microsoft MVP - Excel

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: How do I delete a {=Table(A1)} function

    If you select that cell and do CSE, it will select all elements of the array. Then you can delete.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: How do I delete a {=Table(A1)} function

    shg, that didn't work for me... I had created a quick sample to see the best way to handle Richard B's request... and of course, Richard S beat me to it...

    So I tried what you said and it didn't work.. just gives error "That function is not valid"... in both 2003 and 2007.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,470

    Re: How do I delete a {=Table(A1)} function

    In case it's of any relevance, if I put the cursor in the formula edit bar, the name box shows 'AVERAGEIF'.

    I thought at first that this might be an XL name which I could delete, but it's neither a viewable name nor an =OFFSET(.... defined type of name. Seems like there must be an =AVERAGEIF formula somewhere in the relevant part of the table but I've not managed to discover it yet.

    Regards

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: How do I delete a {=Table(A1)} function

    If this was a 2003 sheet you opened in 2007, then AVERAGEIF would not have been a function available (unless it was a UDF)...

    Any chance of seeing an attachment?

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: How do I delete a {=Table(A1)} function

    shg, that didn't work for me
    I confess, I did not test ...

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,470

    Re: How do I delete a {=Table(A1)} function

    Hi ,

    Good point re the AVERAGEIF which hadn't struck me. However I've checked the original 2003 version and there's no UDF - which doesn't surprise me because this would have been outside the users area of competency. If it is associated with the genuine AVERAGEIF() function I can only assume the user at some stage has changed the original table 'base' cell - wherever that may be.

    I'm attaching an example created in XL2003 The Data Table was created after selecting B6:C9. Note how you can't delete the C7:C9 {=TABLE(,B6)}

    Nor, as far as I can see, how you can elicit the original range from the information given.


    Addendum. Even though this example uses a PMT() function, when opened in 2007 the name box shows AVERAGEIF when cursor is in the formula bar. Which seems to suggest that it's not anything to do with a pukka AVERAGIF() function - weird...

    Regards
    Attached Files Attached Files
    Last edited by Richard Buttrey; 04-21-2010 at 02:35 PM.

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: How do I delete a {=Table(A1)} function

    Richard,

    I was able to delete the contents of C6:C9 following Richard S's procedure above.

    I don't see any evidence of this AVERAGEIF...
    Attached Files Attached Files

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: How do I delete a {=Table(A1)} function

    I just saw your edit... and I opened it in 2007 and no AVERAGEIF was shown in the Name box after selecting E6 or C6

  11. #11
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: How do I delete a {=Table(A1)} function

    Kinda off-topic, but does anyone actually find much use for the Data Table feature of Excel (note, I'm not talking about xl2007's tables which are completely different)? The only time I've come across them was when someone was using some actuarial tables but that was an awfully long time ago. Are they vastly more efficient than simply replicating the formula in each cell?

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: How do I delete a {=Table(A1)} function

    Does 2007 or 2010 have this feature anymore.?.. I am not even sure.

    I have never used the feature... and had to go and search it out and do tests even to help try this post.. it took me a bit to figure it out...

  13. #13
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,470

    Re: How do I delete a {=Table(A1)} function

    Hi,

    I think I may have misled you. I meant to say that if the active cell is in any of the C6:C9 cells, when you then click in the formula edit bar I see AVERAGIF in the name box.

    I can indeed see how in this example the whole thing can be deleted by selecting B6:C9 and deleting the lot. However in the real world workbook I can't detect which are the column or row cells that drive the table (i.e. column B in this example). Could it be possible they're on another sheet perhaps. There seems no way of detecting the original selection that was made when creating the {TABLE}

    Regards

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: How do I delete a {=Table(A1)} function

    Richard,

    I have not been able to figure out or find anywhere on the net, how you would determine the source data...

    I assume in most cases it would be adjacent to the place where the TABLE() function or event the cell reference in the table should give a clue as to whereabouts it is..

    Other than that, I cannot duplicate your scenario to the point that I can help solve it.

  15. #15
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How do I delete a {=Table(A1)} function

    Quote Originally Posted by Richard Buttrey View Post
    Addendum. Even though this example uses a PMT() function, when opened in 2007 the name box shows AVERAGEIF when cursor is in the formula bar. Which seems to suggest that it's not anything to do with a pukka AVERAGIF() function - weird...
    That dropdown shows the most recently used functions. The AVERAGEIF has nothing to do with your table.

    The table formula refers to the top cell in a 1-parameter table or the cell in the top left corner of the table for a 2 parameter table.

    They have their uses but they are quite calculation intensive (which is why you have the option to turn of table recalc).
    Remember what the dormouse said
    Feed your head

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: How do I delete a {=Table(A1)} function

    Quote Originally Posted by romperstomper View Post
    That dropdown shows the most recently used functions. The AVERAGEIF has nothing to do with your table.
    Maybe a stupid question (it is early morning here in my neck of the woods)... which is this dropdown list?

    Then Name box dropdown doesn't show recent functions used..as far as I can see.... I am sure I am being oblivious to something more obvious...

  17. #17
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How do I delete a {=Table(A1)} function

    It does if you activate the formula bar first (ie edit formula mode where formula present)

  18. #18
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How do I delete a {=Table(A1)} function

    It only does it if you select a cell that contains a formula and then enter edit mode.

  19. #19
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: How do I delete a {=Table(A1)} function

    D'oh! I knew that.. just got confused there for a second...

    When I did it in his sample attachment, it said TEXT and it went over my head that this meant the TEXT function... I am losing it ....

    Thanks guys
    Last edited by NBVC; 04-22-2010 at 08:51 AM.

  20. #20
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: How do I delete a {=Table(A1)} function

    Quote Originally Posted by Richard
    does anyone actually find much use for the Data Table feature of Excel
    What's neat about data tables, Richard, is that you can have an arbitrarily complex model (maybe spread over several sheets), and then tabulate results for one or two varying input variables. It's one of those things you don't need very often, but when you do, it's very handy.

  21. #21
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: How do I delete a {=Table(A1)} function

    Quote Originally Posted by shg View Post
    What's neat about data tables, Richard, is that you can have an arbitrarily complex model (maybe spread over several sheets), and then tabulate results for one or two varying input variables. It's one of those things you don't need very often, but when you do, it's very handy.
    Ah I see - hence why I saw actuaries using it! Thanks Shg

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1