I have a 'Participant List ' sheet with 23 'Units' across columns and 100 'Participants Names' down rows; and an 'Assessment' sheet laid out the same way.
A 'Report' sheet is laid out with 'Units' in column A and 'Competent/Not Yet Competent' down Column B.
There is an A4 size area for each participant going down this sheet.
Each area picks up the values from the Participant and Assessment sheets using the following formula:
ie
the formula in D1 is =IF('Participant List'!F1="","",IF(Assessment!B1="C","Competent","Not Yet Competent"))
the formula in D2 is =IF('Participant List'!G1="","",IF(Assessment!C1="C","Competent","Not Yet Competent"))
I have to modify this formula slightly for each of the 23 units in 100 Participant Name areas, which is a lot of typing.
There must be a coding solution...
How can I use VBA to place the formula in the top row of each area (D1) and then fill down so that it changes F1 to G1 to H1 and B1 to C1 to D1 and so on for each of the 23 rows?
Many thanks in anticipation...
!--removed tag-->
Enter your message below
Sign in or Join us (it's free).