Data Validation -- Hide Previously Used Items in Dropdown -> Alpha nummeric

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AA Arens - 16 Aug 2007 13:29 GMT

Hi,

For my project-database (excel) I use the wonderful solution mentioned
in http://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?

Thank you

Bart, Excel 2003

Reply to this Message

Debra Dalgleish - 16 Aug 2007 17:37 GMT

What does your data look like, and what problem are you having with the
original solution?

> For my project-database (excel) I use the wonderful solution mentioned
> in http://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?

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

AA Arens - 17 Aug 2007 12:45 GMT

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?
[quoted text clipped - 8 lines]
> 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

Reply to this Message

Debra Dalgleish - 17 Aug 2007 15:36 GMT

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.

> On Aug 16, 11:37 pm, Debra Dalgleish <d...@contexturesXSPAM.com>
> wrote:
[quoted text clipped - 99 lines]
>
> Bart

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Rate this thread: