Data validation - complex listbox

Hi

I have a need for data validation in which list entries are reduce when used 
anywhere in a pair of rows. However, each entry in the list should remain 
useable in the following pair of rows until used there, etc, etc.

Example

List
Cat
Cow
Dog
Bat


R1 Cat  Cow
R2          Dog Bat
R3 Cat           Dog
R4 Cow
R5
R6                          Cat
R7
R8

Cat would only be available in rows 7&8
Cat, Cow, Dog, Bat would not be available in Rows 1 &2
Bat would be available in rows 3-8
Cow, Dog, Bat would be available in rows 5&6
All would be available in rows 7&8

Is someone able to wind me up and point me in the right direction?

-- 
Steve 

0
AltaEgo
5/19/2008 6:03:40 AM
excel 39879 articles. 2 followers. Follow

5 Replies
495 Views

Similar Articles

[PageSpeed] 26

See if this starts you in the right direction:

http://www.contextures.com/xlDataVal03.html
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"AltaEgo" <Somewhere@NotHere> wrote in message 
news:OhFOXYXuIHA.524@TK2MSFTNGP05.phx.gbl...
> Hi
>
> I have a need for data validation in which list entries are reduce when 
> used anywhere in a pair of rows. However, each entry in the list should 
> remain useable in the following pair of rows until used there, etc, etc.
>
> Example
>
> List
> Cat
> Cow
> Dog
> Bat
>
>
> R1 Cat  Cow
> R2          Dog Bat
> R3 Cat           Dog
> R4 Cow
> R5
> R6                          Cat
> R7
> R8
>
> Cat would only be available in rows 7&8
> Cat, Cow, Dog, Bat would not be available in Rows 1 &2
> Bat would be available in rows 3-8
> Cow, Dog, Bat would be available in rows 5&6
> All would be available in rows 7&8
>
> Is someone able to wind me up and point me in the right direction?
>
> -- 
> Steve 


0
ragdyer1 (4060)
5/19/2008 6:42:23 PM
Thank you...I think

Hmmm, if I transpose the dynamic list formula; don't anchor the defined name 
to a set row; space my pairs of rows with a blank in between and repeat the 
dynamic list every third row... Fat, slow workbook?

OK, not as elegant as the original concept but how about letting the user 
sort out what has or has not been used with some scratching of the head 
(list sorted in alphabetical order) and use conditional formatting that 
highlights duplicates '=COUNTIF($D$2:$N$3,D2)>1'.

Revised question, How do I turn the above idea for conditional formatting 
into something like the following so it actually works when copied to other 
cells?

=COUNTIF($D$ & row() & :$N$ & row()+1 & ,D2)>1

-- 
Steve

"RagDyer" <ragdyer@cutoutmsn.com> wrote in message 
news:#o1u3AeuIHA.672@TK2MSFTNGP02.phx.gbl...
> See if this starts you in the right direction:
>
> http://www.contextures.com/xlDataVal03.html
> -- 
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "AltaEgo" <Somewhere@NotHere> wrote in message 
> news:OhFOXYXuIHA.524@TK2MSFTNGP05.phx.gbl...
>> Hi
>>
>> I have a need for data validation in which list entries are reduce when 
>> used anywhere in a pair of rows. However, each entry in the list should 
>> remain useable in the following pair of rows until used there, etc, etc.
>>
>> Example
>>
>> List
>> Cat
>> Cow
>> Dog
>> Bat
>>
>>
>> R1 Cat  Cow
>> R2          Dog Bat
>> R3 Cat           Dog
>> R4 Cow
>> R5
>> R6                          Cat
>> R7
>> R8
>>
>> Cat would only be available in rows 7&8
>> Cat, Cow, Dog, Bat would not be available in Rows 1 &2
>> Bat would be available in rows 3-8
>> Cow, Dog, Bat would be available in rows 5&6
>> All would be available in rows 7&8
>>
>> Is someone able to wind me up and point me in the right direction?
>>
>> -- 
>> Steve
>
> 
0
AltaEgo
5/19/2008 11:42:34 PM
OTOH

