Getting data with sheet name and columns heading

[Mohandas]

I have in this sample file 3 sheets called town1, town2,town3
Which has category of oulets namely supermarkets and so on

I have another sheet called summary in which this town data needs to be updated

Please help me getting the data from that sheets as sheet name and outlet category as criteria

Regards

Mohandas

 

[Chihiro]

Version of Excel? And do you have PowerQuery/Get & Transform in your Excel?

 

[bosco_yip]

Try,

1] In I2, formula copied right to Q2 and all copied down :

=IF($F2="","",VLOOKUP(I$1,INDIRECT("'"&$F2&"'!A2:B10"),2,0))

2] In G2, formula copied down :

=IF(F2="","",SUM(I2:Q2))

Regards
Bosco