Combining Two Range of Entries into One

Hiya all..

I'm hoping that somebody could help me out.  I have two sets o
entries:

Set 1 ( A1:A10 )
item 1
item 2
item 3

Set 2 ( B1:B10 )
data 1
data 2
data 3
data 4

What I am trying to do is to have a certain cell (say C1) have 
dropdown list  containing entries from both sets.  The formula for th
valid data input would be similar to:

offset(a1,0,0,counta(a:a),1) + offset(b1,0,0,counta(b:b),1)

So far, I have no luck in getting this to work.  Any suggestion o
alternative approach to this would be very much appreciated.

:

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/12/2003 4:37:42 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
213 Views

Similar Articles

[PageSpeed] 50

Hi JaGGer,

An alternative approach would be to concatenate the two 
ranges and put that list somewhere out of sight and create 
the dropdown from the new list.

For example in AA1, formula =A1&B1. Then use AA1 as the 
source for the dropdown.

Biff

>-----Original Message-----
>
>Hiya all..
>
>I'm hoping that somebody could help me out.  I have two 
sets of
>entries:
>
>Set 1 ( A1:A10 )
>item 1
>item 2
>item 3
>
>Set 2 ( B1:B10 )
>data 1
>data 2
>data 3
>data 4
>
>What I am trying to do is to have a certain cell (say C1) 
have a
>dropdown list  containing entries from both sets.  The 
formula for the
>valid data input would be similar to:
>
>offset(a1,0,0,counta(a:a),1) + offset(b1,0,0,counta
(b:b),1)
>
>So far, I have no luck in getting this to work.  Any 
suggestion or
>alternative approach to this would be very much 
appreciated.
>
>:)
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~View and post usenet messages directly from 
http://www.ExcelForum.com/
>
>.
>
0
biffinpitt (3172)
11/12/2003 5:56:31 AM
Hi

Here's one way to accomplish it:

In e.g. K1 enter the formula:

=IF(ROW()-ROW($K$1)<COUNTA(A:A),OFFSET($A$1,ROW()-
ROW($A$1),0),IF(ROW()-ROW($K$1)<COUNTA(A:A)+
COUNTA(B:B),OFFSET($B$1,ROW()-ROW($B$1)-COUNTA(A:A),0),""))

Drag K1 down with the fill handle to K20.

K1:K20 will now contain Set1 + Set2 + a number of cells with "".
The (Set1 + Set2)-part has COUNTA(A:A)+COUNTA(B:B) cells.

To get the range for the drop down box use:

=OFFSET(K1,0,0,COUNTA(A:A)+COUNTA(B:B))

-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.

"JaGGeR" <JaGGeR.wrkom@excelforum-nospam.com> skrev i en meddelelse
news:JaGGeR.wrkom@excelforum-nospam.com...
>
> Hiya all..
>
> I'm hoping that somebody could help me out.  I have two sets of
> entries:
>
> Set 1 ( A1:A10 )
> item 1
> item 2
> item 3
>
> Set 2 ( B1:B10 )
> data 1
> data 2
> data 3
> data 4
>
> What I am trying to do is to have a certain cell (say C1) have a
> dropdown list  containing entries from both sets.  The formula for the
> valid data input would be similar to:
>
> offset(a1,0,0,counta(a:a),1) + offset(b1,0,0,counta(b:b),1)
>
> So far, I have no luck in getting this to work.  Any suggestion or
> alternative approach to this would be very much appreciated.
>
> :)
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
leo.heuser (111)
11/17/2003 8:18:42 AM
Addendum to clarify:

"Leo Heuser" <leo.heuser@adslhome.dk> skrev i en meddelelse
news:%23Cj1QASrDHA.3612@TK2MSFTNGP11.phx.gbl...
> Hi
>
> Here's one way to accomplish it:
>
> In e.g. K1 enter the formula:
>
> =IF(ROW()-ROW($K$1)<COUNTA(A:A),OFFSET($A$1,ROW()-
> ROW($A$1),0),IF(ROW()-ROW($K$1)<COUNTA(A:A)+
> COUNTA(B:B),OFFSET($B$1,ROW()-ROW($B$1)-COUNTA(A:A),0),""))
>
> Drag K1 down with the fill handle to K20.
>
> K1:K20 will now contain Set1 + Set2 + a number of cells with "".
> The (Set1 + Set2)-part has COUNTA(A:A)+COUNTA(B:B) cells.

1.    Choose Insert > Name > Define
2.    Name:    DropDownRange
3.    Refers to:    =OFFSET($K$1,0,0,COUNTA($A:$A)+COUNTA($B:$B))


