Matching values in different sheets and rows

To fill in a value V, with the restriction that the stock number
(10-40)  should have the same value as in Sheets B, C, ...,  I use in a
cell right of the stock numbers in Sheet A the folllowing code:

=IF(ISNUMBER(MATCH(C7,'SheetB'!A:A,0)),'SheetB'!C1,IF(ISNUMBER(MATCH
etc...

But the values in sheets B, C etc are not filled in in the same order
as Sheet A.

How to transfer that the value V is from a certain row?


Sheet A

10 V
20 V
30 V
40 V
...

Sheet B

40 XX
30 ZZ
...

V should be filled in with value where 10 sheet A = 10 sheet B etc.
So:

30 ZZ
40 XX

See also:

http://groups-beta.google.com/group/microsoft.public.excel/browse_thread/thread/31d082b624fd5145/dbe8e71e03097a8f?lnk=st&q=aa+arens&rnum=7#dbe8e71e03097a8f

Bart
Excel 2003

0
12/3/2006 5:50:53 AM
excel 39879 articles. 2 followers. Follow

1 Replies
338 Views

Similar Articles

[PageSpeed] 30

In your formula you are asking to return from SheetB!C1 if there is a
match - surely you want to return from the row that the match occurs,
using an INDEX/MATCH combination, or a VLOOKUP like this:

=IF(ISNUMBER(MATCH(C7,'SheetB'!A:A,0)),VLOOKUP(C7,'SheetB'!A:C,3,0),IF(ISNUMBER(MATCH
etc

The formula can be copied down. Strictly speaking, apostrophes around
the sheetname are only needed if you have spaces in the name.

Hope this helps.

Pete

AA Arens wrote:

> To fill in a value V, with the restriction that the stock number
> (10-40)  should have the same value as in Sheets B, C, ...,  I use in a
> cell right of the stock numbers in Sheet A the folllowing code:
>
> =IF(ISNUMBER(MATCH(C7,'SheetB'!A:A,0)),'SheetB'!C1,IF(ISNUMBER(MATCH
> etc...
>
> But the values in sheets B, C etc are not filled in in the same order
> as Sheet A.
>
> How to transfer that the value V is from a certain row?
>
>
> Sheet A
>
> 10 V
> 20 V
> 30 V
> 40 V
> ..
>
> Sheet B
>
> 40 XX
> 30 ZZ
> ..
>
> V should be filled in with value where 10 sheet A = 10 sheet B etc.
> So:
>
> 30 ZZ
> 40 XX
>
> See also:
>
> http://groups-beta.google.com/group/microsoft.public.excel/browse_thread/thread/31d082b624fd5145/dbe8e71e03097a8f?lnk=st&q=aa+arens&rnum=7#dbe8e71e03097a8f
> 
> Bart
> Excel 2003

0
pashurst (2576)
12/3/2006 9:35:34 PM
Reply:

Similar Artilces:

need macro :repeat rows at top
I am a pure novice with Excel 2007. I only have one work sheet I work with. I need a macro to place rows 3,4,5,23,and 24 at the top of every succeeding page following page one. They show up only in Print. Page setup won't work as the rows must be contiguous.The data in each of these rows must remain as each successive page header is to be the exact duplicate.Coulde you please write the macro exactly as I should copy to VBE. -- J Macho ...

Why does data sometimes duplicate itself on an Exel sheet when I S
I have created a shared workbook to use as a weekly phone log, using Excel 2000 (it has five worksheets within it). When I Save it, the program will sometimes randomly duplicate lines of text within 1+ of the worksheets. For example, it may automatically copy and paste the last ten rows at the bottom of Monday's page, the last eight rows at the bottom of Thursday's page, and leave all the other pages as is. The next time, it will save the entire workbook as I want it. How can I get it to save and not randomly duplicate? (I can delete the duplicated rows and save again, to g...

Pivot Table On Hidden Rows
All, I have a pivot table that is based on a huge set of data (c. 3,000 rows). The pivot works fine but if you filter the source data then the pivot (when updated) will only pick up the data that is shown after the filter. I want to be able to analysis the original data without worrying about the effects on the filter. Any Ideas?:confused: -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23758 View this thread: http://www.excelforum.com/showthread.php?threadid=495745...

Finding Values in a "Matrix"
I have a matrix I have created in Excel. I am using this for correlations. I have cells B1 through G1 filled with text names (the same values as A2 through A7). In the matrix fields I calulate their correlations. In a seperate field, say J1, I am calculating the highest correlation value from my matrix. What I would like to do in cells K1 and/or L1 is find the corresponding text names for that high correlation. So for example if cell E1 is Red and cell A2 is Magenta and their correlation is .95, the highest in the matrix, Cell J1 calculates .95. I would like cell(s) K1 and/or L1...

Stacked AND Clustered AND Different axis scales
Hi all, is it possible to to have both clustered AND stacked bars in the same chart and having the clustered bars in different Y Axis ans scales?. Is there some workaround? Thanks Regards Álvaro ...

Copying varying values down through rows
Hi, I'd like to copy values from a column down till a new value is encountered and then start copying that down. Simple I'm sure but I can't seen to get a start on it. Here's what I have: Column A Column B Value 1 Number Number Number Value 2 Number Number Here's what I'd like after running macro: Column A Column B Value 1 Number Value 1 Number Value 1 Number Value 2 Number Value 2 Number Thanks for the help. S...

link cell from one sheet to another within the same workbook
how can I link cells on sheet 1 to sheet 2 within the same workbook? Lp Copy the cells on sheet 1 and paste/special on sheet 2 with link set -- Gary's Student "Lp" wrote: > how can I link cells on sheet 1 to sheet 2 within the same workbook? > Lp In sheet1, in the cell that should be linked, type = then click to the source cell in sheet2. The result will be a formula like =Sheet2!A1. (You could also just type the formula). "Lp" wrote: > how can I link cells on sheet 1 to sheet 2 within the same workbook? > Lp ...

Merge and Center Multiple Rows
I have two rows of numbers. After doing some comparisons, I want to send this spreadsheet to the customer with JUST the value for each column that we want them to see. Currently, we are going to the two side-by-side columns, deleting the number that we don't want them to see, then highlighting both cells and clicking on the merge and center button on the toolbar. This is all fine and dandy if you're doing one or two rows of data....but we've got pages and pages we have to do this to. My question is two-fold - I know about the STUPID error "The cell reference for a merged c...

Any Difference between Publisher 2000 and Office 2000 Disk 2 Publisher???
This is a multi-part message in MIME format. ------=_NextPart_000_001E_01C47653.0B24A2D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there any difference between the stand alone Publisher 2000 and the = version included on disk #2 of Office 2000? Comparing the contents of the two disks - there are slight differences = in the file structures and contents - Was wondering if this translates into user noticeable differences (ie: = more clipart, additional fonts, better integration into Office, etc). I'm preparing to rebuild my sy...

Copy setup tables to different great plains version
Hi, There is a techknowledge (KB872709) wherein you can copy setup tables from one company to another in Great Plains. Can I use this if the Great Plains version is different? I'll transfer the setup tables from Great Plains 6 to Great Plains 9. Or should I extract the data from Great Plains 6 then use Integration Manager to integrate it to Great Plains 9? Thanks, Mel It's difficult to give a blanket yes or no answer. You would need to compare the fields from table to table to determine if there are changes. For example, GL definitely has additional fields so you couldn't...

Deleting Rows
For a Macro novice... Is there an easy macro that deletes rows when a particular cell has the value 0 (zero)? Thank you try Sub deletezerorows() Application.DisplayAlerts = False On Error Resume Next vlr = Cells(Rows.Count, "d").End(xlUp).Row With Range(Cells(2, "D"), Cells(vlr, "D")) .AutoFilter Field:=1, Criteria1:="0" ..SpecialCells(xlCellTypeVisible).Delete .AutoFilter End With Application.DisplayAlerts = False End Sub -- Don Guillett SalesAid Software dguillett1@austin.rr.com "fak119" <fak119@discussions.microsoft.c...

Excel Rows and Columns Issue
I am an excel 2002 user, and I am having problem putting in th formulas. The formulas are simple, average, add, subtract, an multiplying cells, but the real problem is in "viewing" the rows an columns. Normally, in excel, rows are numbered (1,2,3,4...) and columns ar alphabatized (A,B,C,D...) In my case, both columns and rows ar "numbered" , so putting in a formula is a pain in my neck. How d resolve or change the view of the excel sheet so it displays th "Letters" on the top columns part of the sheet going from left t right, and the "numbers" rows (wh...

MsgBox popping up on entering of certain text in any row of Column X
Dear Experts: I need help with a macro: As soon as a user enters a certain string, say "text" in any row of Column A (with the exception of the column heading) , a msgbox is to pop up, saying "This is not allowed!" I would favour a macro solution, but if there is a built-in functionality to perform this, that would be great too. Help is much appreciated. Thank you very much in advance. Regards, Andreas Hi Andreas, Use Data Validation. (Unless the column header contains an invalid entry then there is no reason not to apply the validation to the entir...

Removing #VALUE!
OK one more 'last' question... In column K I total the result of the VLOOKUP * the quantity I enter into column J, but if there's nothing in J it shows, #VALUE! It's just =I11*J11 How do I make it show nothing in the cell instead of #VALUE!.........? Try =IF(J11="","",J11*I11) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tim" <lakepir8@tampabay.rr.kom> wrote in message news:4pYgg.1199$Es.972@tornado.tampabay.rr.com... > OK one more 'last' question... > > In ...

Function that helps me find the middle value
Hi, I need a function that helps find the middle value. For example, if I have 12, 23 and 100, it must show 23. Or if I have 1,2,3 must show 2. Thanks Here's a generalised solution to the problem: =3DLARGE(A1:A10,ROUNDUP(COUNT(A1:A10)/2,0)) Your numbers can be anywhere in the range A1:A10 (as an example), and works best if you have an odd number of numbers. Hope this helps. Pete On Dec 18, 1:31=A0pm, Diana <dianamrco...@gmail.com> wrote: > Hi, > > I need a function that helps find the middle value. For example, if I > have 12, 23 and 100, it m...

Calculate percent return for different stocks
I’m trying to calculate the percent return for different stocks. I need to get the ([End Price]-[Begin Price])/[Begin Price]. Here is my (non-working) query: SELECT SharePrices.StockSymbol, SharePrices.StockPrice, DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]) AS Date30End, DateAdd("d",0,[Forms]![frmMstr]![cboEnd]) AS Date30Start FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol GROUP BY SharePrices.StockSymbol, SharePrices.StockPrice, DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]), Dat...

Convert data from rows to columns
I have a range of information on a spreadsheet as follows: A B 1 Name: Tom Smith Job Title: Sales Organisation: Made up Telephone: 1111 111 111 Email: tom.smith@madeup.co.uk Subject: Not much I have another 300+ entries of data (of 8 rows exactly as above) totalling 2629 rows. the example above is how it appears on my spreadsheet i.e. headings and names in the same cell (Name: Tom Smith) and sometimes seperate cells (Email: / tom.smith@madeup.co.uk) I need to create 6 columns for Name/Job title/Organisation/Telephone/Email/Subject and then move the data into the relevant columns....

adding work sheets
When I add a work sheet to a work book , Is there a way to add the header & footer settings to the new work sheet? Thanks John Inkster John, If the worksheet will be similar to existing worksheets, consider copying an existing sheet, then removing the data and whatever else is necessary from it. The new sheet will have all the stuff the original did. -- Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "John D. Inkster" <jdinkster@rogers.com> wrote in message news:uWFOiUZPHHA.4172@TK2MSFTNGP03.phx.gbl... &g...

Have the need to sort text code-values within same Excel cell
Got column with thousands of cells containg different codes, e.g., cell P357 --> {X, A, B2, T83, M19}; cell P425 --> {T83, A, X, M19, B2}, etc ... Need a way to sort these individual cells in a similar order so I can aggregate the number of cells containing the same set of codes/values ... I am somehow familiar with the basic Excel functionality but above situation is really challenging me ! Thanks in advance .. Say each of the cells in column A contains a list of 5 codes separated by commas. First copy column A into columns B, C, D, and E. Next set and Au...

How to plot chart where X axis values START >1
My Y values start at X axis values that are >1. For example x y 1 0 5 0 20 15 21 8 .. . 40 14 So I want my chart to show X values from 18 to, say, 42. Please do not tell me to right click on x axis, click on format and set minimum to 18 because on X axis format YOU CANNOT SET A MINIMUM - THE FACILITY ONLY EXISTS FOR THE Y AXIS. Many thanks if someone can help. In article <57817CDC-CB6C-4D10-B5EC-929EF716FC2C@microsoft.com>, Onno@discussions.microsoft.com says... > My Y values start at X axis values that are >1. For example > > x y > 1 0 > 5 0 > 20 1...

Counif with different cells, not a range
How can I count all the the 'zeros' from different cells such as A1, D1, G1, H1 TIA Try this =SUMPRODUCT(--(COUNTIF(OFFSET(1:1,0,{0,3,6,7},1,1),0))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Anthony Slater" <AnthonySlater@discussions.microsoft.com> wrote in message news:E8528F7D-72A5-4101-BA92-8AB9B4422CA6@microsoft.com... > How can I count all the the 'zeros' from different cells such as A1, D1, G1, H1 > > TIA Thanks Bob That worked a treat... Have a good Christmas "Bob Phillips" wrote: > Tr...

Get rows from a queue table ?
I have an application where agents type in trouble tickets that they get via phone and email. Now there is to be a web portal that inserts rows to a ticket queue table. There will be a queue count displayed on the agent's screen along with a GET button. When this button is clicked, the first item in the queue must be populated on the screen and removed from the queue. Since there are multiple ticket agents working simultaneously, how can I code this to ensure that no two agents grab the same row or grab different rows but remove the wrong one? ...

Timestamp a sheet
I would like to timestamp a sheet upon opening but I do not want th date to change after it is initially stamped. Right now I am usin today() but as you all know this gives teh day that you are a currently. Any suggestions -- eluehman ----------------------------------------------------------------------- eluehmann's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1309 View this thread: http://www.excelforum.com/showthread.php?threadid=26418 Hi, You can paste this into the ThisWorkbook module. Private Sub Workbook_Open() If Range("A1").Value = &quo...

Make a text box lines have different colors
In a design view of a report, I want to make the right side line of a text box white and make the other three sides black. Is this possible? Thanks! Glen On Thu, 19 Nov 2009 13:43:01 -0800, *Glen* wrote: > In a design view of a report, I want to make the right side line of a text > box white and make the other three sides black. Is this possible? Thanks! > > Glen No. Not directly. However, you can set the control's Border Style to Solid, Black BorderColor. This gives you a black border on all 4 sides. Then place a line control directly on top of th...

Win98 is cool; Agent works differently on 98 versus XP.
Two points. Win98 is cool. Agent works differently in win98 from winXP, even when using the very same copy of (or course) the very same version of Agent, with the very same data files. Win98 is cool. My misadjusted oil furnace made my computer dirty, and I've cleaned it some but not enough and it still overheats on really hot days, even when only Eudora and Agent are running, so then I go to win98 where it runs about 5 degrees cooler, maybe 8 or 10**. Even though it's just a different partition on the same drive, and the programs and data files for both are exact...