#### lookup in one sheet and insert in second if not found

```I have created a workbook with two sheets in it. I enter all my order
in sheet one and the sheet two totals them. The column on my sheets ar
as follow�.

Sheet one
STYLE            SMALL          MEDIUM       LARGE
100		1		1		1
101		1		1		1
100		1		1		1
102

Sheet two
STYLE            SMALL          MEDIUM       LARGE
100		2		2		2
101		1		1		1

I have used the SUMIF command in the second sheet, which is correct
Therefore my totals are correct. The only problem I am having is thi
��.that I have to enter the style numbers in the second sheet as well.
What I want to do is, to have some kind of look up function some where
So when I enter the style number in the sheet one, it should look a
the style numbers in the sheet two and insert it there if does not fin
it there.

Dal

--
Message posted from http://www.ExcelForum.com

```
 0
7/8/2004 7:03:40 AM
excel 39879 articles. 2 followers.

2 Replies
850 Views

Similar Articles

[PageSpeed] 33

```One possible approach ..

In Sheet1
-------------
You have in cols A to D, data from row2 down

STYLE SMALL MEDIUM LARGE
100 1 1 1
101 1 1 1
100 1 1 1
102 1 1 1
103 1 1 1
104 1 1 1
103 1 1 1

In an empty col to the right, say in col F

Put in F2: =IF(COUNTIF(\$A\$2:A2,A2)-1=0,ROW(),"")

Copy F2 down a "safe" max number of rows that would be expected
say, to F100

(This sets it up for us to extract
the unique "Styles" in col A into Sheet2's col A)

In Sheet2
-------------
Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!F:F,ROW()-1),Sheet1!F:F,0)),"",OFFSET(Sheet1!
\$A\$1,MATCH(SMALL(Sheet1!F:F,ROW()-1),Sheet1!F:F,0)-1,))

Copy A2 down to A100
(to cover the same number of rows as in col F of Sheet1)

Col A will return all the unique "Styles" from the list in Sheet1

For the sample data in Sheet1, Sheet2 will show:

STYLE SMALL MEDIUM LARGE
100 1 1 1
101 1 1 1
102 1 1 1
103 1 1 1
104 1 1 1

--
Rgds
Max
xl 97
---
xdemechanik <at>yahoo<dot>com
----
"daljit >" <<daljit.192co2@excelforum-nospam.com> wrote in message
news:daljit.192co2@excelforum-nospam.com...
> I have created a workbook with two sheets in it. I enter all my orders
> in sheet one and the sheet two totals them. The column on my sheets are
> as follow�.
>
> Sheet one
> STYLE            SMALL          MEDIUM       LARGE
> 100 1 1 1
> 101 1 1 1
> 100 1 1 1
> 102
>
> Sheet two
> STYLE            SMALL          MEDIUM       LARGE
> 100 2 2 2
> 101 1 1 1
>
> I have used the SUMIF command in the second sheet, which is correct.
> Therefore my totals are correct. The only problem I am having is this
> ��.that I have to enter the style numbers in the second sheet as well.
> What I want to do is, to have some kind of look up function some where.
> So when I enter the style number in the sheet one, it should look at
> the style numbers in the sheet two and insert it there if does not find
> it there.
>
>
>
> Dale
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
demechanik (4694)
7/8/2004 10:41:15 AM
```Just a tiny clarification ..

The same structure is assumed in Sheet2,
i.e. headers in row1, data from row2 down

--
Rgds
Max
xl 97
---
xdemechanik <at>yahoo<dot>com
----

