Jun 15 2020 07:46 AM
Hello everyone,
I am trying to use VLOOKUP to reference a second sheet (attached is the spreadsheet I am using). I want to ask column C in the 'Activities' sheet (a number to represent the 'Type' of activity) to autopopulate once someone has selected a number from the drop-down list in column E in the 'Activities' sheet. This drop-down list is the same as column H in the 'Key' sheet. The formula I have tried is in cell C5 in the 'Activities' sheet and is: =VLOOKUP($E$5,Key!H3:I34,7,FALSE). The error message I am getting is #REF!.
Any help in resolving this formula would be very much appreciated - thank you!
Georgina
Jun 15 2020 07:54 AM
Jun 15 2020 08:03 AM
@Jan Karel Pieterse Thank you - do you know if this is a problem with the formula, or with what I want to do? I want a cell to autopopulate with a number from one column in a different sheet, depending on the value someone selects in a drop-down list in another column.
Jun 16 2020 02:43 AM
SolutionHi Georgina
The issue was that you were looking up column H:I (2 columns) but wanting to retrieve data from the 7th column - so you were getting the #REF error.
I have changed the range to H:N columns - and then retrieving the data from 7th column - this returns 0 (space). I added a second line and retrieved data from 4th column so you could understand how this work. Hope this helps!
Peter
Jun 16 2020 04:07 AM
@peteryac60 Thank you so much, you have solved all my problems! I have amended the original spreadsheet so that you can now select an HEI Descriptor from a drop-down list, and the selection auto-populates three other cells. Thank you!
Jun 16 2020 06:59 AM
Jun 16 2020 08:00 AM
Jun 16 2020 08:02 AM
Jun 16 2020 08:04 AM
Jun 16 2020 08:23 AM
Jun 16 2020 02:43 AM
SolutionHi Georgina
The issue was that you were looking up column H:I (2 columns) but wanting to retrieve data from the 7th column - so you were getting the #REF error.
I have changed the range to H:N columns - and then retrieving the data from 7th column - this returns 0 (space). I added a second line and retrieved data from 4th column so you could understand how this work. Hope this helps!
Peter