cancel
Showing results for 
Search instead for 
Did you mean: 

excel

Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

excel

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.

9 REPLIES
Community Veteran
Posts: 38,304
Thanks: 969
Fixes: 57
Registered: 15-06-2007

Re: excel

have you tried using pivot tables

Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

Re: excel

a pivot table would still require manual user intervention to work though AFAIK ?
Community Veteran
Posts: 38,304
Thanks: 969
Fixes: 57
Registered: 15-06-2007

Re: excel

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

Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

Re: excel

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.

Community Veteran
Posts: 4,971
Thanks: 1,152
Fixes: 28
Registered: 16-10-2014

Re: excel

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.

Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

Re: excel

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.
Moderator
Moderator
Posts: 25,874
Thanks: 1,187
Fixes: 49
Registered: 14-04-2007

Re: excel

vLookup?

Customer and Forum Moderator.

Community Veteran
Posts: 3,274
Thanks: 339
Fixes: 12
Registered: 24-10-2013

Re: excel

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.
Community Veteran
Posts: 38,304
Thanks: 969
Fixes: 57
Registered: 15-06-2007

Re: excel

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