Vlookup and multiple data

this is in my 1st work sheet
(a1) 500 (b1)Bob  (c1)Yes  (d1)hants
(A2) 500 (b2)Dave (c2)No   (d2)surrey
(A3) 500 (b3)Rob  (c3)Yes  (d3)york



this is the formula i am using in the next worksheet for a drop down list
=IF(VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)=0,"",VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE))

It will show the 500 seperately but what i would like to do is when you 
select the 500 is to have all of the information for 500 shown in the next 
sheet.I don't want to have to select it individually.
Hope this makes sense.
0
Utf
12/30/2009 11:29:02 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
786 Views

Similar Articles

[PageSpeed] 9

Do you want the data for all rows with 500 to show?  Or just one row of data? 
 Not sure which row of data you want.

"Josuha" wrote:

> this is in my 1st work sheet
> (a1) 500 (b1)Bob  (c1)Yes  (d1)hants
> (A2) 500 (b2)Dave (c2)No   (d2)surrey
> (A3) 500 (b3)Rob  (c3)Yes  (d3)york
> 
> 
> 
> this is the formula i am using in the next worksheet for a drop down list
> =IF(VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)=0,"",VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE))
> 
> It will show the 500 seperately but what i would like to do is when you 
> select the 500 is to have all of the information for 500 shown in the next 
> sheet.I don't want to have to select it individually.
> Hope this makes sense.
0
Utf
12/30/2009 2:21:01 PM
Hi Tom,
I would like all the rows for 500 to show really
my frop down list currently looks like this > 500
                                                               500
                                                               500
What i would like is to select any 500 and for all the information to be 
shown.
Many thanks

"Tom" wrote:

> Do you want the data for all rows with 500 to show?  Or just one row of data? 
>  Not sure which row of data you want.
> 
> "Josuha" wrote:
> 
> > this is in my 1st work sheet
> > (a1) 500 (b1)Bob  (c1)Yes  (d1)hants
> > (A2) 500 (b2)Dave (c2)No   (d2)surrey
> > (A3) 500 (b3)Rob  (c3)Yes  (d3)york
> > 
> > 
> > 
> > this is the formula i am using in the next worksheet for a drop down list
> > =IF(VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)=0,"",VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE))
> > 
> > It will show the 500 seperately but what i would like to do is when you 
> > select the 500 is to have all of the information for 500 shown in the next 
> > sheet.I don't want to have to select it individually.
> > Hope this makes sense.
0
Utf
12/30/2009 2:31:03 PM
The only way I can think of to use the Vlookup and your drop down box with 
the 500's all being the same is change your 500's to like 501, 502, and 503. 
The vlookup is only going to lookup your first 500.

"Josuha" wrote:

> this is in my 1st work sheet
> (a1) 500 (b1)Bob  (c1)Yes  (d1)hants
> (A2) 500 (b2)Dave (c2)No   (d2)surrey
> (A3) 500 (b3)Rob  (c3)Yes  (d3)york
> 
> 
> 
> this is the formula i am using in the next worksheet for a drop down list
> =IF(VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)=0,"",VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE))
> 
> It will show the 500 seperately but what i would like to do is when you 
> select the 500 is to have all of the information for 500 shown in the next 
> sheet.I don't want to have to select it individually.
> Hope this makes sense.
0
Utf
12/30/2009 6:34:01 PM
I think I can write a formula for you, but I need to know something:
Will the drop down box only ever list the numbers in the A column?  For 
example, will you need to sort by all the Robs, or No's?

If all we're sorting by are the numbers, I think it can be done.  
Also, are the only three ways you group them:
<500
500
>500
Is that correct?



"Josuha" wrote:

> this is in my 1st work sheet
> (a1) 500 (b1)Bob  (c1)Yes  (d1)hants
> (A2) 500 (b2)Dave (c2)No   (d2)surrey
> (A3) 500 (b3)Rob  (c3)Yes  (d3)york
> 
> 
> 
> this is the formula i am using in the next worksheet for a drop down list
> =IF(VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)=0,"",VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE))
> 
> It will show the 500 seperately but what i would like to do is when you 
> select the 500 is to have all of the information for 500 shown in the next 
> sheet.I don't want to have to select it individually.
> Hope this makes sense.
0
Utf
12/30/2009 7:46:01 PM
Appreciate the help Guys.

It will only need to list the numbers.
Bascially i have a list of cost centres 500, 600, 750, 800 etc
but i have numerous people assigned to each cost centre, what i would like 
to do is when i select the 500 from a drop down list it pulls all the 
information for every 500 into a seperate worksheet.
I would use a piv table but the second work sheet is a form being sent out 
to users and im not savy with piv tables atm :(



"Tom" wrote:

> I think I can write a formula for you, but I need to know something:
> Will the drop down box only ever list the numbers in the A column?  For 
> example, will you need to sort by all the Robs, or No's?
> 
> If all we're sorting by are the numbers, I think it can be done.  
> Also, are the only three ways you group them:
> <500
> 500
> >500
> Is that correct?
> 
> 
> 
> "Josuha" wrote:
> 
> > this is in my 1st work sheet
> > (a1) 500 (b1)Bob  (c1)Yes  (d1)hants
> > (A2) 500 (b2)Dave (c2)No   (d2)surrey
> > (A3) 500 (b3)Rob  (c3)Yes  (d3)york
> > 
> > 
> > 
> > this is the formula i am using in the next worksheet for a drop down list
> > =IF(VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)=0,"",VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE))
> > 
> > It will show the 500 seperately but what i would like to do is when you 
> > select the 500 is to have all of the information for 500 shown in the next 
> > sheet.I don't want to have to select it individually.
> > Hope this makes sense.
0
Utf
12/31/2009 8:38:01 AM
Reply:

