Data Extraction for Multiple Column (Formula or Macro)

**sumairaashraf** · 09-17-2012, 02:24 PM

Hello Everyone.

I have one excel file which contain four sheets. Sheet A, B, C, D. In each sheet there are different rates for F, G, H, I.
I want to extract these rates in different sheet when i select "A" in Column A from drop down menu and "F" in column "B". It should pick vale from Sheet A and for F and if i select B and F it should pick value from sheet B and for F.
It's working for single sheet with if and vlookup but when i include another one it's not working. Can anyone help plz

**K m** · 09-17-2012, 02:28 PM

The drop down menu is done via DATA VALIDATION

What you are asking can be done via HLOOKUP, VLOOKUP, or MATCH and INDEX

Sample data is recommended
===========================
I suspect the reason the formula works on one sheet and not between sheets is the formula does not include the Worksheet Name in the formula

**FDibbins** · 09-17-2012, 02:38 PM

if you want to use a drop-down, and the items to be included in it are on another sheet, then you need to 1st give the items for the drop-down a name range. then in data validation, use "list" and then use =the-name-you-gave-the-range

**K m** · 09-17-2012, 02:51 PM

FDibbins is correct. Data Validation works between sheets ONLY when Named Ranges are used

**sumairaashraf** · 09-17-2012, 02:56 PM

I have attached both sheets. "Data for import" is used for extracting data and the second sheet is for solution.
I want to select unit and type in solution sheet and rates will be imported from "Data for import" sheet.

I am using this function for single but its not working for multiple sheets
=IF(A2=Sheet2!B2,(VLOOKUP('For Solution'!B2,'[Data for Import.xlsx]A'!$A$1:$B$3,2)))

**sumairaashraf** · 09-17-2012, 02:58 PM

I am using If and Vlookup but it's not working for multiple sheets. Sample attached. Can you plz try on the attached sheets

**sumairaashraf** · 09-17-2012, 03:06 PM

I am using this formula to extract for multiple sheets but its not working.

IF(A2=Sheet2!B2,(VLOOKUP(Sheet1!B2,'[Data for Import.xlsx]A'!$A$1:$B$3,2,False),IF(Sheet1!A2=Sheet2!B3,(VLOOKUP(Sheet1!B2,'[Data for Import.xlsx]B'!$A$1:$B$3,2,False))))

Any suggestion plz, It's a long list about 177, If i use only if function it doesn't make sense to use 177 in one cell. Plz Help