Vlook between multi-pal sheets

[Shakeelqamar]

I have 6 sheets in one workbook

Sheet 1 is main sheet
Sheet 2 Vendor A
Sheet 3 Vendor B

I am think to solve like this,

If I enter in Sheet 1, A2 'P' then data enter in Sheet 2, A2
If I enter in Sheet 1, A3 'B' then date enter in Sheet 3, A2
If I enter in Sheet 1, A4 'P' then data enter in Sheet 2, A3
If I enter in Sheet 1, A5 'P' then date enter in Sheet 2, A4
If I enter in Sheet 1, A6 'B' then date enter in Sheet 3, A3

PLEASE HELP

[FDibbins]

I think you are going to need to upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Shakeelqamar]

Here is test workbook

[FDibbins]

What determines which sheet gets selected?

I know you put B and N, but Im sure that is very simplistic, and you probably have more sheets?

Im thinking that INDIRECT is what we need here, but for that to work, I need to have something (other than B) to link the sheet name to

[shukla.ankur281190]

Try

Put in sheet1

J2=Sheet3
J3=Sheet2
K2=N
K3=B

Now put below formula in sheet2

A2=IFERROR(INDEX(Sheet1!$B$2:$E$250,AGGREGATE(15,6,ROW(Sheet1!$B$2:$B$5)-ROW(Sheet1!$B$2)+1/(Sheet1!$A$2:$A$5=VLOOKUP(MID(CELL("filename",$B$1),SEARCH("]",CELL("filename",$B$1))+1, SEARCH("]",CELL("filename",$B$1))-SEARCH("[",CELL("filename",$B$1))-1),Sheet1!$J$2:$K$3,2,0)),ROWS(A$2:A2)),MATCH(Sheet2!A$1,Sheet1!$B$1:$E$1,0)),"")

above formula will automatically insert data in respected sheet.

Check the attachment.

[Tony Valko]

Note that the AGGREGATE function requires Excel 2010 or later.

The OP's profile says they're using Excel 2007.

[shukla.ankur281190]

Thanks for pointing issue Mr.Biff.

Then you can amend above post #5 formula with below one.

A2=IFERROR(INDEX(Sheet1!$B$2:$E$250,SMALL(IF(Sheet1!$A$2:$A$250=VLOOKUP(MID(CELL("filename",$B$1),SEARCH("]",CELL("filename",$B$1))+1,SEARCH("]",CELL("filename",$B$1))-SEARCH("[",CELL("filename",$B$1))-1),Sheet1!$J$2:$K$3,2,0),ROW(Sheet1!$B$2:$B$250)-ROW(Sheet1!$B$2)+1),ROWS(A$2:A2)),MATCH(Sheet2!A$1,Sheet1!$B$1:$E$1,0)),"") with control+Shift+Enter as it is an array formula

Check attached file.

Note that the AGGREGATE function requires Excel 2010 or later.

The OP's profile says they're using Excel 2007.

[Shakeelqamar]

Thank you Mr. Shukla

Now I try this in my original workbook.

Thanks Again

[shukla.ankur281190]

Glad to help you.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED and add reputation by clicking star * icon left corner on your screen.. Thanks.

[FDibbins]

shukla.ankur281190 I think OP said they were going to go and try that, not that it worked