Office 2003 Excel funtion - data table

[Jolly]

I often use the data table function in excel 2003 but while working on the
spreadsheet and not touching the cells with the "Table" function on adhoc
basis the function looses referneces to the row and column input cells
resulting in #REF error.
Has anyone come across this problem?
Is there a reason why this should happen?

I would be grateful if someone can sort my problem?
Thanks
Rita

 

[Harlan Grove]

I often use the data table function in excel 2003 but while working on the
spreadsheet and not touching the cells with the "Table" function on adhoc
basis the function looses referneces to the row and column input cells
resulting in #REF error.
Has anyone come across this problem?
Is there a reason why this should happen?

....

Do you mean the TABLE formulas start off with something like

=TABLE(X99,Y200)

but as you're doing other things in other parts of the workbook the
formulas become

=TABLE(,)

or do they become

=TABLE(#REF!,#REF!)

?

If the former, I've never come across this. Are you running any
macros? Does this happen after you perform certain operations?

If the latter, the most likely cause is pasting into the input cells
from a Cut operation or deleting the input cells. IOW, performing
operations that would make any other formula referring to those cells
replace those references with #REF!. Are you running any macros? Are
you CUTtine and pasting? Are you deleting cells/rows/columns?

 

[Jolly]

Hi Harlan

Thank you for your response.
I use the latter form of table function and I get the error : Table(#ref,#ref)

The cells that the Table is set at are not touched, changed or the
Row/column deleted in the process.
I might be editing or sometimes deleteing/inserting rows in other sheets
when the Table function looses its references.
Appreciate if you can help.
Thanks

 

[Ashish Mathur]

You have deleted the row/column which contains the row inout and column
inout variable

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com