Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

Excel 2007 changes formula when copying

  • Please log in to reply




  • Member
  • PipPip
  • 16 posts
I have created an Excel worksheet which acts as a summary of a series of workbooks. Using a Macro to import the data creates the links to the underlying workbooks. Once the Macro is run the data is in rows and needs transposing (the links work fine at this point). I use paste special transpose to put the data in the new worksheet and as soon as it is run the data is linking correctly. However, before I can do anything the blue circle appears and Excel changes the cell reference of the linked formulae in the number of rows I have copied.

For example the cell references in the linked workbooks are 'workbook name' $A$1,$B$1-$B$20. If I transpose 5 rows of data the new linked cells are 'workbook name' $A$1,$B$1,$C$1,$D$1,$E$1,$F$1,$B$6-$B$9.

I'm using absolute cell references and if I only transpose the cells B1-B20 this problem doesn't occur. I assume that it is to do with having A1 then B1 and Excel auto-correcting what it sees as a mistake in the formula. Is there anyway to stop this?

  • 0


Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP