Create Named List (range) based on check list

Column A is a list. Column B has =93x=94 in some cells. I need to create a
named range, using a formula, that is populated with items from Column
A when there is a corresponding =93x=94 in column B.
I cannot use a macro in this workbook.
I=92ll be grateful for any help
Robert
0
11/8/2011 8:37:58 PM
excel 39879 articles. 2 followers. Follow

4 Replies
342 Views

Similar Articles

[PageSpeed] 30

Robert Hatcher submitted this idea :
> Column A is a list. Column B has “x” in some cells. I need to create a
> named range, using a formula, that is populated with items from Column
> A when there is a corresponding “x” in column B.
> I cannot use a macro in this workbook.
> I’ll be grateful for any help
> Robert

Good luck!

You'd be further ahead to define a dynamic range to be populated with 
the list using one of the lookup formulas. In this case, though, it 
would be easier if the list was in ColB and the 'flag' was in ColA. In 
any event, you'll probably end up using an array formula IMO.

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
11/8/2011 9:51:17 PM
On Nov 8, 2:37=A0pm, Robert Hatcher <rhatcher...@gmail.com> wrote:
> Column A is a list. Column B has =93x=94 in some cells. I need to create =
a
> named range, using a formula, that is populated with items from Column
> A when there is a corresponding =93x=94 in column B.
> I cannot use a macro in this workbook.
> I=92ll be grateful for any help
> Robert

insert>name>define>name it rangex or whatever. In the refers to box
type
to use a1:e & the row in col b with x. Look at the OFFSET function.
=3DOFFSET(Sheet12!$A$1,0,0,MATCH("x",Sheet12!$B:$B,0),5)
0
dguillett11 (103)
11/8/2011 10:30:12 PM
On Nov 8, 5:30=A0pm, Don Guillett <dguille...@gmail.com> wrote:
> On Nov 8, 2:37=A0pm, Robert Hatcher <rhatcher...@gmail.com> wrote:
>
> > Column A is a list. Column B has =93x=94 in some cells. I need to creat=
e a
> > named range, using a formula, that is populated with items from Column
> > A when there is a corresponding =93x=94 in column B.
> > I cannot use a macro in this workbook.
> > I=92ll be grateful for any help
> > Robert
>
> insert>name>define>name it rangex or whatever. In the refers to box
> type
> to use a1:e & the row in col b with x. Look at the OFFSET function.
> =3DOFFSET(Sheet12!$A$1,0,0,MATCH("x",Sheet12!$B:$B,0),5)

Thanks Don, I will work with that.  Garry, yes, Ill make it dynamic
range, but Ill wait until I have it working first. I find that If I
try starting with a dynamic range at first it gets a bit unwieldy if
I'm trying to work out bugs.
Thanks
Robert
0
11/9/2011 1:51:32 AM
Robert Hatcher expressed precisely :
> On Nov 8, 5:30 pm, Don Guillett <dguille...@gmail.com> wrote:
>> On Nov 8, 2:37 pm, Robert Hatcher <rhatcher...@gmail.com> wrote:
>> 
>>> Column A is a list. Column B has “x” in some cells. I need to create a
>>> named range, using a formula, that is populated with items from Column
>>> A when there is a corresponding “x” in column B.
>>> I cannot use a macro in this workbook.
>>> I’ll be grateful for any help
>>> Robert
>> 
>> insert>name>define>name it rangex or whatever. In the refers to box
>> type
>> to use a1:e & the row in col b with x. Look at the OFFSET function.
>> =OFFSET(Sheet12!$A$1,0,0,MATCH("x",Sheet12!$B:$B,0),5)
>
> Thanks Don, I will work with that.  Garry, yes, Ill make it dynamic
> range, but Ill wait until I have it working first. I find that If I
> try starting with a dynamic range at first it gets a bit unwieldy if
> I'm trying to work out bugs.
> Thanks
> Robert

Yeah, I get that! Best wishes...

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
11/10/2011 1:56:26 AM
Reply:

Similar Artilces:

