Indexing data from another sheet based on drop down list selection

0
votes

I've got 2 sheets. Sheet1 and sheet2 for this lets call them. Cell E3 to X3 will have the formula(with the exception of N3 and O3). Column B has the names to check from B3 onward. Shee2 has the exact same layout as sheet1 so values/locations are same in sheet1 as sheet2. Each row(which has 2 merged rows(so B3/B4 merged together etc)) in Column B in sheet1 is a drop down list of all the names in Column B from sheet2. The situation (assumes all info for sheet2 already inputted): Name selected from drop down list in B3, E3 checks B3 for a name. E3 then checks for match in column B (from B3) from sheet2. E3 then pulls all the data from row 3 in sheet2 and inputs into relevant columns(E3 to X3(with the exception of N3 and O3)).

I had a code for how I was doing it earlier when it was in the same sheet. But I've changed how I want to do it and now it's in a separate sheet. Before it was checking if the value of B3 matched the value of any other row in Column B and simply taking the data it had there into it's own. But now it's in a separate sheet it works a bit different I think. I tried adding the sheet name in front of the column values in hope of it working but of course it did not.

=IF(COUNTIF($B:$B,$B3)>1,INDEX(E:E,MATCH($B3,$B:$B,0)),"")

I also tried changing it to just an IF column B in sheet2 = B3 then index but didn't work but I probably didn't do it right. Any ideas?

1
excelexcel-formula

1 Answers

1
votes

If I understand your problem properly and referring to your previous questions, my guess would be that you need something like that:

=IF(COUNTIF(Sheet2!$B:$B,$B3)>1,INDEX(Sheet2!E:E,MATCH($B3,Sheet2!$B:$B,0)),"")

Though now the risk of having a circular reference might be absent, so you could probably get away with:

=IFERROR(INDEX(Sheet2!E:E,MATCH($B3,Sheet2!$B:$B,0)),"")

If you want to add a sheet name, the syntax is Sheetname!Range. If Sheetname contains spaces, then you have to surround the name with single quotes, like 'Sheet name'!Range.