To get the inputrange for the drop down box, set its
inputrange to DropDownRange.


-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.



0
leo.heuser (111)
11/17/2003 4:11:26 PM
Reply:

Similar Artilces:

Combining Rules
Is there a way to write several rules and then combine them into a single rule? Well, without writing a new rule. This selection is not available in the rules wizard so is there a way to create a rule without using the wizard? I guess that's really two questions. Thanks "=?Utf-8?B?Unlhbg==?=" <Ryan@discussions.microsoft.com> wrote in news:32A1151C-5E94-45B0-94AF-E4DE9467D0DE@microsoft.com: > Is there a way to write several rules and then combine them into a > single rule? Well, without writing a new rule. This selection is not > available in the rules w...

Exclude one folder in a filesearch
I Have this simple macro that will list all the files in a folder including all the subfolders Sub file_list() With Application.FileSearch ..NewSearch ..LookIn = "C:\My Documents\" ..SearchSubFolders = True ..Filename = "*.*" ..FileType = msoFileTypeAllFiles If .Execute() > 0 Then For I = 1 To .FoundFiles.Count Cells(I, 1) = .FoundFiles(I) Next I Else Cells(I, 1) = "No files Found" End If End With End Sub The folder i am looking in being, in this case being C:\My Documents\ has many sub folders. I want it to list the files in all but...

User one the domain DO NOT receive....
HI One of the users in my domian do not receive mail from a specific user outside. the person phoned and send a mail to her and CC me in. I did get the mail but the manager did NOT get it. How can this happen....pls help guys..excg2003andxp PRO Well, what does message tracking show? Does it show the message being delivered to both? What do the smtp protocol logs show? Does your user show up in the rcpt to: line? Are you positive that this user doesn't have any special views applied? -- Ben Winzenz Exchange MVP MessageOne Read my blog! http://winzenz.blogspot.com http://feeds.f...

Empty Range
Hi, I'm trying to do a function who delete all the row of a range I pass is argument and will copy them to an other sheet. Here is my function : Function RangeToDelete(ToBeDelete As Range, SheetsNameCopy As String) If WorksheetFunction.CountBlank(ToBeDelete) = 0 Then Selection.EntireRow.Copy Sheets(SheetsNameCopy).Paste Selection.EntireRow.Delete End If End Function I want to check if the range is empty first so I don't have an error cause I can't delete something that is empty. But this function doesn't work since the CountBlank is not workin...

Dynamic range across sheets and Advanced filter
Hi, I have two sheets of data with same structure (data more than one sheet can carry), and want to use advanced filter to copy data to a new location. How to make advanced filter List Range includes these sheets? Thanks. Regards, Angus Hi Angus You can't make the range for AF span 2 worksheets. You will need to carry out your extraction form each sheet separately, using the same criteria, then combine the 2 extracts to another sheet. -- Regards Roger Govier "Angus" <Angus@discussions.microsoft.com> wrote in message news:0B14E27C-F722-4636-B82D-AE98903BAC9B@mic...

Inserting a row after every two rows in a list of points
I have a list of points in an excel file that is as follows: 0.195062 -1.429289 0.195062 -1.429289 0.219444 -1.437452 0.219444 -1.437452 0.243825 -1.445616 0.243825 -1.445616 0.26822 -1.457892 0.26822 -1.465994 0.292601 -1.479351 0.292601 -1.488074 Is there a quick way to make the file look like this: 0.195062 -9.086782 0.195062 -9.587177 0.219444 -9.290192 0.219444 -9.775616 0.243825 -9.497057 0.243825 -9.968761 0.26822 -9.704681 0.26822 -10.163868 0.292601 -9.910387 0.292601 -10.358173 which basically inserts a blank row after every two rows of dat points. This is only the first te...

Two Default SMTP Virtual Server?
configuration info: 1-Ek23 SP2 server (old); cluster E2k3 SP2 server (new) For give my ignorance (brain cloud), but how many default SMTP virt servers can a single organization have? I ask b/c we are about to migrate from our old server to a new cluster setup. Today I added the EVS to the SMTP Connector as a local bridgehead server, along side the old server. Everything seemed to work fine until I did a test failover and then my old server rebooted and generated this log entry: Event Type: Warning Event Source: USER32 Event Category: None Event ID: 1076 Date: 4/4/2007 Time: 12:52:31...

