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

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

If you select that cell and do CSE, it will select all elements of the array. Then you can delete.

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.

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

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?

shg, that didn't work for me

I confess, I did not test ...

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

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

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

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?

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

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

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.

Originally Posted by Richard Buttrey

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).

Originally Posted by romperstomper

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

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

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

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

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.

Originally Posted by shg

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