urgent, need help w/excel question!! $1MM to whoever answers it first

i have copied a "Sheet A" from "workbook 1" into "workbook 2". workbook 1 has a cell named "value_begin". workbook 2 has a cell named the same thing, but sheet A is still referencing "value_begin" from workbook 1. how do i get it to reference the "value_begin" in workbook 2?

ANY help would be MUCH appreciated

Comments (9)

Apr 9, 2008

I didn't know you can have two cells with the same name in a single file. Is this in '07?

Apr 9, 2008

why not just change the name. I thought the same way T73 did.

Apr 9, 2008

it's just still giving the same data as the previous workbook, and the previous workbook happens to have that particular cell named the same thing as what i need my formula to reference to

Apr 9, 2008

there are way too many to change; this is someone else's file, not mine, so i did not do this, but i have to work with it somehow

Apr 9, 2008

Write a macro, or cut the cells from the first sheet over the ones in the second sheet.

Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Apr 9, 2008

CTRL+F3 to open the Name Box

Then delete the name perscribed to the VALUE_WHATEVER_IT_IS cell so it isnt named anything. Relink 2nd sheet to the raw cell. Rename the cell back to whatever you want. It should have broken the other reference to the extraneous worksheet.

Apr 9, 2008

Use the audit to trail back to the reference cells. make sure there's no Workbook1!value_begin reference. You can always write a quick macro to scan all your cells it WorksheetA and replace any "Workbook1!value_begin" references to "value_begin" reference.

Sub ReplacePainInTheAssReference()
Dim bcell as range, i as integer
For i = each bcell in selection
If instr(1, bcell.formular1c1, "workbook1!value_begin" then
bcell.formular1c1 = replace(bcell.formular1c1, "workbook1!value_begin", "value_begin")
end if
Next i
End Sub

Apr 9, 2008

i owe you $1MM; thanks so much!

Apr 9, 2008
Comment