Data Validation/Macro/AutoSort

[Ctomo]

Hopefully I can explain what I am trying to do without sounding like a melon.

I export data from a company created file into excel containing various, ever changing information. What I would like to do is automatically calculate the average "Plate Thk" dependant on the "Program No".

For Example, for rows containing "1" in column "Program No" calculate the average "Plate Thk", and so on for all the other programs.
I would be able to do this if the imported data stayed the same size using data validation and Vlookup, however, my problem is the amount of data and range of different program numbers means data validation with name tables will not work.

I must admit I am a novice at Excel, I've only just managed to figure out how to actually use Vlookup!

I have attached a copy of an example of the data imported. Any help would be appreciated.

Thanks In Advance

[Fotis1991]

Do you mean?

=AVERAGEIF(B:B,B2,D:D)

[Ctomo]

Basically, yeah. But every time I import new data I would have to keep changing formula, this would be a problem because some data files contain up to 10,000 rows with 25 different program numbers.

I would like to have a drop down box which would contain only unique program no's (which can change with new data entered) to then calculate the average plate thk based on the program no selected.
As I said, if the data sizes weren't variable I could do it. I could have 2 entries for program 1 in one data collection and 35 entries for program 1 on another.

[Ctomo]

Never mind I've used the AverageIF function with a data validation list based on every program number ill ever use
Thanks for the Averageif function

[Fotis1991]

So we have to create a unique list. Let's say in AA2 and copy down.

=IF(MAX($AA$1:$AA1)=MAX($B:$B),"",SMALL($B:$B,1+COUNTIF($B:$B,"<="&N($AA1))))

Name this as List. Create in L2 a validation list using that name(List).

Then use this formula.

=AVERAGEIF(B:B,L2,D:D)

[Ctomo]

Thanks!

I managed to do it by creating a 'master list' of all the rivet programs possible (basically 1 - 200), naming that range and creating a data validation list and then using your averageif formula to calc the average based on the number chosen in the data val list.

I did try and make a chart with loads of functions but gave up...think I will do that the long way

Thanks for your help