Copy a formula down a varying number of cells

bruty

Active Member
Joined
Jul 25, 2007
Messages
448
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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,538
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

Active Member
Joined
Jul 25, 2007
Messages
448
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

Active Member
Joined
Jul 25, 2007
Messages
448
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??
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top