Swing Chart with dynamic Range
I would like to create a swing chart (stock chart) with a dynamic range that grows as more data is added. Any ideas? Thank You Joe Hi, For information on the dynamic ranges have a read of Jon Peltier's page http://peltiertech.com/Excel/Charts/Dynamics.html Depending on which stock chart you want you will need 3 to 5 named ranges. Cheers Andy Joseph Sayah wrote: > I would like to create a swing chart (stock chart) with a dynamic range that > grows as more data is added. > > Any ideas? > > Thank You > Joe -- Andy Pope, Microsoft MVP - Excel http://www.andyp...

Creating Custom views
Hello, I'd like my support reps. to be able to create custom views for accounts, contacts, cases etc... I gave them the rights to do so in the policies (create, read and write custom views for the organisation) but they don't have access to this part in the Settings... What's wrong there? Thanks Eric ...

Resize check box and have "x" instead of a check
Is it possible to resize the size of the check box? I know I can resize the text portion of a check box, but I would like to make the box larger. I would also like to change the check mark to an 'X', is that possible? Thanks Tim Tim, It is not possible to change the size or use a 'X'. There is a way to simulate. Use 2 Label. 1: Borderstyle: 1 Name: Chk1 Caption: "X" Font: The size you like Size: to fit the "X" Location: Where you like it to be 2: Borderstyle: 0 Name: Lbl1 Caption: Your text Font: The size you like Size: minimaal...

creating a dropdown list in excel
i want to create a dropdown list in excell that will highlight a word when first letter is typed To make a dropdown first highlight the list you wish and give it a name (anthing you want eg listX). Do this by going Insert>name>create. After doing this go to the cell where you wish for the dropdown list to appear, and go Data>Validation. When the box comes up, in the box next to "allow:" secect "list". In the box below that says "source" type "=(your list name). For example "=ListX" Your dropdown is then complete, so click ok. hope ...

How do I setup a name, home address and phone to automatically at.
when I used Outlook Express I had setup a name, home address and phone number that automatically appeared on my outgoing email. How do I setup the same for "Outlook" email which I recently purchased with Office 2003 Professional? Set it up in your signature, it's in the tools/options of Outlook. -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ Outlook & Exchange Solutions Center: http://www.slipstick.com "Robert C. Sollars" wrote: > when I used Outlook Express I had setup a name, home address and phone numb...

Removing Access 97 recently used file list
Is it possible to suppress the recently used file list from Access 97? I've just discovered that even if you hide the database window and lock it so that the user can't get in by holding down the shift key, the user is still able to open the whole thing, completely unprotected, simply by selecting it twice in succession from the recently used file list on the File menu. Seems absolutely ridiculous to me... It sounds, then, as though your database isn't properly secured, as that should have no bearing on how it opens. For Access 97, the MRU is stored as up to four...

automatically create chart upon selecting
I have a spreadsheet containing about 50 different financial ratios for 2002 thru the current month. I want to be able to click on the name of the ratio and have a chart automatically generate using all historical data thru the current month, thus including each month as it is added. Does anyone have any suggestions? On Wed, 29 Oct 2003 07:43:30 -0800, Jason <jason.loesch@zinpro.com> wrot= e: > I have a spreadsheet containing about 50 different > financial ratios for 2002 thru the current month. I want > to be able to click on the name of the ratio and have a > char...

Is this possible to create a company from exisiting Company but with some of data
Hello Folks, Happy labor Day !!!!!! I have a very strange situation atleast for me. I have a company and now I need to create another company out of that and move some of the data but not all. Let's say I have 1000 items existed in a company and then I create another one and wish to move 300 items out of 1,000 and these 300 will not be in any sequence. By moving 300 items also means that that I want to move all other data related to these items like BOMs, GL Accounts, AR, AP, Sales Orders and Purchase Orders as well. Is there a way to slice the DB using any Import/Exp...

