Fill Cells With Specify Values

  • Thread starter Thread starter SORRY
  • Start date Start date
S

SORRY

I HAVE IN AN AREA (E.G. $F$1:$F$10) SAME VALUES.
I WANT (WITH DATA-VALIDATION MAYBE) TO PUT THIS VALUES (SAME OFF THI
VALUES) AND ONLY THIS IN CELLS $A$1:$A$5.
VALUES IN A1-A5 MUST BY DIFFERENT ONE FROM OTHER AND ALL MUST BY FRO
DATA AREA F1-F1
 
Hi
not quite sure what you want to achieve but if you want to extract
unique entries from your source range F1:F10 you may use advanced
filters:
For an example have a look at
http://www.contextures.com/excelfiles.html#Function
The example file 'AdvFilterCity.xls' shows you one way using advanced
filter in combination with some VBA code

If you need a formula try the following
In A1 enter 8this is the first unique entry)
=F1
in A2 enter the following aray formula (entered with CTRL+SHIFT+ENTER)
=INDEX($F$1:$F$10,MATCH(0,COUNTIF(A$1:A1,$F$1:$F$10),0))
and copy this down. Ih there a less than 5 unique entries in your
source range this formula will return #NA after the last unique entries

HTH
Frank
 
First of all, many thanks for your time. My exact problem is. In f1-f1
I have ten different values. I want put some off them in cells a1-a5.
want put this values using a data validate in a1-a5 cells, but, I wan
values in cells a1-a5 is unique (e.g. if 1,2,3,4,5,6,7,8,9,10 i
numbers-values in f1-f10 cells and I put the number 7 in a1 cell,
can’t put number 7 in any off the rest a2..a5 cells.
 
Hi
so you want to put them MANUALLY in the range A1:A10 and only numbers
from the range F1:F10 are allowed. And in addition you can enter a
number only once in this A1:A5 range. If this is your specification
then try the following:
1. Select your cells A1:A5
2. Goto Data Validation and enter the following formula:
=AND(COUNTIF($A$1:$A$5,A1)=1,NOT(ISERROR(MATCH(A1,$F$1:$F$10,0))))

Frank
 
YES,....... BUT i want put the numbers in a1:a5 cells with data validat
list, or something else like this mecanism.
 
YES,....... BUT i want put the numbers in a1:a5 cells with data
validate list, or something else like this mecanism.
Click to expand...
Hi
wanting everything, validation and a list :-))
AFAIK this is not possible without using VBA. Of course you can create
a listbox for your enrties but to prevent duplicates you have to use an
event procedure.
But maybe someone else has a better solution to combine listbox entries
with additional validation for duplicates

Frank
 
Back
Top