CONCATENATE Formulas Changing

**ThaGonz** · 09-02-2011, 07:03 PM

I am new to posting here but have used this forum before for help. I appreciate all the knowledge I have gained from the people here....so thank you all!!

Now....I have an issue that I cant find info on so I decided to see if any one can help me out.

I am using a multiple sheet format with formulas on sheet 2 that will auto populate cells on this sheet depending on the data that is in the referenced cells from sheet 1.

The problem I am having is when I "drag" or "cut and past" data from one cell to another cell on sheet 1 it changes the formula on sheet 2 and I get a REF error.

I have included an example sheet because I realize it is hard to understand what I am trying to say.

In the following example I filled out sheet 1 and have added formulas to cells on sheet 2 that will autopopulate based on the data in the referenced cells on sheet 1.
Both sheets work fine until I try to drag the name "Rob" in cell A2 on sheet 1 to cell A6 on sheet 1.
Cell A2 on sheet 2 is supposed to autopopulate data from A2 on Sheet 1 and it does so it says also says 'Rob". Cell C2 on sheet 2 is supposed to autopopulate data from A6 so when I drag the name "Rob" for A2 to A6 on sheet 1 it should now appear in cell C2 on sheet 2 but I instead get a REF# error in C2 and the formula in A2 on sheet 2 changes and now autopoulates from A6 on sheet 1.

I cant understand why the formulas on sheet 2 change when I move data on sheet 1.....I am trying to be able to move data around on sheet 1 and have sheet 2's formulas remain unchanged and display the data from the original cell i referenced. I do alot of draging or cutting and pasting because the sheet I am working with has much more data and copy and paste causes duplication errors. The example is small and basic but displays the problem I am having.....any help would be greatly appreciated!!!

Thanks in advance

**Andrew-R** · 09-03-2011, 06:55 AM

It's Excel trying to be helpful and assuming that if you move a cell you're referencing you want to move the reference as well.

If you change the formula on sheet 2 to (for example) =INDIRECT("'Sheet1'!A2") then Excel won't change it when you cut and paste.

**teylyn** · 09-03-2011, 07:01 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

**ThaGonz** · 09-03-2011, 05:37 PM

Originally Posted by Andrew-R

It's Excel trying to be helpful and assuming that if you move a cell you're referencing you want to move the reference as well.

If you change the formula on sheet 2 to (for example) =INDIRECT("'Sheet1'!A2") then Excel won't change it when you cut and paste.

That works great...Thank you! I do have one more question though. In some formulas I was using =CONCATENATE(Sheet1!A4)&RIGHT(Sheet1!C4,2)

By using "INDIRECT" instead of "CONCATENATE" I was able to stop the formula from changing for cell A4 when data was dragged or cut from that cell but the "RIGHT" formula still changes when data is dragged or cut from cell C4 .....is there a way to also get that target cell locked and still only take the right 2 letters from the cell in the same formula?

**ThaGonz** · 09-03-2011, 06:15 PM

Never mind on the second question I figured it out

New Formula is =INDIRECT("'Sheet1'!$A$2")&(RIGHT(INDIRECT("'Sheet1'!$C$3"),2))

Thanks again for the help!! Changing this to solved