Highlight specific values and use these values in subsequent calculations

[Wulfke]

Hi,

I'm a bit struggling right now because I think I'm going to give myself waaaay to much work when I'm going to do everything manually.
So here's the thing. I have about 200+ raw data files and I copy and paste some of the data (4 columns) of these raw files into an a new document. So that's going to be 1 sheet for every file, so 1 document with a lot of sheets. See image for how the sheets will look like, don't look at the numbers these are made up because it's an example https://image.ibb.co/dEZqiv/Example.png

For every new sheet I have to do exactly the same except for one small difference, here are the actions required:
- Paste the first 4 columns
- Calculate column E,F,G (formulas are the same for every sheet)
- I have to look at the first graph (top one)
- Decide which part of the first graph I'm going to use (for example: 9 until 25)
- Highlight included period (in the example highlighted green)
- Make a new graph with that time period (bottom one)
- All the averages below are calculated with that time period

So the difference for every sheet is the highlighting part, and thereby affecting the graph and the calculated averages.
So I was thinking, is there a possibility to just paste the 4 columns, look at the graph, highlight the included period and excel will do everything else for me? And if so, how?

[p24leclerc]

what are the type of your 200+ files? TXT? CSV? XLS?
A macro could automatically
1.transfer the 4 columns of every files into a new sheet.
2. Adjust the formulas to the data transfered.
3. and may be adjust the graph to the data transfered

Can the highlighting be automated? What are your criterias?
If so, this could be added to the macro.

First, let start with the transfer and we'll look into other part of your project later on.

[Wulfke]

The 200+ files are XLS files. In the time that has passed, I've already transferred 80% of the data into the new sheets so a macro for that won't be necessary anymore.
There are no real criteria for the selected range needed to create the new graph and calculate the averages. It is all very subjective so a macro could not do that.
However, what I'm looking for, and I think a macro could do, is give some sort of pop-up in which I need to select the correct range. And then the macro will create the graph and calculate the averages and highlight the included values.

[p24leclerc]

that coul be done but you'll have to attach a workbook with samples of your data to work with.