How to find data from multiple sheets to a single sheet as shown in sample file

Dear friends,
Thanks in advance as I have got lots of help from you. Now, I need to find data from multiple sheets to a single sheet as shown in the sample file(File size 22 kb). I have Stock name in column B (please see Analysis sheet) and I have lots of other sheets where I will obtain data from web. Hence, I want to put data in analysis sheet as I have designed( Column C, D, D,E............ in analysis sheet). But I have failed to put data using my limited knowledge. I am eagerly waiting for getting help help to solve my problem. I need this data frequently. I humbly seek your kind help.
With regards,
Samorita

Originally Posted by Glenn Kennedy

Hi there. Try this formula in C3, copied across and down:

=IFERROR(OFFSET(INDEX(INDIRECT("'"&$B3&"'!$A$1:$A$20"),MATCH(INDIRECT(ADDRESS(1,3+5*INT((COLUMNS($A:A)-1)/5),4)),INDIRECT("'"&$B3&"'!$A$1:$A$20"),0)),1,MOD(COLUMNS($A:A)-1,5)+1,,),"")

See sheet to see it working:

Dear Glenn,
Thank you so much. Your formula has solved my problem within nano second. But I have tried to use the same formula for getting another data, unfortunately I have failed to do so. I have attached herewith the sample file. I will be highly pleased if you do me another favour and explain the formula as well so that I can use it in future in a customized way. Thank you once again for your excellent quick support.
With regards,
Samorita

Hi @Samorita
I think you can avoid many problems if you store your information in another way, as shown in the image.
Samorita.JPG
Please find attached the file with the data structure in Sheet1

Originally Posted by José Augusto

Hi @Samorita
I think you can avoid many problems if you store your information in another way, as shown in the image.
Attachment 474780
Please find attached the file with the data structure in Sheet1

Dear Augusto,
Thanks for your support. But the file you have uploaded is not readable. Will you please check the issue.

Originally Posted by samorita

Dear Augusto,
Thanks for your support. But the file you have uploaded is not readable. Will you please check the issue.

Hi
It works for me but I include another with extension xlsx

Dear Augusto,
Thank you so much for your generous help. Take care friend.
With regards,
Samorita

Originally Posted by Glenn Kennedy

For the first column:
=TEXT(IF(INDIRECT("'"&$B$3&"'!"&ADDRESS(ROWS($1:1)+1,COLUMNS($A:A),4))=0,"",INDIRECT("'"&$B$3&"'!"&ADDRESS(ROWS($1:1)+1,COLUMNS($A:A),4))),"dd/mm/yyyy")
copied down.

For the rest:
=IF(INDIRECT("'"&$B$3&"'!"&ADDRESS(ROWS($1:1)+1,COLUMNS($A:B),4))=0,"",INDIRECT("'"&$B$3&"'!"&ADDRESS(ROWS($1:1)+1,COLUMNS($A:B),4)))
copied across and down.

Dear Glenn,

Thank you so much. It has solved my problem. I am really grateful to you. God must help you in some other ways. Take Care my friend.
With regards,
Samorita