Calling the following from Worksheet Change together with a few other pieces 
of code to resort the list to be continuous and not move the cursor of the 
user enters a name manually is working a treat.


Sub RemoveFromList()
Dim valToRemove
valToRemove = Selection.Value

    Range("a:A").Replace What:=valToRemove, _
        Replacement:="", LookAt:=xlPart, MatchCase:=False

End Sub

All that is left is to write some code to rebuild the lookup list; make some 
of the range references dynamic and the task is finalised.

--
Steve





"AltaEgo" <Somewhere@NotHere> wrote in message 
news:uvNnEoguIHA.1504@TK2MSFTNGP05.phx.gbl...
> Thank you...I think
>
> Hmmm, if I transpose the dynamic list formula; don't anchor the defined 
> name to a set row; space my pairs of rows with a blank in between and 
> repeat the dynamic list every third row... Fat, slow workbook?
>
> OK, not as elegant as the original concept but how about letting the user 
> sort out what has or has not been used with some scratching of the head 
> (list sorted in alphabetical order) and use conditional formatting that 
> highlights duplicates '=COUNTIF($D$2:$N$3,D2)>1'.
>
> Revised question, How do I turn the above idea for conditional formatting 
> into something like the following so it actually works when copied to 
> other cells?
>
> =COUNTIF($D$ & row() & :$N$ & row()+1 & ,D2)>1
>
> -- 
> Steve
>
> "RagDyer" <ragdyer@cutoutmsn.com> wrote in message 
> news:#o1u3AeuIHA.672@TK2MSFTNGP02.phx.gbl...
>> See if this starts you in the right direction:
>>
>> http://www.contextures.com/xlDataVal03.html
>> -- 
>> HTH,
>>
>> RD
>>
>> ---------------------------------------------------------------------------
>> Please keep all correspondence within the NewsGroup, so all may benefit !
>> ---------------------------------------------------------------------------
>>
>> "AltaEgo" <Somewhere@NotHere> wrote in message 
>> news:OhFOXYXuIHA.524@TK2MSFTNGP05.phx.gbl...
>>> Hi
>>>
>>> I have a need for data validation in which list entries are reduce when 
>>> used anywhere in a pair of rows. However, each entry in the list should 
>>> remain useable in the following pair of rows until used there, etc, etc.
>>>
>>> Example
>>>
>>> List
>>> Cat
>>> Cow
>>> Dog
>>> Bat
>>>
>>>
>>> R1 Cat  Cow
>>> R2          Dog Bat
>>> R3 Cat           Dog
>>> R4 Cow
>>> R5
>>> R6                          Cat
>>> R7
>>> R8
>>>
>>> Cat would only be available in rows 7&8
>>> Cat, Cow, Dog, Bat would not be available in Rows 1 &2
>>> Bat would be available in rows 3-8
>>> Cow, Dog, Bat would be available in rows 5&6
>>> All would be available in rows 7&8
>>>
>>> Is someone able to wind me up and point me in the right direction?
>>>
>>> -- 
>>> Steve
>>
>> 
0
AltaEgo
5/20/2008 1:28:48 AM
There's a sample file here with data validation that hides selections 
made in previous columns:

   http://www.contextures.com/excelfiles.html

Under Data Validation, look for DV0016 - Assign Employees to Single Task 
Per Day

AltaEgo wrote:
> Hi
> 
> I have a need for data validation in which list entries are reduce when 
> used anywhere in a pair of rows. However, each entry in the list should 
> remain useable in the following pair of rows until used there, etc, etc.
> 
> Example
> 
> List
> Cat
> Cow
> Dog
> Bat
> 
> 
> R1 Cat  Cow
> R2          Dog Bat
> R3 Cat           Dog
> R4 Cow
> R5
> R6                          Cat
> R7
> R8
> 
> Cat would only be available in rows 7&8
> Cat, Cow, Dog, Bat would not be available in Rows 1 &2
> Bat would be available in rows 3-8
> Cow, Dog, Bat would be available in rows 5&6
> All would be available in rows 7&8
> 
> Is someone able to wind me up and point me in the right direction?
> 


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