```
 0
demechanik (4694)
7/8/2004 10:53:43 AM

Similar Artilces:

Excel
Hi, I need to rearrange columns on many sheets in a certain order based on on column header. For example, my columns might come into my spreadsheet as C,B,D,G,A,H,I,F , or as F,B,C,A,D etc. I want them all sorted say A,B,C,D - the rest of the columns is irrelevant. How do I accomplish this ? TIA What about the sort function Data/Sort prior to xl2007. I recorded a macro while doing this: Selection.Sort Key1:=3DRange("B4"), Order1:=3DxlAscending, Header:=3DxlGuess, _ OrderCustom:=3D1, MatchCase:=3DFalse, Orientation:=3DxlLeftToRight Presumably you could...

Can mutliple users acess an Access database at one time?
See that big white space? That's where you're supposed to put the question... Out of the box. Access is multi-user enabled. The recommended approach, though, is to split the application into a front-end (containing the queries, forms, reports, macros and modules), linked to a back-end (containing the tables and relationships). Only the back-end should be on the server: each user should have his/her own copy of the front-end, ideally on his/her hard drive. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Bigskin Pete" <Bigskin ...

Sheet name in another sheet's cell
Hi, I have 12 sheets for 12 months An other sheet is used for summary (sheet 13) I need to copy range of cells (part of a column) from each sheet and put in sheet 13. sheet 13 does not contain the names of the 12 previous sheets. Is there a way to tel the program: to get value in cell C170 from sheet 1 and put it in cell D50 in sheet 13 in a way that if i renamed one of the 12 sheets the sheet 13 will not be affected. Khalil D50: =Sheet1!C170 If the sheet is renamed the formula updates. -- HTH RP (remove nothere from the email address if mailing direct) "Khalil Handal" &...

Someone was able to insert Users of other domains in the CRM 4?
The domains are with all issues of trust made, but when I insert the error appears. When I writes the Login, fills the name. It is very urgent ... Help me!! Hi, What is the error you are receiving? "Vânia" wrote: > > The domains are with all issues of trust made, but when I insert the error > appears. > When I writes the Login, fills the name. > It is very urgent ... Help me!! Hi, The error is "An Error is ocucrred". Can you help me? Thanks "Niths" wrote: > Hi, > > What is the error you are receiving? > > >...

tracking sheet #2
I want to know how to set up my excel program to highlight a certain row if time has elapsed more than one week. I know it can be done, just not sure how to do it. See if this helps http://www.uncc.edu/sysdev/HowTos/Excel/Using%20Conditional%20Formats%20with%20Date%20Values.htm Judy Freed Systems Development UNC Charlotte "sarah" <sarah.moore@eglin.af.mil> wrote in message news:9EE8B3E7-BA7C-4D7F-9A2F-FCDE1FCA68B6@microsoft.com... > I want to know how to set up my excel program to highlight a certain row if time has elapsed more than one week. I know it can be don...

Taking 12 sheets making one summary
I have 12 spreadsheets, one for each month that has revenue an employee. Each sheet/month has 350 or so people and the rev, however Not all the months have the same people and rev. How do I search the 1 sheets for the names and add all the rev? Basiclly I want to make summary sheet... Good things is all names are unique... on all sheets the names start on a1 and the rev starts on b1. Thanks.. -- Message posted from http://www.ExcelForum.com Paste the data from the 12 sheets onto a new sheet. Do it so all data is stacked in the same columns. Sort by name. Use Data | Subtotal to sum rev...

How to compare large, simple excel sheets..
Does anyone know how to do that? I have to compare two sheets (5 columns by 2000 rows). The first three rows contain a number or two. Nothing more. The next two contain text (requirements actually) and I need to compare the contents of all the cells. Have any text changed? Been removed? Added? I know there are some plugins or stand alone products that can help me with this but those I have looked at have been a little too fancy. I am looking for something that can help me with this relatively simple sheet. Any help could save me weeks worth of work. Thanks! -- mag7417 ----------------...

Export Departments, Categories and Items from One Store To Import
Is there a way to export departments. categories and items from one store database to import to another store database? Both stores are using the same RMS 2.0 database, but we have done many department and category changes and added many other items and we want to transfer the items to another store, but not have to loose the transaction data or import the other store's transaction data. Basically we want to transfer the Categories, Departments and Items (with prices, etc.) from one store to another and overwrite any items that are existing in the old store database. Any thoughts ...

Insert Row When Column Amount Changes
This is a multi-part message in MIME format. ------=_NextPart_000_0080_01C55029.CC482AD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Here is an example of what I am talking about. A B C D=20 1 A B C D=20 2 2 41100 274 584=20 3 2 41100 274 625=20 4 2 41100 274 333=20 5 2 41100 274 102=20 6 2 41100 274 957=20 7 2 41100 274 362=20 8 2 41100 274 651=20 9 2 41100 274 611=20 10 2 41100 284 965=20 11 2 41100 284 336=20 12 2 41100 284 251=20 13 2 41100 284 362...

Anyone found a way to remove Outlook CRM client after a corrupted or deleted user profile?
Just curious if anyone has discovered a way to remove the Sales for Outlook after having either a corrupted or deleted user profile? So far I have had this twice and have had to re-image each computer in order to be able to re-install the client for a new user. Thanks. Carl Fisher The main thing you need to do is remove the MSCRM key from the registry (local mahchine\software\microsoft\mscrm). Then, you actually need to install and then uninstall. This will give you the cleanest removal. Matt Parks ---------------------------------------- ---------------------------------------- On Mo...

how can i insert a holegrame inmy publication
holegrame? if it's an image insert it like any other image...except if you mean "hologram" I kinda doubt you can make it work. As good as inkjet printers are, production of 'holegrames' is presently beyond their capabilities.. "marcus" <marcus@discussions.microsoft.com> wrote in message news:565097FC-9AC4-404F-A95D-395ADF21ED85@microsoft.com... > Where would you get a hologram? If you mean those shiny pictures that change when you look at them from different angles and look sort of 3D (like they put on a driver's license somewhe...

Merge two files based on one column
Hi, I have the following two files File 1: Column1, Column2, Column3, Column4, Column5 SSN1, First1, Last1, Street1, Zip1 SSN2, First2, Last2, Street2, Zip2 SSN3, First3, Last3, Street3, Zip3 File 2: Column1, Column2, Column3, Column4 SSN1, City1, State1, Country1 SSN2, City2, State2, Country2 SSN3, City3, State3, Country3 I need to merge the two files based on Column1. How do I do that? Vlookup only lets me add one column in file1. I want to merge both files based on SSN. You need to use 4 vlookups. One for each new column with the column number different in each Vlo...

how do I insert a counter into my flyer
I am making a voucher/flyer and I would like to add a counter so that I can make each one individual Thanks in advance Louis Make a file in Excel or as a text file in Notepad with the numbers you want in a single column, then use the MailMerge tools in Publisher to do exactly what you are requesting... GG Louis wrote: > I am making a voucher/flyer and I would like to add a counter so that I can > make each one individual > > Thanks in advance > Louis > ...

lookup or match?
on tab: Weeks NQCQ I have a table as follows Col A Col B Col C Week CQ Week NQ Week wk2 1Q10 Wk-12 Week 2 wk3 1Q10 Wk-11 Week 3 wk4 1Q10 Wk-10 Week 4 wk5 1Q10 Wk-9 Week 5 wk6 1Q10 Wk-8 Week 6 wk7 1Q10 Wk-7 Week 7 wk8 1Q10 Wk-6 Week 8 wk9 1Q10 Wk-5 Week 9 wk10 1Q10 Wk-4 Week 10 wk11 1Q10 Wk-3 Week 11 wk12 1Q10 Wk-2 Week 12 wk13 1Q10 Wk-1 Week 13 wk14 2Q10 Wk-13 Week 14 On tab "Week at a Glance", in cell B4 I will put in the current week, lets say I input wk8, on the same tab, I need a formula to look at the table ...

one solution to outlook "unable to connect to server"
For anyone getting the "unable to connect to server" error message ... By default, SBC DSL does not allow sending mail through smtp servers other than their own because they block port 25. A search of these groups and the web did not produce much reference to port 25 blocking as being the possible problem. I messed around with settings, different servers, different clueless support lines, and different email clients for hours. SBC phone support did not once mention that port 25 blocking could be the problem; I finally discovered that on my own, **buried in their very own online d...

how do i set up a new user in an old one in outlook?
i'm trying to set up my account where i work, but the one old who belonged to an old employee still show up and when i send emails, are on his name. i already set up my account when it asked me but still on the old employee's name. what could i do? carlodc22 <carlodc22@discussions.microsoft.com> wrote: > i'm trying to set up my account where i work, but the one old who > belonged to an old employee still show up and when i send emails, are > on his name. i already set up my account when it asked me but still > on the old employee's name. what could i do? ...

Negative Seconds
I have a list of times (splits from a rowing machine), like so: 02:36.3 02:28.5 02:38.7 02:29.4 and I want to plot them as an oscillator, above and below 02:30.0. In other words subtract each time from 02:30.0 (2 minutes 30 seconds). Excel has a problem when the result is negative. 02:30.0 - 02:28.5 is fine (= +00:01.5), but it cannot do 02:30.0 - 02:36.3 (which I would like to = -00:06.3). Can this be done? Thanks, Steve Steve, Try applying 1904 system (Tools -- Options -- Calculation). NB - this will adjust any dates you may have entered already. If applicable, copy them out to ano...

Multiple-users, one account
I am working on Exchange 2003 with Outlook 2003 clients, and am having a conceptual issue. I have one external (SMTP) email address that I would like 3 exchange users to be able to monitor and respond to. My goal is not force the users to log into outlook with a seperate profile for that user (this is my fall back plan, so if all else fails..) I created a public folder with a delivery address of the external address. Granted all three users rights to the folder. Works great for incoming messages. Of course if they reply to those messages it shows their own address which is a problem (peo...

Add one month to the previuos month
I am setting up an amortization table and want to add one month to the previous month: Ex: b9 has the start date 12/1/04 b15 needs to be the next month Jan 05 b16 Feb 05 and so on for 10 years The problem comes in 2007 (must be a leap year) it skips a month and throughs the whole thing off. Try this formula in B15: =EOMONTH(B9,0)+1 .... and then this one in B16: =EOMONTH(B15,0)+1 You may need to format these as dates. The EOMONTH() formula determines the last day of the month; adding one yields the next day, or the first day of the next month. Heater, In B15, enter the formula ...

Q) CCombobox only displays one item.
Hi, I've added several strings into a combobox, but I did see only selected item when I clicked the dropdown button. (please see below sorce code. I only see "A-" when I clicked the droupdown button) Why i didn't see any other string such as "A+", "A", and etc? Could you please advise me? Thank you. Daum BOOL CGradeDlg::OnInitDialog() { CDialog::OnInitDialog(); // TODO: Add extra init CString str = _T("A+"); m_Grade.AddString( str ); str = _T("A"); m_Grade.AddString( str ); ...

RPCProxy HTTP 404
I instal on FE server RPC over HTTP. Isapi filter on bouth server is working correctly. But afetr i enter https://*****/rpc I enter User name and password, and i get foloving error https://FQDN1/exchweb/bin/auth/owaauth.dll HTTP 404 - File not found I have sevral OWA site housted in organization, witch are public visable. The certificat for https://FQDN1/, is working , and inported in computer..... On Mon, 11 Apr 2005 07:17:04 -0700, "Mr.B" <MrB@discussions.microsoft.com> wrote: >I instal on FE server RPC over HTTP. >Isapi filter on bouth server is working correc...

Get-ChildItem.Count not working with one file
I'm executing the following command on a directory that has one file in it. When I execute it, there is never a result. Only when I add a second file does it come back with an answer. any help? (Get-ChildItem c:\temp\output *.csv).Count If I run the normal Get-ChildItems c:\temp\output I do see one file listed. Hello Steve, > I'm executing the following command on a directory that has one file > in it. When I execute it, there is never a result. Only when I add a > second file does it come back with an answer. any help? > > (Get-ChildItem c:\tem...

dellinks.exe Puts All Graphs in One Place
Hello, I just downloaded and started using the dellinks.exe file from Microsoft. It seems to be working, but I have problems when there is more than one graph on a page. All of the graphs are layered on top of each other so that only one is visible. Is there a way to fix this? Thanks. Sincerely, Tom ...

two A5 size letter heads from one sheet of A4
I would like to know how to print two A5 size letter heads from one sheet of A4 paper, so that I could cut the sheet in half and have two letter headings. It's probably very simple when you know how but I've tried to do it via page setup without success. Thank you very much for your help. Leave your document setup as A4 and in the Zoom section of the File>Print dialog, select "2 pages" in the "Pages per sheet:" control. Assuming that you have just the one page document that you want printed, enter 1, 1 in the "Pages:" control of the Pag...