Bring the values based on multi criteria from a data table

**nadeem.ansari1980** · 03-12-2014, 06:47 AM

Hello to all the gurus out there
I have a problem which I am looking up to you guys, I have many shop's P&Ls and each one of them has many elements and each element has its own sheet as you can find it in attached file, I would like to have your guidance on how I can bring the values of each element in front of them based on the year they belongs to. For example how can i bring the Gross Sales amount for ShopA from Gross Sales tab, is it possible to bring the values from each elements tab based on two criteria one of them will be in cell A1 (Shop Name) of each shopTAB and second one will be Year.
how I can do it in very efficient way considering the fact that I will add new shop P&L continuously in future as well.
thanking you in advance for your consideration and precious help and advice...

Kind Regards

**AZ-XL** · 03-12-2014, 07:00 AM

Hi
Paste this formula to b3 cell shopA sheet
=INDEX(INDIRECT("'"&$A3&"'!$B$2:$E$20"),MATCH('Shop A'!$A$1,INDIRECT("'"&$A3&"'!$A$2:$A$20"),0),MATCH('Shop A'!B$2,INDIRECT("'"&$A3&"'!$B$1:$E$1"),0))

You can use same formula for other shops but change the sheet names.

Bring the values based on multi criteria from a data table

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Bring the values based on multi criteria from a data table

Re: Bring the values based on multi criteria from a data table

Thread Information

Users Browsing this Thread

Similar Threads

Bring in data from one tab based on two sets of criteria on a different tab

[SOLVED] Bring multiply values from a table

[SOLVED] Bring back a name in one column based on criteria in 2 other columns

Macro to Filter unique rows of data based on multi criteria

based on certain criteria, need to get cell values from table

Bookmarks

Bookmarks

Posting Permissions