0
dsd1 (5911)
5/20/2008 1:32:44 AM
OTOOH

Deborah's method easily handles deleted entered values. Looks like its back 
to the think tank on that method before committing too deeply on one method 
or another.

BTW if anyone is following the thread, the Sub below was bashed together in 
haste as a test.

xlWhole rather than xlPart is highly recommended.
MatchCase:=True would also be an improvement.

-- 
Steve

"AltaEgo" <Somewhere@NotHere> wrote in message 
news:e9dnbjhuIHA.3792@TK2MSFTNGP02.phx.gbl...
> OTOH
>
> Calling the following from Worksheet Change together with a few other 
> pieces of code to resort the list to be continuous and not move the cursor 
> of the user enters a name manually is working a treat.
>
>
> Sub RemoveFromList()
> Dim valToRemove
> valToRemove = Selection.Value
>
>    Range("a:A").Replace What:=valToRemove, _
>        Replacement:="", LookAt:=xlPart, MatchCase:=False
>
> End Sub
>
> All that is left is to write some code to rebuild the lookup list; make 
> some of the range references dynamic and the task is finalised.
>
> --
> Steve
>
>
>
>
>
> "AltaEgo" <Somewhere@NotHere> wrote in message 
> news:uvNnEoguIHA.1504@TK2MSFTNGP05.phx.gbl...
>> Thank you...I think
>>
>> Hmmm, if I transpose the dynamic list formula; don't anchor the defined 
>> name to a set row; space my pairs of rows with a blank in between and 
>> repeat the dynamic list every third row... Fat, slow workbook?
>>
>> OK, not as elegant as the original concept but how about letting the user 
>> sort out what has or has not been used with some scratching of the head 
>> (list sorted in alphabetical order) and use conditional formatting that 
>> highlights duplicates '=COUNTIF($D$2:$N$3,D2)>1'.
>>
>> Revised question, How do I turn the above idea for conditional formatting 
>> into something like the following so it actually works when copied to 
>> other cells?
>>
>> =COUNTIF($D$ & row() & :$N$ & row()+1 & ,D2)>1
>>
>> -- 
>> Steve
>>
>> "RagDyer" <ragdyer@cutoutmsn.com> wrote in message 
>> news:#o1u3AeuIHA.672@TK2MSFTNGP02.phx.gbl...
>>> See if this starts you in the right direction:
>>>
>>> http://www.contextures.com/xlDataVal03.html
>>> -- 
>>> HTH,
>>>
>>> RD
>>>
>>> ---------------------------------------------------------------------------
>>> Please keep all correspondence within the NewsGroup, so all may benefit 
>>> !
>>> ---------------------------------------------------------------------------
>>>
>>> "AltaEgo" <Somewhere@NotHere> wrote in message 
>>> news:OhFOXYXuIHA.524@TK2MSFTNGP05.phx.gbl...
>>>> Hi
>>>>
>>>> I have a need for data validation in which list entries are reduce when 
>>>> used anywhere in a pair of rows. However, each entry in the list should 
>>>> remain useable in the following pair of rows until used there, etc, 
>>>> etc.
>>>>
>>>> Example
>>>>
>>>> List
>>>> Cat
>>>> Cow
>>>> Dog
>>>> Bat
>>>>
>>>>
>>>> R1 Cat  Cow
>>>> R2          Dog Bat
>>>> R3 Cat           Dog
>>>> R4 Cow
>>>> R5
>>>> R6                          Cat
>>>> R7
>>>> R8
>>>>
>>>> Cat would only be available in rows 7&8
>>>> Cat, Cow, Dog, Bat would not be available in Rows 1 &2
>>>> Bat would be available in rows 3-8
>>>> Cow, Dog, Bat would be available in rows 5&6
>>>> All would be available in rows 7&8
>>>>
>>>> Is someone able to wind me up and point me in the right direction?
>>>>
>>>> -- 
>>>> Steve
>>>
>>> 
0
AltaEgo
5/23/2008 11:51:01 PM
Reply:

