Copy name referenced formulas between workbooks

[piaskun79]

Hi,
here is my scenario.

I have 2 workbooks containing tables with the same structure (same name, headings).
In first table I am adding column with formula referrencing to column names.

Now I want to copy this formula to my 2nd table.
In simply copy/paste Excel adds the full path of the 1st workbook. Which doesn't work for me as I want to work on data in 2nd table.
For now I use find&replace to get rid of file paths but this is annoying a little. Specially when there is a cuple of calculating columns to copy.

Is there a solution to copy/paste formulas between tables in different workbooks as they were "relative"?

Thanks in advance for your help!

 

[Chihiro]

Just double click on cell and copy literal string for formula (instead of copying cell).

Edit: If more than 1 range, you can replace "=" in the formula with "'=" copy range and paste. Replace "'=" with "=" on both workbooks.

 

[piaskun79]

Thx @Chihiro for reply.
Adding "'" to formulas and then removing it is similar to what I am doing now. I am "removing" file path after pasting formulas instead of "removing" apostrophs from your solutions.

Other workaround for me would be to copy sheet containing table and then replace values. But you can not copy sheet with table...
As much as I like Excel tables these are small inconvienences

Edit: I copy formulas strings as you proposed if it concerns several columns.

 

[KenU]

@piaskun79,

You might try the following:

1. Press Ctrl + `(the one on the ~ key on US keyboards); this shows the formulas in each cell rather than the resulting values; alternatively you could accomplish the same through the options dialog
2. Select all of the cells (continuous range) with the formulas you want to copy
3. Press Ctrl + C to copy the selection
4. Paste into a text editor
5. Select all and copy from text editor; steps 4 & 5 will prevent Excel from altering the references
6. Be sure that destination workbook is also showing formulas (as in step 1)
7. Select the destination cell (Upper left corner of destination range)
8. Press Ctrl + V; this pastes each formula as written
9. Press Ctrl + `(the one on the ~ key on US keyboards); to return to viewing values; or go through options dialog

I know this seems like a lot of steps, but it really does not take very long (unless the ranges are quite large).

I hope this helps.

Regards,
Ken

 

[piaskun79]

Hi @KenU
this is good solution and it's working.
I knew about CTRL + ~ but I didn't think about it, especially combining with text editor.

I know this seems like a lot of steps, but it really does not take very long (unless the ranges are quite large).

You are right. It only seems like

Thanks!