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.  

Please help

Thank You in advance

Dal

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

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

2 Replies
620 Views

Similar Articles

[PageSpeed] 27

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
---
Please respond in thread
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.
>
> Please help
>
> Thank You in advance
>
> 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
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----


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

Similar Artilces:

Second Y axis (same as first)
Is there a way to do this that does not involve VB? I have searched through the site and seen people post about http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html I have tried adding another series of the same data, and hiding the data - but this always makes the spacing on the x axis a bit 'shifted to the left'. Any help would be very much appreciated. Hi, You can not adjust the scales min/min values to a specific value without using code. If you just want the secondary axis to match the primary then you can do that by including the maximum data value in a series o...

Format a cell as seconds
Is there a way to format cells to appear as seconds. I would like to type 7 and have it generate a 0:00:07. I know that I could use the TIME function for this, but I would need two cells for that. I want to format the cell I'm working in. Thanks! Hi have a look at http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards Frank Kabel Frankfurt, Germany Lisa wrote: > Is there a way to format cells to appear as seconds. I > would like to type 7 and have it generate a 0:00:07. I > know that I could use the TIME function for this, but I > would need two cells for tha...

Insert Graphic onto Worksheet
Hi all, I am using Excel 2007. I have 20 worksheets in a workbook and I intend to paste a logo onto all worksheets. Is there a way to just insert once and the logo will appear in all 20 worksheet. Thank you. You could do something like this: Insert the logo so that it covers A1:B3 (say). Then select A1:B3 (the cells behind the picture) Go to the other sheet (one at a time). Shift Edit|Paste picture link (in xl2003 menus) In xl2007, I think you'll need to add the Camera tool to the QAT to do the copy pasting. If you have trouble doing this in xl2007, maybe some...

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Two tables, one query, one form, one problem
Hello all: I have two tables (one with employee data and one with Office location data). They are joined using the office address as the primary key in the Office Table and the foreign key in the Employee Table. I have created a query using all of the fields in each table. Here is what I want to be able to do (and can't seem to): Enter in new employee data and (with a combo box) select the address of the office and have it populate the rest of the office information. When I try it, it doesn't work. I have created a query with the addresses and bound it to the combo box. ...

open an excel sheet in works
Is there a way to download an excel sheet and open in works or works spreadsheet? Not that I know of. -- Don Guillett SalesAid Software donaldb@281.com "mike" <mikeext249@hotmail.com> wrote in message news:754C543F-E2EE-4B4F-9955-58682BCB14D9@microsoft.com... > Is there a way to download an excel sheet and open in works or works spreadsheet? ...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Turning off One Note
When I installed Microsoft Office 2007 Ultimate the application set up a One Note feature that gets loaded every time I start up the computer. The One Note process shows up as an icon in the system tray, but it is a process I do not use and I don't want it. The only problem is that I cannot figure out how to get rid of it. there is no entry in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run Nor is there an entry in the start folder of either my local user or of all users. If there is an entry in the Office Branch of the registry that controls this, I...

one macro different buttons
Hi, I have lot of textboxes and labels (controls) in a sheet in different places. When I click any of them I will go to cell A. i.e. Private sub Label1_click() Range("A1").Select End sub and Private sub Label1_click() Range("A1").Select End sub .... .... .... How can I use only ONE code to all of these lables? To be more clear: Is there something like: Private sub Label1_click() , Label2_click , Label3_click()... Range("A1").Select End sub Can any one help? No. You need a different event for each one I believe.... OJ Hi Khalil. If you are using &quo...

Cannot change the font size for Property Sheet
Hi, The issue that I am facing is described below: The requirement is to change the font size of Property Sheet and the corresponding Property Pages displayed there in. Even though at design time I change the font size of individual property pages to 12 pixels, at run time they are still displayed in 8 pixel font size. Another approach that I tried was to programatically set the font size explicitly in both property sheet and individual property pages, but still it does not help. Is there any specific thing that I should take into consideration? Regards, Mona "Mona" <mona.j...

excel extract data from one sheet to another
a b c Name Vault Rank Blake, Jasmine 7.900 1 Fattouche, Chloe 7.600 3 Fattouche, Nicole 7.700 2 Pratt, Erika 6.100 5 Salem, Alexandra 6.300 4 Mills, Sara 7.700 2 Ranked compares each via =SUMPRODUCT(--(C5<$C$5:$C$148),1/COUNTIF($C$5:$C$148,$C$5:$C$148&""))+1 This data is on one sheet and already calculated the rank. Need to pull the top three and put on seprate work sheet. Including ties. Name Vault Rank Blake, Jasmine 7.900 1 Fattouche, Nicole 7.700 2 Mills,Sara ...

Retroactive filter between two sheets?
I have two sheets - one with rows A-I and ID numbers 1-9 (like this: http://img.photobucket.com/albums/v294/greenerton/excel1.jpg ), another with the same ID numbers 1-9, but instead of the lettered row, one with various colors (like this: http://img.photobucket.com/albums/v294/greenerton/excel2.jpg ). I have a filter on the second sheet so I can sort by color to see all the IDs that have that color (IE red has 1,3,4,8), and vice versa with the numbers. How would I set it up so that I could sort the second sheet by the various colors and have Excel simultaneously sort the first sheet in the sa...

view multiple people's appointments in one calendar
Further to my last post... My goal is to have one calendar in Outlook (without exchange server) where I can book and manage appointments for four people. The other people don't have outlook nor will they need to access it. I would manage the appointments in one occurance of Outlook. I can enter side by side appointments for each person but I don't know how to filter down to view only one persons calendar at any given time. Thanks, Tim See if http://www.slipstick.com/calendar/scheduleall.htm#noexchange helps Tim wrote: > Further to my last post... > > My goal is to ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

Quickly Format Multiple Data Series in One chart
I do a lot of charts that contain spectral data. I'll have 20-30 data series and I plot them as a XY scatter using the line only format. However the default thickness in XL 2007 is way too fat and consequently I loose any ability to differentiate the series. So far the only way I've been able to change this is by actually selecting each individual series and changing the thickness. Is there a way to change the default line thickness to 1/2 pt? Or at least to change all the series in a chart in one go? In Excel 2007, if all you are changing is one single attribute of a serie...

Find value for one cell from 2 different lists.
value for cell L55 when K33 has two options. K33 = Bags or sack and form that I need to get values from 2 different lists and a 3rd cell L13 has a volume to start from. I am using L13 as my starting cell. Formula is. =lookup(L13,A117:A191,H119:H191) for when K33 = sacks. and if K33 = bags. =lookup(L13,A117:A191,K117:K191). Regards Chris Sorry. How do I get it to work from both lists with one formula. Thanks Chris. "Chris" wrote: > value for cell L55 when K33 has two options. K33 = Bags or sack and form that > I need to get values from 2 different lists and...

Outsourced Exchange
Hey All: I have clients who would like to use the public folder and contact sharing capability of Exchange without having the burden of housing Exchange inhouse (currently they use POP email). So I looked at some calendar and contact sharing tools on slipstick.com, but when I do research on them, they seem flaky. I am also considering whether to move them to an outsourced Exchange service provider such as Mi8, etc. Does anyone here have experience with using managed/hosted exchange service? What are the risks and pitfalls? Which provider has a great track record? Thanks. - Raheem I have he...

export excel sheet to visio
i have prepared diagrams, and illustrations in microsoft excel(using lines, rectangles etc). i want to import the excel sheet in visio and use the superior graphic abilities of visio. please advise on how to do this Just copy and paste into visio. Then select the pasted image and ungroup with "Shape=>Grouping=>Ungroup". Enjoy with Visio "S N" <uandme72@yahoo.com> a �crit dans le message de news:eHjy89ZEIHA.3980@TK2MSFTNGP03.phx.gbl... >i have prepared diagrams, and illustrations in microsoft excel(using lines, >rectangles etc). > i want to imp...

Moving from One Note 2003 to One Note 2007
Can I export my 2003 notes into OneNote 2007? Any other way of showing my 'old' notes from OneNote 2003 in my new software (2007?) OneNoteDAD wrote: > Can I export my 2003 notes into OneNote 2007? Any other way of > showing my 'old' notes from OneNote 2003 in my new software (2007?) If you open the notebooks in ON2010 they will automatically be converted. Pls note: This is a one-way-street. There is no way to downgrade the notebooks once they are in the new format. So make sure that you have backups in case you would want to further work with ON2003. ...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Second Global Catalog Server
I am running Windows 2000 Active Directory Domain in Native mode with 2 domain controllers on the network. Exchange 2000 has been running on a member server for about 2 years. Currently only the 1st domain controller is designated as the Global Catalog, I would like to also make the 2nd domain controller a global catalog for redundancy. Is the procedure for assigning global catalog role to the 2nd domain controller the same as if there was no Exchange server present? In AD Sites/Services I see an Exchange Settings folder under the 1st domain controller. Any ideas or suggestions would be g...

Find Total and insert one blank row below
Normally I may have 140 to 180 items in a list, I’m subtotaling and then trying to insert a blank row under the *Total* in Column E. This code worked fine the first couple time I ran it, but then stopped working. I can’t figure out why. It shouldn’t be because of the 250 rows which now contain some blanks (sometimes I will have more than 180 items; I want to capture all). I know you can insert rows into a Subtotal list, so that's not it. At one point I had a few of these guys in Column E: #VALUE! #VALUE! Total I got rid of those, not that they should matter with the *...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...