Match/Index over multiple tabs

[Mango1337]

Hi,

I am trying to do a match/index over multiple tabs but it will not work.

My formula is located in the 'Design' tab in the F Column, see the attached file

=IF(ISNA(MATCH($D2,'F1'!$A$2:$A$200,0)),0,INDEX('F1'!$G$2:$G$200,MATCH($D2,'F1'!$A$2:$A$200,0)))

This is the formula that works for one tab, the "F1" tab in this case.

So basically, it compares the value of the D column in the Design tab with the value in the F1 tab at a certain column and return the price if the cells matches. (Column F = Prix, which is "price in french")

Does anyone know how to make it work over multiple tabs, say 40 tabs ?

Best regards,

[JohnTopley]

Question: what differentiates F1 from F40?

[Mango1337]

Question: what differentiates F1 from F40?

Each tab would represent a different order.

[JohnTopley]

I would recommend you have a single table with a column representing what is currently the tab name (F1, F2 etc).

[Mango1337]

I would recommend you have a single table with a column representing what is currently the tab name (F1, F2 etc).

This would not work as I would like to be able to have twice the same row but with a different quantity in the same sheet and it doesnt add up correctly if I have more than one row with the same name.

Any other ideas ?

Thanks,

[JohnTopley]

I don't understand your logic: you could have 20 rows with "Bananas" with an "Order Number" for each row. You can summarise by Order or any other parameter.

[Mango1337]

I don't understand your logic: you could have 20 rows with "Bananas" with an "Order Number" for each row. You can summarise by Order or any other parameter.

I tried to summarize by order but the right amount will not update correctly in E column of the tab "Design".

As you can see in the attached file, see the F1 tab for example.

I might be misunderstanding what you are proposing, sorry if this is the case.

Best regards,

[JohnTopley]

To SUM .

in E2.

=SUMIFS('F1'!C:C,'F1'!B:B,Design!D2)

Copy down

[Mango1337]

To SUM .

in E2.

=SUMIFS('F1'!C:C,'F1'!B:B,Design!D2)

Copy down

Seems to be working, thanks for your help and have a nice day.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[kvsrinivasamurthy]

Here is the macro code to fill formulas given by JohnTopely , with slight modification ,in range G2 to last row Last column. Filling manually is a difficult job.

Please Login or Register  to view this content.