Local and Internet name...
Hello, I got a problem with a exchange 2003 SBS. The domain name ( let say 123.org ) is by a provider , I configured pop3 connector to pik up email, this works fine. As some 123.org users are in my lan and others around the country, I got a problem when I try to send a mail to the users that are not configured in my server. The exchange server server answer me that the user is unknown. I heve certainely configured the server as as the FQDN 123.org name mail name and this is my problem. So my question is, can I change that and where. Thank you very much for any help. Jean-Pierre ...

Create a handwritten signature.
How do I apply a handwritten signature on a document? I suggest having the signiture in black on a white background. Scan it, save it as a TIF or JPG, insert the new file into your Publisher document. Bruce "AROBBINS" wrote: > How do I apply a handwritten signature on a document? The way the post is worded, Bruce, begs an answer like this: a pen and ink would be way to go. Sarcasm aside, your method is best. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Brucels" <Brucels@discussions.microsoft.com>...

how do i create a four line consumption schedule for economics
...

How do I create a calendar from data in excel?
I have two columns with dates in them and would like to display a calendar at the bottom of the page. Is this possible? Try a 3rd party application like WinCalendar... With this you can insert a Calendar into any excel sheet. You can also save daily appointments on a floating Calendar that later display on the inserted calendar. Refer to http://www.wincalendar.com/ for more info. TG On Jul 31, 12:00 pm, kare7272 <kare7...@discussions.microsoft.com> wrote: > I have two columns with dates in them and would like to display acalendarat > the bottom of the page. Is this possible...

check posting crashes system
I have 2 users out of 15 who have issues when trying to post checks. One is payroll, the other is payables. When they post their checks, their machine crashes. It does not happen every time. We are GP10, service pack 2. They are both on XP. We have a shared reports dictionary. Reinstalling does not help. Any thoughts/ideas would be appreciated. Keith, No thoughts or ideas yet, but lots of questions to start: What are the specs on the machines? What is the approximate size of the batches? The whole machine crashes, not just GP? Are the posting journals set to go directly to...

Name not appearing in GAL for new mail but it is created on Exch55
Hi! I have Exchange 5.5, and I created a new custom recipient (properly created) last week. If I go on my computer and I want to create a new email, that name is not displayed in the Global Address List. That custom recipient is not "hide in the adress book" Anyone can help me? thx! -- Luc Turmel ...

How to create command button to save the dates and time
Hi, I wonder if anyone can help me. I need to know how to create a command button, when clicked it saves the spreadsheet along with the dates and time on my desktop. However, if that can't be done...is there a formula that lets u saves the spreadsheet along with the dates and time. Whenever I open the same spread sheet, the dates gets changed and my previous information of the dates are lost - I use [= NOW()]. How can i stop this? copy and paste special =now() as a value or delete =Now() and Insert a static date or time Current date Select a cell and press CTRL+; Current time...

Outlook 2000
I need to know how/if I can create a rule so that when I open an email it is moved to a second folder upon opening. I am trying to prevent 2 people from working on the same email at the same time. If there are other ways to do this, I am all ears. Thanks for the help. Jason You can set up a rule to forward mail to a recipient or a rule to move email to a folder based on key words or sender. So, I suppose that you could set up rules forwarding email from certain people to one worker and from others to another worker (you could also do this by key word). Hope this helps. >----...

Create Diffgram Based on 2 Different XML Files or Data Sets
I have an xml file that my application downloads on a periodic basis. I also have a dataset based on this that is used in the application. At present, the application takes the new downloaded xml, creates a data set from that and merges it with one in use. The ultimate goal is to have the update of the primary data set trigger change events based on only data that has changed (updates, additions, or deletions). I am thinking that I need to generate a diffgram based on the current data and the newly downloaded data and then apply that to the current dataset. Is this the propper approach...

