excel

[#1]

any excel gurus here?

I have a spreadsheet with 2 sheets.
"Front Sheet" and "Data".

The Data sheet contains all the raw data.
The Front Sheet contains stats based on what is in the Data sheed (using formulas and calcuations etc).

What I am looking to do is display a sub-set of data from the Data sheet on the Front Sheet based on certain criteria.
The amount of data brought forward from that sheet could vary, so it could be 2 rows or 12 rows of data (for example).

Is there a way to do that in Excel?
Basically it would be pulling all data where column E is "blah", for example.
It would need to do this withouth any user interaction. All they would do is paste the raw data into the Data sheet and everything would automatically populate on the Front Sheet.

[#2]

have you tried using pivot tables

[#3]

a pivot table would still require manual user intervention to work though AFAIK ?

[#4]

depends on how you set it up although it is many years since I used them

[#5]

the only user input that can happen is the copying and pasting of the raw data into the sheet.
I thought it might require something along the lines of a VBA function with an array of some sort.

[#6]

If you're comfortable using Python then you can get the user to supply the raw data as CSV then use openpyxl to create the document, load the sheet and create the formula based on the number of rows imported. I'll gladly help you with the Python if you want.

[#7]

as this is based on someone else doing it (office staff).
it'll need to be a simple copy and paste job into the existing excel spreadsheet.

[#8]

vLookup?

[#9]

a vlookup would only work if you had a set number of rows AFAIK.
the amount of rows would vary.
plus it would need to match on something on the first sheet, which there wouldn't be.

[#10]

as I said I am way out of touch but I seem to remember doing something like that using pivot tables and a macro button