Similar Artilces:

Data on a 3.5 diskette
I realize this may be somewhat old school, but I have a problem with some data on a 3.5 diskette using WinXP. I put some personal data in a '.xls' file on a 3.5 diskette and update it every now and then. The other day when I put the disk in to enter some new data, I received the following error msg: "book1.xls" cannot be accessed. The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding. My options at this point are "RETRY" or "CANCEL". I check the...

data range is too complex.
I am just changing over to excel 2007. . Most of my data will update to the embedded chart just fine but one tells me "the data range is too complex to be displayed. I don't know how to fix it -- Thanks for all your help. God Bless, Frank I had one like it and all I did to fix it was select the chart and right click on it, select data-> click on the icon at far right of Chart Data Range, use the mouse to select the data range, click on the icon at far right then OK. Note: If there are multiple ranges and the ranges are not adjacent, select the first range then hold the Ct...

last added line always visible in listbox
Hello I add new lines to listbox using AddString(); I want that last added line to listbox was visible when I put many many new lines. Now I always see top of list even I add many lines. Is some simple way to do this ? "tio" <tomjey@wp.pl> wrote in message news:1155909533.593851.160030@m73g2000cwd.googlegroups.com... > Hello > I add new lines to listbox using AddString(); > I want that last added line to listbox was visible when I put many many > new lines. > Now I always see top of list even I add many lines. > Is some simple way to do this ? > Maybe a...

Pulling data from individual files to master list
Hello, I've just been entering the world of Excel for the past few months, as I started a new job last year and my main duty is to bring the company into the 21st century (or even the late 20th, at this point). What I'd like to do sounds a little backwards, but I think it's the way to go, if it's possible: I'm creating individual sheets for our products, so that all the relevant info for, say, product A001 is shown on one sheet named "A001.xls". But I would also like to create a "master" list. I say "master" in quotes because it's not r...

Transfer data from one sheet to another
Did I stump you guys? This is a hard one! Here is the senario: Worksheet one has a cell with a drop down menu with the numbers 1-10 in it. Below the drop down menu are two columns labled 'account number' and 'method' each with many fields available for info below them. Worksheet 2 is a database. It has many columns labled 'account number', ' method', 'date', 'cycle' etc. The cycle field is what contains the numbers 1-10 of the previous worksheet. I need a way of making it so that when I choose 1-10 from the dropdown menu on the fir...

export data to excel
Hi im quite new to using visio. im currently doing an office plan and using the "office space" shape to determine the amount of area in a certain office area. What i wanted to know was if there a way to export the office space amount it has calculated (eg 57sq metres) dynamicly to excel so that when i resize the area on the plan the spreadsheet updates. thanks Yes, you can use the "Link to Database" functionality in Visio to export and link the database values. Highlight the shape Click on Tools \ Macros \ Visio Extras \ Link to Database Set the database source to ...

Charting data by date
Hi, I'm new to Excell 2002, and have made worksheets with data refreshed from the Web. However I can't get it to show up on a chart with dates on the X axis. Have the axis formatted OK, but how does it collect its data? Have tried a simple worksheet with manually entered data & the dates entered in column A, but still can't get a chart to work. ...June. Jon Peltier has a charting tutorial that may help you get started: http://www.peltiertech.com/Excel/ChartsHowTo/index.html When you manually enter data and dates, what problems do you have with the chart? June wrote: >...

Transfer of Data
I've used the db1 file. Loaded it to the hard drive, clicked it on from where I stored it and Money begins to 'work' then gets hung up. Must have tried 20 times today. Have also tried restoring from the bak ext. No luck. Is it possible to transfer from 1999 to 2003? Sorry for the thread error. I'll make a copy of my file with the .mny extension and see if that works. Is what I'm attempting even possible. ('99 to '03?)? In microsoft.public.money, Lynn Ringuette wrote: >Sorry for the thread error. I'll make a copy of my file >with the .mn...