alphabetizing by first letter if list
Is there a way to cause an existing to be organized by the first letter of each line? If each line is a separate paragraph, select all the items and choose "Sort." In Word2007 it's on the Home tab. Before Word2007 it's under the Table menu (but the paragraphs don't have to be in a table). If you have a table, the select one of the columns and choose Sort, and all the rows are listed in the order of that column. On Jan 1, 11:05=A0am, jakson <jak...@discussions.microsoft.com> wrote: > Is there a way to =A0cause an existing to be organized by the...

Auto populate form field based on selection
Hi, I have a form which is linked to a table(tblAntibody) for data entry. I have another table(tblCatalogPartNumbers) with no relationship to the first which contains all of our codes and has four coulmns CatalogNumber, UnqualifiedNumber,NIPartNumber, and PIPartNumber. All of these columns store thier data in tblAntibody. On the form CatalogNumber is a combo box that has it's selection criterial set to tblListCatalogPartNumbers. What I need to happen is when a user selects a catalog number from the combo box, I need the other 3 fields to auto populate from tblCatalogPartNumbers. Catalo...

spell check does not visually cycle through cells when run from macro?
I'm trying to recreate the same behavior in a macro that spellcheck has when the spellcheck button is pressed. The spellcheck does not cycle through the cells visually when run from a macro, so when there is a misspelled word, you cannot see the cell it is a part of. If anyone knows how I can get this behavior to work, it would be GREATLY appreciated. I did a record new macro, and got code similar to the following: Sub testSpelling() Range("F2:F500").Select Selection.CheckSpelling SpellLang:=1033 End Sub thanks, dan Dan, Add the fiollowing to your code as shown: ...

Referencing the name of a worksheet in a cell
hello excel gurus... I would like to have a string of text that incorporates the name of on the worksheet tab. I have a series of worksheet that are all very similar, it is just one word in the title that changes and that is what I will be naming the worksheet. Is there a way that I can have that cell reference the tab name, so that I only have to name it once and not twice. Thanks! -Bob Bob, Not sure if you want to get the cell from the tab name, or set the tab name from the cell value. So, if the former =MID(CELL("filename",A1),FIND("]",CELL("filename&qu...

Create/Modify Times are Wrong in the DB
I noticed that the Creaded on and Modified on times are off by 5 hours in the CRM database. I have my timezone set correctly - and the server is the correct time... Is this an issue or correct? Thanks! The dates in the DB are stored in GMT. "Kristina Ledford" <noemails@noemails.com> wrote in message news:e0rAtHzlDHA.644@TK2MSFTNGP11.phx.gbl... I noticed that the Creaded on and Modified on times are off by 5 hours in the CRM database. I have my timezone set correctly - and the server is the correct time... Is this an issue or correct? Thanks! I see - and depending ...

Linking two Subforms & showing name in place of ID?
This is a repost of a question that apparently didn’t go through on 5/1/2010. I have an unbound form with two subforms on it [SubFrm Actual Volunteers] & [SubFrm Volunteer List]. These two subforms are linked together using a text box on the main unbound form called [Event ID]. To do this, I followed the directions (half way down the page) on: www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp [SubFrm Actual Volunteers] : ~Master ~Has fields like [Volunteer ID] & [Event ID]. ~Also has a combo box [Name] with the row source: SELECT [Tbl Vol Info].[...

Update list box
I have a form to update a list box. Everything works well but the user must hit "enter" after imputing a new item or the value will not be updated in the list box. All edits to list box work, but new fields require the "enter" key to be pressed. How can I program the "enter" key in VB in a close button. So far I have tried the following: Private Sub Command4_Click() DoCmd.Restore Forms("frmVictim").lstVictim.Requery DoCmd.Close End Sub I'm unclear on what you are trying to do... Listboxes are predefined lists of items... why would...

no show all function in dropdown list in Pivot Table
Dear Sir, I'm using excel 2000 professional version. When I create new pivot table, everthing is fine but once I want to look at the specific item, I have no "show all" function in the dropdown list of item field. So that I have to unmark all items, instead of only unmark the "show all". How can I have "show all" function in this case? thanks In Excel 2000, and earlier versions, the Show All checkbox isn't available. You can use programming to hide or show the items -- there's sample code here: http://www.contextures.com/xlPivot03.html...