Linking multiple cells at once - from vertical in the source sheet to horizontal in the destination

JH@HL

Hello - I have a sheet that is a questionnaire with basic check boxes. The questions run vertically down a column, with the checkboxes in the adjacent column. I would like to link those cells, that exist vertically in the source, to a destination sheet, where each row is now a column. I can do this cell-by-cell. However, I seem unable to link multiple cells at the same time.

In other sheets, I can select multiple cells positioned vertically, and link to another sheet where the cells are also arranged vertically. However I seem unable to, for example, select 10 vertical cells in the source and link them to 10 horizontal cells in the destination sheet.

Is this even possible? If so, how? THANKS.

Mark Cronk

Hi @JH@HL ,

I have an idea but it may not be exactly what you need. Use a VLOOKUP to populate your destination sheet. Copying one formula will do them all.

In row 1 of you destination sheet enter the Column Name, which I assume matches the questions on your questionnaire sheet. Lock the row.

In the destination cells your going to look up the value in row 1, the question, in your questionnaire question column and return the checkbox value in column 2. The formula should be something like:

=VLOOKUP([insert column title]$1, {insert questionnaire range, 2 columns}, 2)

You should be able to copy that right and fill the row.

Work for you?

Mark

Mark Cronk

HI @JH@HL ,

Happy to help. Thanks for using the community. Please accept my answer so the discussion closes.

Be Well,

Mark

Mark Cronk

Hi @JH@HL ,

I have an idea but it may not be exactly what you need. Use a VLOOKUP to populate your destination sheet. Copying one formula will do them all.

In row 1 of you destination sheet enter the Column Name, which I assume matches the questions on your questionnaire sheet. Lock the row.

In the destination cells your going to look up the value in row 1, the question, in your questionnaire question column and return the checkbox value in column 2. The formula should be something like:

=VLOOKUP([insert column title]$1, {insert questionnaire range, 2 columns}, 2)

You should be able to copy that right and fill the row.

Work for you?

Mark

JH@HL

Thanks so much for responding! I'm not sure. I have been reading it over, and I am just not experienced enough to grasp what you're saying - or to know whether i really explained it well enough. What i'm working on is a skills matrix, and each employee has their own sheet, for their own skills assessment. For formatting purposes, each individual skills matrix sheet has the skills listed vertically, down several rows of a sheet. However, when linking all of the responses to one master skills matrix, we wanted to put the employee names down the rows, and the skills horizontally across the columns. (That way it appears to look more like a traditional skills matrix that we would normally have in Excel). The challenge is getting it to match up. If the master skills matrix had all of the skills down the rows, and employee names as each column, i could link an entire skill sheet at the same time. Since we've flipped the format, i'm doing each cell manually - and it seems like there should be a more efficient way of doing it.

JH@HL

Mark Cronk! You're a genius! It worked! I finally figured it out. WOW - game changer. I think I was getting tripped up because the 2nd column was a check box - so i had to change the "2" in the formula to "false" and now it is working. I can't thank you enough!

Mark Cronk

HI @JH@HL ,

Happy to help. Thanks for using the community. Please accept my answer so the discussion closes.

Be Well,

Mark