Repeat same email one at a time?
I need to send the same email with an attachment to many people, but one at a time because the attachment will change. What is the best way to do this. I thought of resending it each time, but isn't there a better way? Thanks for any help. You can use Signatures to write the email and then just use Insert>signature on the menu and add the attachment. Signatures do not have to be signatures and can be as long or short as you want. The alternate method is to use Mail Merge. "Barbara I." wrote: > I need to send the same email with an attachment to many people, bu...

Publisher 2003 hyphenation can't be changed for one word
Hello: I'm trying to work on a newsletter, in a three column format that needs hyphenation turned on, so the justified text looks better. However, there is one word that it wants to hyphenate (the word is eScrip, a company name), which should not be hyphenated. I've seen older articles in this NG suggesting various work-arounds, including turning hyphenation off for the whole story, changing the font size, inserting carriage returns, and changing the spelling of the word itself. However, none of this is acceptable. Isn't there a way to select one word, and tell Publisher *not*...

auto formula fill after data entry
what is the trick to have a formula fill down after a new row of data i entered. for example if i wanted to calculate miles per hour, i woul have three cells in a row; miles, hours and the formula of cell1/cell2 i want the formula in cell3 to automatically fill down when i ente values in cells 1 and 2 in each successive row. thanks in advance, joe -- Message posted from http://www.ExcelForum.com The following MSKB articles explain how Auto Extend works: XL2000: How Auto Extend List Behavior Works http://support.microsoft.com/?id=231002 XL2000: Entering Data in List May Not Extend Form...

how do i change chart legend entry in Excel?
Now it says "series 1" or similar and I can't figure out how to change it to the description that exists in a particular cell. Click once on the chart (activate it). The standard toolbar should show an option called "Chart". Click on "Chart" -> "Source Data". In the "Source Data" dialog box go to the "Series" tab. You can reference the cell that you want for the legend description via the "Name" input area. Or, you can type the name into that area to manually input it. -- John Mansfield "basm...

Help! need to fit 4 - 3.75"x5.25" cards (all different) on one page
Hi, I am new to ms publisher 2003, and I am trying to create a document that does the following: - about 50 unique thankyou cards - fit 4 or so on one page Thanks! When you say 'unique' do you mean you are Merging data and/or pictures to the cards 4 to a page? A little more information please? -- The US should free all those illegally held prisoners they are torturing, abusing and denying human rights being held at Guantanamo Bay. Hi, Each card will be typed manually, and I would like to fit for of these cards on one page/sheet of paper (to save paper). I will then cut ...

Synchronise two combo boxes
I am trying to synchronise two combo boxes on a form but get no list in the second. Details are:- tblRepairCategory RepairCategoryID (Primary Key - Autonumber) RepairCategory (Text) tblRepairType RepairTypeID (Autonumber) RepairCategoryID (Number - Long) RepairType (Text) BuyPrice (Currency) SellPrice (Currency) These two tables are linked by RepairCategoryID - One to Many. My form and combos:- FormName = DependentCombos First Combo Box Name = Categories RowSourceType = Table/Query RowSource = tblRepairCategory ColumnCount = 2 ColumnWidths ...

Adding same people to two distribution lists
In Outlook 2003, we need to add the same 19 people to two different public distribution lists in the Global Address List every month. We then need to send an e-mail message to those same 19 people. Is there any way to do that by typing the 19 names one time instead of three times? -- kenelder You are repeating this with the same people, every month? Once the admin creates the DL in the GAL it should 'stick'. What version of Exchange? There are ways to import a list of addresses. You can paste the addresses from the same list into a message to send them a message. Depending ...

Guaranteed 1,000,000 visitors every month and one website. Absolutely Free
Yes! Guaranteed 1,000,000 Visitors and one website, it's absolutely Free! This system is FREE http://www.clickaudit.com/goto/?51447 Quick and easy sign-up... get your own viral webpage in under 5 minutes! This "Viral Marketing" system works for you around-the-clock to bring you traffic! The viral nature of this system ensures that your traffic will exponentially increase! In less than 5 Minutes you can have your own Free Viral Marketing mini site.... and then watch your traffic EXPLODE !! Visit the Link below, http://www.clickaudit.com/goto/?51447 ...

Remove tilde from entries
Hello, I have a long list of names and some of them have tilde's over certain letters in their names. I need all of those tilde's removed but it doesn't seem like the Find/Replace is going to work in this instance. Can anyone help? Thank you! I'm so sorry - It's not a tilde. It's the accent mark that's on the tilde key that is over these spanish names. Can anyone tell me how to remove the accent over letters? Thank you! "TxWebdesigner" <beverly@beverlylanedesigns.com> wrote in message news:O%23gp7DH0HHA.1184@TK2MSFTNGP04.phx.gbl... &...

