@orianfnlc-org-au

Not sure what you mean. Ctrl-Shift-Arrow down just selects cells from the active cell up to and including the first non blank cell. It doesn't copy anything.

Or are you referring to the N/A errors? If that's coming from an XLOOKUP formula, we discussed before, Excel simply doesn't find a match. Need to see the file to resolve the issue.

Hi Riny,

I got the data validation correct under Regions column and partly filled up data (Regional Coordinator and Project Officer) when a specific region is chosen but it stops filling at a certain row down the sheet and can't figure out why. 

The Regional Coordinator and Project Officer are under same workbook but separate worksheets.

I copied the formula down from the active cell by just dragging the cross-hair down but is there a better way to do it meaning copy a formula down? 

I misunderstood that Ctrl+shift+arrow down as copying but is just highlighting ranges. I got that now. Thanks.

Do you need the spreadsheet itself?

Thanks 

Dan

@orianfnlc-org-au 

Yes please, upload the file. Just remove anything confidential.

@riny

 As attached let me know if can't access. Nothing confidential I am automating my workbook and initiated that so nothing to worry. All worksheets are in separate tabs.

Thanks

Dan

@orianfnlc-org-au 

Ah, I see. The problem was that you used relative references in XLOOKUP. The first formula looked at rows 1:25. Dragged down, the next one used 2:25, 3:26 etc. You need to fix the rows by making them absolute. For example $A$2:$A$24

But, better to transform the lookup table into a proper Excel table so that you can use structured references. Have done that in the attached file. On the CFC Regions tab there is a table called "tRegions" and the formula in the "Gamba" tab look like this:

=XLOOKUP(AQ2,tRegions[Region],tRegions[[ProgramCoordinator]:[ProjectOfficer]],""

)