Similar Artilces:

MS Money Data On Mac
I have recently been put in charge of the accounting at a non-profit, which has kept their records on a PC using MS Money 99. Problem is, I use a Mac. How can I view or import MS Money 99 data on/to a Mac? Any help would be much appreciated. Not at all easily and not without, say, Quicken for Mac and lots of mangled Money data. "Ted Eimon" <teimon@thegrid.net> wrote in message news:BC3CAACB.45B5%teimon@thegrid.net... > I have recently been put in charge of the accounting at a non-profit, which > has kept their records on a PC using MS Money 99. Problem is, I use a Mac...

Dynamic Charting
I am looking for some ideas around creating a series of user friendly charts where for ony one specific metric the user can select from up to 10 business units, which ones to compare on a single chart. A chart with all 10 lines would be too busy but if a user wanted to look at his/her BU and maybe 2 or 3 others which are close in size then the chart would be useful. I am imagining a box where the user can choose a few BU's and graph just those. If the user wanted to change his/her selection they could by unclicking/clicking on the choices and a new graph would be generated. Any thoug...

Multiple Search Criteria/ Index Match
I am using the following formula to retrieve data that matches tw specific criteria. =INDEX(C2:C80,MATCH(1,(A2:A80="Liverpool")*(B2:B80="January"),0)) using ctrl,shift, enter to give curly brackets to make it work. This formula is then repeated in the cell directly below, but searche for "February", below that "March" and so on. However when I copy my formula, I have to change the month for eac cell and therefore have to put in the curly brackets again. Is there anyway around this -- Message posted from http://www.ExcelForum.com Have a list with the ...

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

Restoring data to Exchange
Hello, We are trying to restore our exchange data after doing a successful Active Directory domain rename. Since Exchange is on a domain controller, we were forced to remove it to complete the domain rename. So, we used ntbackup to backup all of the data. Now, we are trying to restore the data, but we get the following message: <!-- Begin Message --> Unable to restore Exchange data to HHNSRVR\Microsoft Information Store\First Storage Group, check the application event log for more information <!-- End Message --> As it says, we looked in the event log and found this: <!-- B...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Creating a word2000 document with access 2000 data
I have a database in access 2000 it is basically customer info. address and dates and timesof appointments. I need to place a button on the formpage that prints a letter using the customer details and the date & time entered in the database. Anyone have any clues as to the easiest way to do this.sort of done it using mailmerge but not what I want. Actually want a word document to pop up that the staff can just double check and press print. Any help would be appreciated even just a point in the right direction to a good source of information. to see if the following website's offer...

stack overflow problem with _bstr_t data type
According to MSDN description, passing a lengthy string( of type char*) to a _bstr_t data type may result a stack overflow error. So, why it happens, and what is the maximum length of the string that we can pass thanks in advance, Naren ...

Multiple charts in ChartSpace; problems with double Categories
I want to show two or more charts with different categories and data in one chartspace, so I did the following: 1. Create Chartspace 2. Add Chart1 in Chartspace 3. Add Series in Chart1 4. Series.SetData chDimCategories 'A,B,C' 5. Series.SetData chDimValues '5,2,6' Now I see a chart with categories A (value=5), B (value=2) and C (value=6). So everything okay. Next thing I do: 6. Add Chart2 in Chartspace Now I see a second empty chart, BUT WITH already filled categories A,B and C!! I don't want this, because I want to use other categories in this second chart2. And whe...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

how replace comma with point in import data
I import some stocks prices from the web. They come in with a comma as decimal limitator and I need them to appear with a point instead. how can I convert it ? Set up your Windows Regional Options (in Control Panel) for the Format in which you want to read your input data. Format Cells/ Number for the way that you want to display the output. -- David Biddulph "Gepetto" <Gepetto@discussions.microsoft.com> wrote in message news:2D214513-6C9B-4E14-8AAD-EF7012728197@microsoft.com... >I import some stocks prices from the web. They come in with a comma as > ...

I need the UNMATCH data
Hi. I have a table that have named as querie1 that has record, imagine 1 to 100. I have table2 that have 50 record 1 to 50. Those 50 record of table2 are equal to 50 record that exists in table1. I need a querie that shows me the that the does not mach. That shows me the others 50 records. Please help me. Regards, Marco On Tue, 27 Mar 2007 16:23:24 -0700, Marco <Marco@discussions.microsoft.com> wrote: >Hi. I have a table that have named as querie1 that has record, imagine 1 to >100. I have table2 that have 50 record 1 to 50. Those 50 record of table2 are >equal to 50 re...

