Creating a summary sheet from data across multiple worksheets

Hopefully someone can give me some guidance on a little task I am working on.

The data I want to use is structured as follows.

In a single excel file there will be multiple worksheets as follows

Front End
Data 1
Data 2
.
.
Data n
Summary template.

What I envisage is that in the front end worksheet you input the data you want to look for e.g. "Oranges" and press a button

Somewhere in Data 1 to Data n is a single cell with the entry "Oranges"

Excel populates the Summary template with the data relating to "Oranges" which is always fixed realtive to the cell containing "Oranges" (e.g. If "Oranges" was on worksheet Data 2 in Cell H6 - the data relating to price would be in cell I6, If you were searching for "Apples" and that was found on Data 1 in cell J9 the data relating to price would be found in K9)

Finally I want to be able to save the populated summary sheet as a separate file with the name "Summary Oranges" populated with all the data.

Unfortunately I have no idea how to go about this.

Thanks in advance for any help.

Mookarts wrote:
> Hopefully someone can give me some guidance on a little task I am
> working on.
>
> The data I want to use is structured as follows.
>
> In a single excel file there will be multiple worksheets as follows
>
> Front End
> Data 1
> Data 2


I think you should give some more details...

--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy