IFERROR formula

[aaronschembri]

I have this excel sheet and I would like it to

1. Once a treatment is written in the treatment column , it is copied into the "treatment dispensed" sheet and the GU Code copied accordingly. If there is no value that it is not copied.

Example -

with medication A I would have dispensed it to code 1 and therefore on medication dispensed sheet i'd see

"Code - 1 " "medication - A "

Could you please help?

tried creating an IFERROR formula but got stuck.

Thanks


A

[azumi]

For GU Code, put this on Sheet "Treatment Dispensed" on cell A2 then copied down:
=IFERROR(INDEX('Consult Sheet - Investigations'!$A$9:$A$33,AGGREGATE(15,6,(ROW('Consult Sheet - Investigations'!$A$9:$A$33)-MIN(ROW('Consult Sheet - Investigations'!$A$9:$A$33))+1)/('Consult Sheet - Investigations'!$T$9:$T$33<>""),ROW(A1))),"")

For Treatment, put this on cell B2 then copied down:
=IFERROR(INDEX('Consult Sheet - Investigations'!$T$9:$T$33,AGGREGATE(15,6,(ROW('Consult Sheet - Investigations'!$T$9:$T$33)-MIN(ROW('Consult Sheet - Investigations'!$T$9:$T$33))+1)/('Consult Sheet - Investigations'!$T$9:$T$33<>""),ROW(B1))),"")