CRM
Hi, I've got a problem to import data into drop down list (CRM). I alreday try cdf tools. But, when I execute the "p_cdf_PopulateStringMap" nothing happens! The cdf_StringMap is not completed... Help! someone has an idea to resolve my problem ? thanks regards, When you have installed the Data Migration Framework, you have also got 2 executables. You can (probably) find them in this folders: c:\program files\microsoft crm data migration tool\cdf c:\program files\microsoft crm data migration tool\cdfmigration one of the tools is called initializecdf.exe. Run this tool and...

Multiple Pivot Tables based on the same data
I have two pivot tables based on the same source. However, each table is unique in how it shows the date. The first shows all thirty days of the month. The second groups the date in 10 day increments. The first table is summed data while the other is averaged data. When I try to set up the second table to group, it also groups the first table even though its' on a different sheet. I remember being able to do this in version 2003. What have I overlooked? Thanks Phil In xl2003, I could create the pivottables as separate entities. I created the first PT and then sta...

averaging data
Hi, I have a program that collects data throught out the day and populates an Access table. This goes on everyday. I collect various data but the important ones to mention here is the date and percent error. I wish to show the average error for each day in a query. Is this the best way and if so how does one do this. Cheers Gerry GC, Create a "Totals" query (in query design mode menu... View/Totals). You would Group on the date value, and probably Sum the value data. This should yield totals for the day... from which you can calculate the percentages of...

Can you create calendars in Excel from data?
I am looking for a way to use the data entered in Excel to make an actual calendar. Is this possible or does Excel not have this functionality? ...

ow to create comparison chart using text as value data?
lplease help me. I am trying to set up a comparison chart of 3 computers cost and features! I cannot figure out how to use the features as values on the x or y axis says data range is to complex so cant select rows or columns. I am trying to enter the features as my source data too bungled dont know what to do term project is now overdue and I am stressing out!~ Dont want to fail. please help me!do i have to redo the whole chart or what any suggestions and demonstration would be deeply appreciated. thanks, Dee Johnson The first thing you need to do is think how the chart should look. Wh...

Validate Purchase Order Date
We were able to enter a PO (against a Project) dated 2004, several years before we started on GP, so I'd guess that (because this is non-posting) the date isn't validated against the fiscal periods that have been set up. However, this lead to a Project Begin Date of 2004, and caused an error when we tried to enter a fixed fee amount against the project "Fiscal information does not exist for the date range. The fiscal year must exist in order to create the periodic records". The only way I found to fix this was to go into SQL table PA01201 and fix the Project's b...

Import data from a web page
I've read the suggestions about this topic and none of them work. The only thing I am able to do is manually copy and paste data from a website to my Excel worksheet. In the past, with the previous version of Excel I had a web query that worked but now I just get an error message that says the query retrieved no data. Following the instructions in the error message window yields no results. Has anyone else had this problem and arrived at a solution? What version of Excel, previous version is not descriptive of what you had what you have now. And it possibly does not matter. In...

Querying XML data
Hello, Using SQL 2008 I'm taking xml string from a VARCHAR(max) datatype column and inserting into XML datatype column then want to query it. Doing the above like so: DECLARE @XmlSourceTable TABLE (RecordId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, XmlData XML NOT NULL ) INSERT INTO @XmlSourceTable (XmlData) SELECT TOP 1 CONVERT(XML,REPLACE(REPLACE(CSR, '&lt;', '<'), '&gt;', '>'),2) FROM CDATA The xml looks like this after inserting here is just a small part: <SoapGetCSRReturnResponse xmlns="http://temp...

