Formula help with transferring data

[xchorse]

I have a worksheet with two tabs. The first tab I have 6 columns withe general number data in them for hours of work to perform the task at the 6 different levels which are the 6 columns. This is the basic set up for my template. Now I used this template fore each job and depending on which level the job falls in will determine which of the 6 columns I want to keep and I am going to delete the remaining 5 columns with hours in them because they don't have anything to do with this job. So when I delete the unused columns I will always have the remaining useful data for that project in column J. Then I have to take that information from column J and transfer it into the second tab in the workbook. I thought I could go onto the second tab where I need the final information from the first tab and use the formula =SUM(Worsheet!J97) and it will read the data that is in cell J97 from the tab called Worksheet. This is all works in the template but as soon as I delete the other 5 not used columns the formula on the second tab gets an error in it saying that (Moving or deleting cells causes an invalid cell Reference, or function is returning reference error). So how do I get this resolved? No matter what column I choose to keep it will always be in Column J, but then I need that information from Column J to populate into the second Tab. What is the best way to accomplish this.
Thanks for your help.
Marcy

 

[xchorse]

Really no one has any words of advice to help me with this problem?

 

[GR00007]

There are many questions to even start to formulate advice:
- are the columns deleted prior to J - J will no longer be J
- could you use J to select the 1 of 6 valid values instead of deleting columns
- Sum is for adding a range of values, not one cell
- depending on the version of excel, deleting/inserting columns may automatically update the reference on the other sheet
Do you have an example?