Xlookup in cell to have other cells "Paste Link" Data from other worksheets

[Ajaxs87]

I want to use worksheet ("Form") to pull data from the other worksheets based on cell F3. I am choosing Paste Link to do so because the information is update randomly from the source file.

So if I select Fr 10 in F3 on worksheet ("Form"), I want column C, cells under Steps to "Paste Link" from worksheet ("Sample 1") column B. Or if Fr 11 is selected, it Paste Link from worksheet ("Sample 2") column B.

I would appreciate any help with this. The file is attached.

Thank you in advance

[Sam Capricci]

so in your dropdown in F3 you have 12 items, are you saying you expect to have a formula that will lookup values in column B for the steps in col C on 12 different worksheets?

[Ajaxs87]

Yes, that is my expectations when its completed. I tried to water down the file, so it would be easier to configure.

[Sam Capricci]

Ok, I thought someone might answer this while I was away but didn't.
Here is how I would approach it.
1) you'll have to change the names of your tabs to match your dropdown list, so instead of Sample 1, Sample 2 etc, you need to change the tab names to match those of your dropdown list in cell F3 of the form page.
2) in your list of the information you want returned, GET RID OF THE MERGED CELLS, sorry, not really shouting, just needing to emphasize this item's importance.
So, in your Sample 1 tab (which I changed to FR 1) cell A1 is ok, but you should put all the info into B1 (Remove seatbelt connector and heater connector and return to home position.) and not try to have that in B1 through G2. Excel only "sees" what is in B1 when it comes to formulas so the merged cells will mess up the formula I'm going to give you.
3) get rid of the gaps in your lists on the lookup tabs, so in your Sample 1 tab (which I changed to FR 1) cell A1 has 1, cell A2 should have 2 etc instead of the gap now, this isn't REALLY important but it will make it so you don't have to adjust the formula I'm about to give you that will work.

Now, in your Form tab, this is the formula I'd use in cell C8 dragged down (and I'm reattaching your workbook that shows those changes and how it will work)...
=IFERROR(VLOOKUP(B8,INDIRECT("'"&F$3&"'!"&"A1:B20"),2,FALSE),"")
Now indirect can be a function that can be slow though it appears your range is limited and your number of workbooks are limited, but this formula makes it so you don't need a bunch of IF(vlookup, this sheet, or IF(vlookup that sheet etc with 12 vlookups or more in it.

[Sam Capricci]

BTW, in my point #2 above, you can left justify what is in Sample 1 tab (which I changed to FR 1) and double click on col B to adjust the size of col B to show all the info there. Any formatting you want to have you can format that in your form tab anyway you want.

[Sam Capricci]

I probably should show you how the formula actually looks because it is very particular about how it is written so I will highlight what is in it ...
=IFERROR(VLOOKUP(B8,INDIRECT("'"&F$3&"'!"&"A1:B20"),2,FALSE),"")
those highlighted in bold red are on the same key as the double quotes but without using the shift key to access them.

[Ajaxs87]

Thank you Sam!

I agree about the cell merge stuff, I am trying to get with the document's owner about fixing that part. I learned a lot with your help on this and I appreciate you for taking the time to help.

[Sam Capricci]

You are very welcome! Glad I could help. And thank you for the rep!