Forum Discussion
Can I copy a table's formula while omitting the sheet/ workbook reference
Hello,
I have a large data set that ranges across about 10 tabs within a worksheet. I have one tab that pulls specific data from each tab and puts it onto one sheet. Below is an example. Each of these data points are in the same exact cells in each tab. So, the function is ='P1 (4.9.21)'!G15 with P1 (4.9.21) being the tab name. I would like to copy the functions for the whole table while keeping the tab references, but OMITTING the whole workbook reference, so I can paste it into a different workbook (which has the same exact layout) and have it pull the data from that workbook. Currently, when I copy the table from one workbook and paste it into another, there is a workbook reference ='[P1 - 8 weight data.xlsx]Packaging 1 (4.9.21)'!L15 .
My question is: is there a way to copy ONLY the cell function and paste it into a different workbook without copying the workbook reference? I need to keep the tab reference (I pull the same data from the same tabs across all workbooks).
My apologies if this doesn't make sense, it is a complicated issue I am having and it was difficult finding the words to describe it!
| P # | Average % Δ | Top Shelf | Middle Shelf | Bottom Shelf |
| P 1 | -2.91 | -2.95 | -3.00 | -2.79 |
| P 2 | -4.19 | -5.16 | -3.83 | -3.60 |
| P 3 | -0.32 | -0.37 | -0.37 | -0.21 |
| P 4 | -0.08 | -0.30 | -0.15 | -0.13 |
| P 5 | -0.86 | -1.32 | -0.57 | -0.43 |
| P 6 | -0.39 | -0.23 | -0.38 | -0.36 |
| P 7 | -4.24 | -5.89 | -3.79 | -3.57 |
| P 8 | -3.65 | -4.77 | -2.82 | -2.66 |
4 Replies
kupdegraff96 You could copy first, then use the Replace dialog (Ctrl+H) to replace [P1 - 8 weight data.xlsx] with nothing (leave the 'Replace with' box blank)
- My apologies, I think I misunderstood you. I tried doing a replace dialog as you suggested, but it keeps pulling up an "Update Values" page and wants me to select data from my computer
Perhaps you need to replace the worksheet name too?
HansVogelaar I have a whole table that I'd like to copy, so copying each cell individually and deleting the workbook reference would take the same amount of time as just making a new table with new functions.
