I don't quite follow your setup, but in this formula:
=IF(ROWS($1:1)<=COUNT('Other Parts'!M$7:M$506),SMALL('Other Parts'!M
$7:M$506,ROWS($1:1)),"")
COUNT will only count numbers, and SMALL works on a set of numbers.
AA Arens wrote:
> On Aug 16, 11:37 pm, Debra Dalgleish <d...@contexturesXSPAM.com>
> wrote:
>
>>What does your data look like, and what problem are you having with the
>>original solution?
>>
>>AAArenswrote:
>>
>>>For my project-database (excel) I use the wonderful solution mentioned
>>>inhttp://contextures.com/xlDataVal03.html.
>>>I face the problem that the data is alpha-numeric. How to modify the
>>>solution so I am able to manage this kind of data?
>>
>>--
>>Debra Dalgleish
>>Contextureshttp://www.contextures.com/tiptech.html
>
>
>
> Hi Debra,
>
> I use the following:
>
> A 1 sheet for data
> B The hidden "working" sheet to conduct the data management, as
> explained in your website
> C Several sheets to assign the provided data in the appropiate column
> (by using the combo's)
>
> There might not be an error in the complex formulas as it works fine
> with nummeric values.
>
>
> ------------------
>
>
> This is the data in A:
>
> 56378
> 56379
> 56381
> 56382
> 00000F45F4EC
> 00000F466875
> 00000F4671E7
> 00000F468572
> 00000F468593
> 00000F4686D3
>
> With cell format = General
>
>
> ------------------
>
>
> For B:
>
> Column 1 Column 2 Column 3
>
> ID Key Serial Number Value Number Available Values
> 56378
> 56379
> 56381
> 56382
> (Btw. It all works fine with nummeric values!)
>
>
> with the formulas for the three columns 1, 2 and 3:
>
>
> =IF(ROWS($1:1)<=COUNT('Other Parts'!M$7:M$506),SMALL('Other Parts'!M
> $7:M$506,ROWS($1:1)),"")
>
> =IF(COUNTIF(CTI!$W$7:$W$206,Q2)+COUNTIF(...etc...)>=1,"",ROW())
>
> =IF(ROW(Q2:Q501)-ROW(Q2)+1>COUNT(R2:R501),"",
> INDEX(Q:Q,SMALL(R2:R501,ROW(INDIRECT("1:"&ROWS(Q2:Q501))))))
>
> All cell formats = General
>
>
> ------------------
>
>
> For C (one of the sheets for data assignment):
>
> Column head: ID Key Serial Number
> <empty>
> <empty>
> <empty>
> <empty>
> <empty>
> etc.
>
>
> Combo: I can only choose the nummeric values from A unless already
> assigned to other sheets
>
> Data Validation: List > =IF(F$7<>"",AVIDKey).
> Only In-Cell Dropdown toggle is set.
>
> Cell format = General
>
>
> I use excel 2003 on XP,
>
> Add-Inns:
>
>>Lookup
>>Analysis Tookpak
>>Analysis VBA
>
> are all active.
>
>
> Bart
>
--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html
Similar Threads
