Pulling out data from different worksheet when changing a heading on the top-down list

[jjin]

Hi guys,

Please have a look at the sample excel file attached below so you get the idea of what I want to achieve here.

In the file attached below, you will notice that I have used VLOOKUP function in Table A to pull out data that meets certain criteria from Table B in 'Product A Index' tab.

Now, when I change the heading to the 'Product B' on the top-down list, I want the table A to use the data from 'Product B Index Tab', instead of pulling out data from 'Product A Index tab',
Its just so that I do not have to use separate tabs for each product lines.

Any help will be greatly appreacited.

Cheers,

HAPPY NEW YEAR TO YOU ALL!!!!!

Sample - Excel.xlsx

[benishiryo]

hi jjin. i havent really studied the formula, so i'm just going add the INDIRECT formula to it & not change much. in B13:

=IFERROR(INDEX(INDIRECT("'"&$B$6&" INDEX'!$C$7:$C$16"),MATCH(0,COUNTIF(Sheet1!B12:$B$12,INDIRECT("'"&$B$6&" INDEX'!$C$7:$C$16"))+(INDIRECT("'"&$B$6&" INDEX'!$D$7:$D$16")="")+IF(INDIRECT("'"&$B$6&" INDEX'!$F$7:$F$16")>CAL,IF(INDIRECT("'"&$B$6&" INDEX'!$G$7:$G$16")>CAL,IF(INDIRECT("'"&$B$6&" INDEX'!$H$7:$H$16")>CAL,IF(INDIRECT("'"&$B$6&" INDEX'!$I$7:$I$16")>CAL,1,0)))),0)),"")

in C13:

=IFERROR(IF(VLOOKUP($B13,INDIRECT("'"&$B$6&" INDEX'!$C$7:$I$16"),3+(C$12/12-1),0)<=CAL,VLOOKUP($B13,INDIRECT("'"&$B$6&" INDEX'!$C$7:$I$16"),3+(Sheet1!C$12/12-1),0),""),"")

seems like you want to show the amount that is equals to CAL too, so you need "<=CAL"

and a happy new year to you too
=)

[jjin]

It works perfectly on my excel spreadsheet m8. Exactly how I wanted. Thanks heaps. Happy new year