INDEX/MATCH to return the result from 12 different sheets.

The banner at the top of the forum explains how yuo can upload a workbook

But can I suggest a more useful way if you are prepared to alter your data layout

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that contains all the data from your monthy sheets plus an extra column to identify the date.
Then every new record will be added to the master database. Typically you'd have a data entry row above the dtabase and a button to click to add the entry row to the data.

You can create the master database sheet manually by copying and pasting 12 times, or upload the workbook and we can do it for you. A simle macro would automate that task

As you are using the 2016 version of Excel you could utilize Get & Transform and a pivot table to display the data as modeled in columns K:M on the Style Detail sheet.
Note that the months Jan through April are modeled. If this is of interest, then the ranges on the other month sheets will need to be converted to tables and Get & Transform applied. It would probably be easiest to produce a new pivot table at that point.
The advanced editor code for Power Query is:

Please Login or Register  to view this content.

Let us know if you have any questions.

Please follow the attached file and see how i am doing my work and what i required for it.
Maybe you can better understand by looking here into Style Detail Sheet tab.

See the yellow banner for attaching sheets

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

Note: As you are new I will add it for you.Please read forum rules which you might have missed

https://www.msofficeforums.com/excel...lect-data.html

As there has been no response to the query on the msofficeforums:
The formula you are using in column C will not work. Utilize the Evaluate Formula feature on cell C18 and you'll see that it is only considering the Jan sheet.
If you put the following formula into cell C6 and drag down, it will work. As you can imagine it will become hard to manage when twelve sheets are involved as the formula would require IFNA to be invoked eleven times. Of course it is totally your choice.
Formula for C6: =IFNA(INDEX(Jan!$B$6:$B$31,MATCH($B6,Jan!$A$6:$A$31,0)),INDEX(Feb!$B$6:$B$31,MATCH($B6,Feb!$A$6:$A$31,0)))
Let us know if you have any questions.

Okay JeteMc
You mean i have to put this formula =IFNA(INDEX(Jan!$B$6:$B$31,MATCH($B6,Jan!$A$6:$A$31,0))......12 times because i have 12 sheets (months)?
in other words i can say that a short formula cannot work for the scenario?
What will be the full formula in single cell for the whole 12 sheets same like Jan and Feb then Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec.

My belief is yes, you can say that a short formula cannot work.
The formula would be similar to:

Formula:

Please Login or Register  to view this content.

Where there would be eleven IFNA functions and of course twelve instances of INDEX/MATCH.
There may be another contributor that can think of a shorter formula, however since that hasn't happened in the week since this thread was posted I am less than optimistic.
If you would like to consider the Get & Transform option then the following link may help.
https://www.youtube.com/watch?v=jVkWDZ7B-Zs
Let us know if you have any questions.