Index match different workbooks

Hi guys,

I am battling with 2 worksheets and desperately need some help with an index and match function!

I am looking to have a master moderator file (for a group of moderators) whereby I will input various comments for different moderators each month. In the master moderator file this will be the April tab.

How can I setup index match for the comments relevant to a moderator (see 'test case file') for comments to be input on the 'test case file'.

So for example,

On the test case file. cell B2 should have the data from cell B2 on the master moderator case file? this is for moderator number 171. there will be different moderator numbers for different moderators, hence why i want to use index match - oh and this is just for the april tab! thank you guys

Formula:

Please Login or Register  to view this content.

thanks a lot avk.

Just playing with this now. Can I ask how can I get all references of the moderator number captured? so for example from the master moderator case file:

Row 1:

Moderator no. 171

Row 2: Moderator no. 1001

Row 3: Moderator no. 171

How can I ensure that different comments etc are captured for row 1 and row 3 (basically everytime i input anything to do with moderator no.171)? in the test case file spreadsheet

thank you!

Originally Posted by rayted

thanks a lot avk.

Just playing with this now. Can I ask how can I get all references of the moderator number captured? so for example from the master moderator case file:

Row 1:

Moderator no. 171

Row 2: Moderator no. 1001

Row 3: Moderator no. 171

How can I ensure that different comments etc are captured for row 1 and row 3 (basically everytime i input anything to do with moderator no.171)? in the test case file spreadsheet

thank you!

can anyone help please?

If both workbooks are open try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Originally Posted by JeteMc

If both workbooks are open try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Hi JeteMc

This seems useful but if I may elaborate on my situation further..

I will have one master file (master moerator file) and 30 test case files per unique individual for them to access etc. Therefore, is there a way I can have this setup so I don't need to open 30 test case files and also ensure that i don't have to manually drag down a formula in each test case file? so if we say test case file no.1 will be for someone with ID no. 100, all they need to see is when the master moderator file has anything to do with ID no. 100 (i.e. there's a message for them). So I don't know if '100' or cell 'A2' on a test case file spreadsheet should be a reference point as part of a formula?

thanks as always for your continued guidance

Look forward to hearing from you

If the master moderator file was accessed by a pivot table, Excel may be able to automatically produce the 30 individual case tests as sheets in the same workbook. If that is something you think may be of interest, take a look at the tutorial and video.
https://www.excelcampus.com/pivot-ta...-filter-pages/
Let us know if you have any questions.

Originally Posted by JeteMc

If the master moderator file was accessed by a pivot table, Excel may be able to automatically produce the 30 individual case tests as sheets in the same workbook. If that is something you think may be of interest, take a look at the tutorial and video.
https://www.excelcampus.com/pivot-ta...-filter-pages/
Let us know if you have any questions.

Hi JeteMc

this offers a good solution, but not quite applicable in my case.

I will want seperate sheets (which will be stored on microsoft one drive) with access available to 30 employees (but to their own file only). hence why i am looking to have a master file with comments being fed into seperate sheets!

Also.. if I use your suggeston in post no.5, i have wrote the moderator iD no. in column A in the test case 5 and dragged the formula down. As there was nothing populated in the master modeator file, the cell returns: #NUM! - that seems fine. Can I add an IF function to your formula to basically say something instead of #NUM! i.e. 'No data to display here' - just to prevent confusion for any readers looking at the test case file.

Thank you!

As to the separate sheets on one drive, I have a feeling that a VBA solution will be needed. You may want to close this file by marking it 'Solved' since your original question was answered, then open a new thread in the Excel Programming / VBA / Macros forum.
As to amending the formula, try this:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.