Cut vs Copy Changes Formula

[Mitchy]

I am working in a workbook with three basic types of worksheet.

One sheet has a list of parts and various data related to the part for an entire job including a cell refering to a vendor code.

Another group of sheets is the order form for the parts for each individual vendor.

Finally, the third form is a summary sheet for all of the various order related to a job.

I either cut or paste data from the first sheet to the vendor specific sheet. Each vendor has its own tab named for the vendor code. As this info is pasted, cell G18 is filled with the vendor code.

The summary sheet totals all of the various parts that were ordered from that vendor for the job. The summary also has a formula that refers to the cell on the order form to copy the vendor code to the summary sheet.

The formula in question is =(vendor code!g18) where the vendor code is the specific tab of the order work sheet.

When I copy and paste the data, the formula works and returns the data in g18 from the vendor code tab.

However, when I cut and paste, the g18 changes to #ref!. I cannot figure why copying vs pasting changes the formula.

My preference is cutting because I can see when everything on the list has been ordered. Any suggestions?

 

[AnotherHiggins]

Have a look at Relative vs. Absolute References in Excel Help. From Help:

"Relative references automatically adjust when you copy them, and absolute references don't."

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Mitchy]

I think I understand the relative vs absolute reference.

Why does a copy/paste work correctly but a cut/paste not work. I am currently just going in and changing the formula manually which is a pain.

Thanks,

 

[bluedragon2]

Because, when you cut and paste, it moves the formula as is, when you copy and paste, it offsets relative references...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Mitchy]

I am not copying or cutting a formula. The cell in question just contains a three letter abbreviation refering to a specific vendor name. The formula in question in on a different worksheet that references the cell with the text data in it.

 

[SkipVought]

Mitchy,

I have found that in 99.99% of cases where users are using copy 'n' paste, there are MUCH better ways of accomplishing the objective, including using lookup formulas or PiovtTables or Filters or MS Query.

I advise avoiding excessive use of and on-going use of copy 'n' paste (including cut 'n' paste)

Skip,

_[red][/red]