Excel 2003 -- Problem selecting link range within an autoshape.
In a multitab worksheet, we have a rectangluar autoshape on each sheet. The rectangluar shapes are not moved or resized with cells. The shapes are set to no fill. On any sheet, if you want to select a range within the rectangle on that sheet, there is no problem. But if you want to link from one sheet to another -- i.e type = for a cell's contents and then go to another sheet to select a cell or cells -- you are not permitted to select a cell within that other sheet's autoshape. One may select a range that overlaps the autoshape, however. One can also manually enter the range,...

How do I print one image (say page 1) twice on the same page?
Hello, does anyone know if Excel offer the print option of printing 2up? Meaning, printing the same page twice on one sheet (ie: page 1 printed twice -perhaps landscape, side-by-side, and 50% of course. I have tried and I do not know if Excel is capable of this. Any assistance is appreciated. Hi there I had exactly the same question a few months ago. The simple answer is Excel can't do this easily. So a workaround is required. What I did for my workbook was to add an extra worksheet on which I copied what I wanted to print. I called the worksheet "Double". The worksheet ha...

Mailbox management policy: runs only one of three policies
Hello all, An Exchange 2003 server on a Windows 2003 server in a w2k domain environment. I have a few recipient policies defined (in System Manager -- Recipients -- Recipient Policies), each has its own priority. The Default Policy resides in the bottom of the list and has priority=Lowest. Three of the policies are Mailbox Management ones. The problem is, when the scheduled time comes, only the policy at the top of the list (priority=1) runs, but it runs three consecutive times during the time window. The other two never run, unless I move them up to the top of the list. Why does it behave ...

How do i get my multipage publication to print on one page?
I created a custom doc that is 16" x 16". I would like it to scale to fit to an 8.5 x 11 sheet of paper for proofreading. How do I do this? Print to a PDF. There are free converters around. Some folks recommend www.primopdf.com If you have Publisher 2007 there is a Microsoft PDF converter add-in. http://www.microsoft.com/downloads/details.aspx?FamilyId=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Erin Heim" <Erin Heim@discussions.microsoft.com> w...

data range
How do I choose every ninth row to be my Y data on a chart? I have 15,000 rows so picking one individually will be time consuming. Thank you. Holly Jon Peltier has instructions for this in his Excel Charting FAQ article: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon024 Holly wrote: > How do I choose every ninth row to be my Y data on a > chart? I have 15,000 rows so picking one individually > will be time consuming. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

how to join two workbooks
I'm probably being extremely stupid but i'm new to excel and i'm face with the following problem: I've got two separate spreadsheets with company information. What need to do is somehow look at both and create another spreadsheet whic contains only the data that is not in both. i.e. list only the employees (described by their employee id) tha aren't present in both spreadsheets. All help gratefully recieved.. -- Message posted from http://www.ExcelForum.com You should visit Chip site at www.cpearson.com. He has lots of stuff on comparing and joining workbooks Best w...

comapring two worksheets
i would like to compare the contents of two worksheets is this possible thank you depends what your need to do, if looking for "math" changes its' one thing, if your sheet has different number of rows and columns then that's much larger. if you have 2 sheets exactly the same, you can have a 3rd sheet do a comparison of the cells on third sheet a1 of sheet 1 minus a1 of sheet 2 will tell you if the math results in a different value. Before you copy the A1 formula to the rest of teh sheet, use condtional formatting to highlight the background if the value is not ze...

Various Excel files into one file
Is there a way to make various Excel Spreadsheet be move to be access to only one Spreadsheet without copying and pasting each file into the that one spreadsheet? Jose Noriega - > Is there a way to make various Excel Spreadsheet be move to be access to > only one Spreadsheet without copying and pasting each file into the that > one spreadsheet? < Edit | Move Or Copy Sheet - Mike www.mikemiddleton.com Hi see: http://www.rondebruin.nl/copy2.htm -- Regards Frank Kabel Frankfurt, Germany "Jose Noriega" <Jose Noriega@discussions.microsoft.com> schrieb im...

Changing more than one part of Word simultaneously
Hi, everyone. Got a question I'm hoping someone can help with. I have been given a form which is a Word Template and asked to add in functionality that would take the information entered in certain places and have it automatically duplicate in the header of each page. Apparently this was possible in winxp/office2003 but now in vista/office2007 these templates don't work as they should. The code that seems to create this effect seems to be xml but I have no experience with this and so am wondering if there's any way of producing this effect some other way - either...