• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Removed column and now formulas are corrupt.

I have an excel workbook that has 2 worksheets in it. one is a summary report, the other is a data page. the summary report uses the data on the data sheet to show numbers. the numbers are derived from formulas in the cells. I noticed on the data sheet that there was a blank colu,n between month and year so I tried to highlight it only to find that the year colu,n was merged with this blank column. I seperated the two columns then highlighted the blank one and chose deleter and to shift the cell to the left. once I did that and went back to my summary sheet all of my 250 formulas were showing #REF! and looking at them where there once was a rang of :
Business Objects_Data'!F1767:F1826
it now shows:
Business Objects_Data'!#REF!
I can do a ctrlZ and put them back the way they were but is there a way for me to removed the merged column without breaking the reference to the data?
  • 2
1 Solution
Saqib Husain, SyedEngineerCommented:
Can you upload an excel file with a few rows of data which shows how the formulas are working? You may use fake data.
jlcannonAuthor Commented:
ok it would be very difficult to post an example so let me explain. I have a year column that is E and F should be the month and all of my formulas reference F as the month for for instance in the formula above I am looking in the range of 1767 to 1826 in column F but I just noticed the column F is not the month column becuase column F is merged with column E which is the year so it will never find a number 1 in column F. so I seperated clumn E and F then highlighted the newly blan column F and hit delete and said to shift cell t the left which then makes column F the month like it should be but then in all my formulas where previously F was referenced it is now #REF!.
Instead of deleting a the cells, can you copy and paste them over.  Then delete the other cells without shifting?
jlcannonAuthor Commented:
perfect. I guess I was over thinking.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now