Combing Workbooks

Resolved · Low Priority · Version 2016

Tue 29 May 2018, 16:45 Jessie Scott (guest) said...

Combing Workbooks

I would like to combine two workbooks that are formatted in a questionnaire style, one column in the first workbook is questions and the other is answers, the other workbook currently only has questions so I would like to combine the two so that any blank unanswered questions from the new workbook are shown up. I have googled this for multiple explanations but mostly are unable to do this because the format is words rather than numbers. I am unable to compare and merge because although I have created the shortcut in the toolbar it is blanked out even though all of our workbooks are automatically shared as it is created on a shared drive I am unable to do this.

Is there any way i will be able to do this so I can easily see the unanswered questions to my questionnaire?

Thank you.

Thu 31 May 2018, 09:04 Trainer Jens said...

RE: Combing Workbooks

Hi Jessie,

Thank you for the forum question.

Shared workbooks have a lot of limitations.

You could use an If function.

IF you in the source workbook have the question in column A and the answers in column B, you can test in the destination workbook if a question is answered.

If the source workbook is named source.xlsx.

The If function will need the path to the source file. I am testing if the answer column is different from blank. If it is I want a blank cell in the destination workbook. If it is not blank, I want the text "Not Answered".

=IF('C:\Users\Questions\[source.xlsx]Sheet1'!B2<>"","","Not Answered")

I hope this makes sense.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector