Copy text from 1 workbook to another with no formula

Using Microsoft Office Professional Plus 2010.

I am trying to copy text from cells in workbook A to workbook B. My current method of doing this is typing = in a cell in workbook B and clicking the cell from workbook A that needs to be copied over. This gives the cell in workbook B a formula like ='[WorkbookA.xlsx]Tab1'!$C$37. This method is very time consuming as I have to manually copy and change the cell number each time to move to the next cell down. Clicking and dragging does not work in this case. Is there an easier way to copy text from workbook A to workbook B that keeps workbook B constanly up to date?

Thank you

It all depends on the structure of your two files.

If the data is structured in the same way, then just remove the $ signs and you can drag across/down as normal. For example, if you put this in B2:
='[WorkbookA.xlsx]Tab1'!C37
and drag it to B3, you'll get:
='[WorkbookA.xlsx]Tab1'!C38
or drag it to C2 to get:
='[WorkbookA.xlsx]Tab1'!D37
In other words, the column and/or row reference will increase by one when you drag, just as normal.


If the structures are different, then there should be some sort of lookup which can be done, but the details will depend on how the data is organised (rows/columns/empty cells/etc).
Please attach a sample workbook/s. Make sure there is just enough data to demonstrate your need. Include a before sheet and an after sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove any confidential data (real names, etc - use fake data if the type of data essential to the process).
The paperclip icon for attaching a file doesn't work at the moment, so to attach a file, click on Go Advanced under the reply box, scroll down and click Manage Attachments to open the upload window.

• open workbook with data
• open new workbook
• drag sheet with your data to the new workbook with Ctrl key
• it will copy whole sheet

or use PowerQuery add-in where source will be in your workbook A and result in workbook B

Glad we could help, thanks for the rep and for marking the thread as Solved.