Forwarding Multiple Emails to AOL
I have about 2,400 email messages saved on Outlook that I need to transfer to an AOL Email account. How can I do this without emailing each individually? "awerhun" <awerhun@discussions.microsoft.com> wrote in message news:B2B78760-FADA-47C9-BF21-1597E345CF71@microsoft.com... >I have about 2,400 email messages saved on Outlook that I need to transfer to > an AOL Email account. How can I do this without emailing each individually? Simply create your AOL account in Outlook, then drag the messages to the AOL folders. -- Brian Tillman [MVP-Outlook] O...

No data in dynamic spreadsheet
Hi One of our users is using the MS CRM 3.0 web client and terminal services. When this users exports a dynamic excel spreadsheet and enables automatic refresh no data is visible in the spreadsheet. The user is using Office 2003 and is able to view all records in CRM so the permissions are correct. Anyone have an idea as to what could be causing this? Static excel spreadsheets display data. Does the user need to connect to the SQL database in some way? Appreciate your assistance. Thanks Mark When the spreadsheet is opened, check if there is a message at the top asking to unlock the ...

Create Multiple Index
Access 2007 I want to build a multiple index on a table in my database. Can anyone tell me why this does not work? Private Sub Command0_Click() DoCmd.RunSQL "CREATE INDEX Models ON tblCatalogModels, (Year, Make, Model)" End Sub "NEWER USER" <NEWERUSER@discussions.microsoft.com> wrote in message news:88DBF188-1446-43D3-8C5F-24C9E3728097@microsoft.com... > Access 2007 > I want to build a multiple index on a table in my database. Can anyone > tell > me why this does not work? > > Private Sub Command0_Click() > > DoCmd.Ru...

retrieving data from a table on a website
There is a internet page with chemical data : http://spreadsheets.google.com/pub?key=twQ35hFIq-y0N84xQ5l0ICQ&output=html It has chemicals in the first column and proerties in adjacent columns. Is there a way to have a userform lookup a chemical in a textbox from the websites 1st column and then populate other textboxes with the adjacent data from the table? Can anyone help? Thanks, Roger On Jan 18, 9:59=A0am, Roger on Excel <RogeronEx...@discussions.microsoft.com> wrote: > There is a internet page with chemical data : > > http://spreadsheets.googl...

Data Text to Columns
I have a large column and would like to split it for the literal that says "Company Name" and then have the company name right next to it which is enterable. I tried using Data/Text to Columns, Fixed Width, sliding the arrow after Company Name: and it did not seem to split the column. I also tried putting an arrow out a ways so that there looked as though there were two defined fields and that didn't seem to work either. Am I approaching this the right way??? HELP!!! Thanks! Hi this is not possible in Excel. You have to use two columns for this -- Regards Frank Kabel Fr...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

multiple item select from a combo box
I want to use this as a navigational tool, rather than to "input" values into a database. This control will be on a form that sets parameters for graphical chart displays. One of the requirements is to be able to select multiple values from a 2nd combo box after a 'parent' 1st combo box determines what should be in the 2nd one. I have a couple of ideas of how to do this, but was just wondering if someone has already tackled something like this before. I suppose I could populate a temporary table that has yes/no values in it and display that in a subform (which scroll...

sort data
i am trying, unsuccessfullty, to sort some data (a small example is below) so I can graph it. I want to put it into a pivot table format but don't want calculations, just the actual data. any ideas??/ Location DATE Result WAT002WA ALBANY BOTTOM 19-Jul-05 0.83 WAT002WA ALBANY BOTTOM 23-Jun-05 0.8 WAT002WA ALBANY BOTTOM 28-May-05 1.16 WAT002WA ALBANY BOTTOM 02-May-05 0.93 WAT002WA ALBANY BOTTOM 06-Apr-05 0.66 WAT002WA ALBANY BOTTOM 11-Mar-05 0.59 WAT002WA ALBANY BOTTOM 13-Feb-05 0.54 WAT002WA ALBANY BOTTOM 18-Jan-05 0.62 WAT002WA ALBANY TOP 09-Mar-07 0.73 ...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

Data for Chart with Blank Formula Cells
I am trying to chart a worksheet that contains many formulas. When th result of the formula is zero, the cell is instructed to leave the cel blank (which is important to the information being tracked). However when I chart the data, it charts a ZERO for the cell. I tried the interpolate feature, but because the cell has a formula i it, it is not being read as "blank", even though the value is blank. Help! If week one is 2, week three is "blank" (remember, cell actuall has a formula that is returning the blank), and week four is 6, I do get my chart to interpolate from...

Publisher 2000-XP multiple pages not visible
Hi, I am new to this group seeking some help. I was working on a large number of pages that I had scanned images onto and entered text. In one document, I can see the first page only and not the other 6. When I print, it prints out the pages correctly, but I cannot view these pages, and needless to say, make any corrections. My other documents, which have 100 pages in them, show nothing at all. They also print. When I made a new document to see what was going on, I can Insert new pages, and nothing shows up on the bottom of the screen to show the pages. In My Documents, the file sizes ind...