I have a master file which imports codes of items from a source file using VBA. The number of codes can vary each time they are imported. The codes are imported into a sheet on the master file called ‘source data’. I then use an INDEX formula to transfer all those codes from the source data sheet into a calculations sheet. The transfer uses a dynamic spill range so that the correct number of codes are transferred to the calculations sheet. Each of the items has a score from various tests in a sheet called ‘score data’ also forming part of the master file. I then calculate an average of each score for each item.
My issue is that I would like to have the formula to calculate the average appear on every row of the spill range and extend / contract dynamically as the spill range changes. So looking at the example below, if the spill range (red) expanded to (say) row 20, I would like the AVERAGE formula (yellow) to appear in every row up to 20; if the spill range expanded to 25, I’d like the AVERAGE formula to extend to row 25. I could achieve this by a VBA routine as the data is imported or by brute force extending it all the way to the last row or some arbitrary row far enough down that it wouldn't be an issue. However, the latter isn’t very elegant.
A stripped-down and simplified version of my file is attached. Any ideas gratefully received.
Screenshot 2022-04-11 172214.jpg
Bookmarks