Copy a formula down a varying number of cells

[bruty]

I have a spreadsheet that will be having data imported into it each week. The amount of data will change week to week. I then run a formula down these to pull out the first 3 chars of each cell.

I am now wanting to create some validation based on the first 3 letters, so I have created a named range that covers off the formula for the first 3 chars. However, to get the formula to work covering all data, I have copied it down a fair way so when I base the validation on this it has hundreds of blanks. There are also a fair few duplicates in there.

Is there any way of just creating a list of the first 3 chars of the list, removing duplicates and then basnig the validation on this?

 

[Kaps_mr2]

You could use a VBA macro - but I would need some more information on how your data is structured. could you post a screenshot using Excel Genie ? Thanks

Kaps

 

[bruty]

You could use a VBA macro - but I would need some more information on how your data is structured. could you post a screenshot using Excel Genie ? Thanks

Kaps

Not at the moment as I'm on a work PC and they've locked it all down

 

[bruty]

You could use a VBA macro - but I would need some more information on how your data is structured. could you post a screenshot using Excel Genie ? Thanks

Kaps

I'll try and explain it though.

On Spreadsheet1 in Column A I have a list of data eg:
SPM001
SPM002
ADF001



In Spreasheet2 I have a macro that imports this column and in Column B I have a formula =left(A1,3) so that then gives column A and B like this (this formula is copied down about 100 rows to make sure I cover it all):
SPM001 SPM
SPM002 SPM
ADF001 ADF

Then on Sheet2 of Spreadsheet2 I have A1 that needs to have validation from ColumnB of Sheet1 without the duplicates and blanks.

Hope this clears it up a bit??