I want to show chart data in order of bars, not row names
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am trying to show a chart that has about 20 values, and I have sorted the source data so that I have the highest value items at the top and the lowest value items at the bottom. For example, if rows were Apple, Banana, Guava, Orange, and Yucca, I have sorted the data in order of the greatest value items (20 Guavas, 10 Oranges, 5 yucca, 2 bananas, and 1 apple). Yet the chart (a bar chart) only seems to let me display in either ascending or descending value by food name (either Apple to Yucca or Yucca to Apple...

how to activate a cell if it contains data
I've created a Pvt Table which contains 6 fixed columns and it will show data depends on how many columns I wanted to see. In addition, I've created another set of columns to copy data shown on the pvt table. So here is my question, is there a way to activate cells with data on the Pvt Table? Here is an example: If I wanted to see 4 columns of data on the Pvt Table: Column A Column B Column C Column D Column E Column F Row1 $27 $30 $49 Total My other Set of columns should appear like this: Colum...

manipulating data from Find dialog in web browser ActiveX control.
Hello everyone, I would like to know how can I get access from MFC to the text that was entered in the Find dialog of the Microsoft web browser ActiveX control. Documentation is pointing to IHTMLOptionsHolder interface but I cannot get reference to it (I am getting E_NOINTARFACE) from any of the available interfaces in my code. Note, I do not have problem to query other standard interfaces exposed by ActiveX control (ex.: IHTMLDocument2, IOleCommandTarget, and IDispatch). I would appreciate any help regarding this matter. Best Regards, Andrew ...

Looking for Missing Data
I have a spreadsheet on employee deductions. Since it's the beginning of the year I need to verify if every employee has a specific deduction and if it has been calculated correctly. My report is laid out as follows: EE Name Salary Deduction Code Deductiont Amount Sam $2000 A $120 Sam $2000 B $80 Sam $2000 C $10 Sam $2000 D $17 Sam $2000 W $43 Dave $2500 A $150 Dave $2000 B $80 Dave ...

Mail Merge with external data source
I am attempting to have an xml mail merge document automatically connect to an external database using the w:query and w:datasource settings. When I open the xml doc, Word 2008 gives me an error that it cannot find the data source. This works fine with Office 2003 and 2007 for windows, but I am having issues trying to get it to work with 2008 for mac. Is this even possible on the mac platform? Thanks for any input. Hi Myron: A data source for a mail merge operation can be an Excel sheet, the Office Address Book, a FileMaker Pro database, or a Word document. Sorry, nothin...

Transfer of Data Between Sheets
Hi All. I hope I can explain this clearly. Here is what I would like to do: I have created a large worksheet that contains, in part, all the projects I'm doing for different departments. Say, for example, I get a new project from sales. When I enter the new project details on the main worksheet and I type in "sales" under the "department" column, I want the whole row of data for that project copied to a new worksheet (within the workbook) titled "sales." If I get a project from legal, I want the data transferred to a worksheet called "legal" an...

copying data from folder to folder
I have folders on a server, one folder for each branch office. Within each branch folder, I have several folders labeled Warehouse, etc.. In the warehouse folder, I have several excel workbooks (one for each month, etc.), each comprised of 8 to 10 worksheets. If I add lines in one branch warehouse worksheet, can I have the other branch warehouse worksheets set up to copy that data? TIA You can have a "Master" workbook Then link the others to that one, so it can be updated when opened. "RPW" wrote: > I have folders on a server, one folder for each branch office...

Extracting data from one column
I have a column with the following data that I would like to extract into three columns: Current data in one column ------------------------------------- Berlin, Germany - 3,337,000 Riyadh, Saudi Arabia - 3,328,000 Desired result in three columns ------------------------------------- City Country Population Berlin Germany 3,337,000 Riyadh Saudi Arabia 3,328,000 Thanks, Craig Try the Text to Columns Wizard: Data/Text to Column. regards, Hans >-----Original Message----- >I have a column with the following data that I would like >to extract into ...

Converting Graph data links to values using code
Excel 97 I create charts using a workbook with templates and change the reports using dynamic data ranges. One worksheet can hold several graphs. I then copy the charts to another workbook for distribution. I extinguish the data series links by manually converting them to values (select graph line, F2, F9, Enter) etc., graph by graph. A painstaking job at times. Can this be automated with code? TIA WSF Hi have a look at http://peltiertech.com/Excel/Charts/chartvba.html#DelinkCht -- Regards Frank Kabel Frankfurt, Germany WSF wrote: > Excel 97 > I create charts using a workbook w...