What does your data look like, and what problem are you having with the
original solution?
AAArenswrote:
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
