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
832 Views

Similar Articles

[PageSpeed] 0

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:

Locking data #2
Hi, Is there any way that once a user has clicked a button or entered data into a cell it is locked in an equivalent cell? For example in Cell B1 formula =A1 in Cell A1 = 5 If the 5 becomes 6 then so will B1. But is there anyway, that by using a formula or short onClick code I can make it so that B1 remains 5 even if A1 changes. Thanks Antony ...

Transmitting Outlook data to a web server
I am looking for a solution on how to publish data in Outlook to a web server. A custom web app will be written to access the data published to its server and and display it in some special way. I will either need to write custom VBA for Outlook that transmits the data to the web server or use some third-party tool that integrates into Outlook that will transmit the data for me. The data is sent in only one direction - from client to server. The data on the server is read- only. Whenever the user changes data in Outlook, either it gets sent to the server automatically (the preferred wa...

Table-data transformation
I would like to take data from 3 different columns of data and recombine to a different format in another column. The original data is a series of numerical and text codes. first column, second column, third column 12,30,1245CF I would like the new column to contain the data in this form: 12-30-1245CF Can this transformation be automated? Can I set up a form to just collect the individual numbers and make the table transform them to this new form and unique identifier (key) ??? Or perhaps the form transforms and puts the data in the table. I need the data in both formats and don't wan...

Merging Excel Data into Word
Hi I use Word and Excel XP and when we merge dates from an Excel doc into Word, it puts the date in an American format for some reason even though the formatting is Australian. Regional settings are set to Australian. I have managed to edit the field in Word to change the date but was hoping that someone would know why it is behaving like this so I can stop it from happening? We also have problems with some of the forumlas. For example, we might use the currency formula which has two decimal places (ie. $23.09) but it comes into Word like 23.0258. Any ideas why this is happening? ...

exclude certain data
Hi I have a series of data in rows which are plotted on a simple line chart. Sometimes I want to exclude a particular data row from appearing on the chart by typing X in a column (I currently use this to exclude that row of data from average & mean calculations, which I also plot on the same chart) Also, I have upto 30 rows of data printed on one chart - is it possible to scroll the the dataset & highlight the chart line that the data belongs to. I do not have labels because it makes the charts too crowded I'm sure I've seen this somewhere but can't find it again Ma...

How to paste data to alternating cells down a column?
I am copying data from a column in one sheet where the entries run concurent down the column. When I paste to the new sheet I need to paste the information in every other cell down the column. What is the best way to do this, I have 3600 entries copied in groups of 25 to 72 different sheets, each sheet holds 50 entries? One extraction technique which should serve you well .. Assume you have source data in Sheet1, running in A2 down In another sheet, In any starting cell, say in B2: =IF(MOD(ROWS($1:1)-1,2)=1,"",OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/2),MOD(ROWS($1:1)-1,...

word to excel data transfer help
I have a word document that i need to export to excel. The data is addresses and a when i paste it into excel it fills up more than one row. Is there any way of controlling the data such that i can fill up the data in each row. please help. Eg: Certificate has no expiry date listed. Have to check with SGS at their website:www.ics.sgsna.com Spoke to Frank McDonald on 2004.12.14. They have not migrated QA program from ISO 1994 to ISO 2000 and their cert has lapsed therefore no longer registered. They continue to follow ISO 1994 until they update to 2000. This text in...

How do I copying data from a cell on sheet to a diff cell/sheet
It data entry of scores on worksheet one that has to be automatically copied to worksheet 2. Once I entry the data on wksht 1 want it placed on wksht 2. I run tournaments, need all of the date on worksheet 1 but only some on worksheet 2 so I can sort it. When I set up my wksht 2 us the formula =wksht1!C4 on the first line. the on the second line input =wksht1!C11 then highlight the two and drag it down. It will copied what i inputed but won't calculte and change the cell referrence by 7 how do I get it to do that without having to manually change the number? To get ...

Pivot Tables
I have a new computer and previously I was able to use Pivot tables as a shortcut to highlight a list of say 500 items and display the unique items in a pivot table. However when I try to do that now it doesn't display the items, it only show the dropdown with the unique items checked or unchecked and therefore I cannot highlight the unique items to create my unique items list. Does anyone know if there is an option that is preventing me from doing this or any other way to create a unique items list from a larger list. Thank you very much. If you create a pivot table with a fi...

Data amalgamation
I have a table of data, including columns for date, a code indicatin data source, and various analysis under headings. As follows: Date Source A B C 1/1/01 X 2 4 8 15/3/01 X 3 5 7 16/6/02 Y 12 1 9 I want a summary table that is driven by a (variable) date range amalgamating data by source and A,B,C. As follows: X Y Z Pre 30/6/01 - A 5 0 0 - B 9 0 0 - C 15 0 0 Post 30/6/01 - A ...

whole lines are disappearing before I finish entering data
When using the "find" option I can see the text is still there, but I've lost whole lines while entering data in Excel and now the lines are numbered 1, 2, 3, 6, 7 etcl. There are 3 lines missing. How do I get the back? And why are they disappearing? Maybe your worksheet is filtered (hiding some rows, showing others): Data|Filter|ShowAll. Or maybe you just have hidden rows (manually hidden). Ctrl-a (twice in xl2003) format|row|Unhide DiWoz wrote: > > When using the "find" option I can see the text is still there, but I've lost > whole lines wh...

move data
How do I rapidly move data from cells A1 - A5 to A1-F1? I did this a couple years ago and thought is was in paste special but no luck this time. I think you want copy|paste special|check transpose. but the "from" range can't overlap the "to" range. maybe paste somewhere else first and then move it (two steps, now). chanelg wrote: > > How do I rapidly move data from cells A1 - A5 to A1-F1? I did this a couple years ago and thought is was in paste special but no luck this time. -- Dave Peterson ec35720@msn.com Thanks Dave, its the can't overlap that was...

Named-range source-data for pie charts on copied worksheets
I have to finally concede that I'm stuck and have to ask for advice. I am using Excel 2000 and have been able to use Andy Pope's help on "Automatic removal of zero values in pie chart". That works fine. But if I copy the worksheet, the pie chart on the copied (new) worksheet references the named range as defined on the original worksheet. My named ranges, which the pie chart references, are: PieLabels=OFFSET(blank!$O$26,0,0,COUNT(blank!$N$26:$N$34),1) PieValues=OFFSET(blank!$N$26,0,0,COUNT(blank!$N$26:$N$34),1) So, one solution might be to define the same named range on dif...

FROM address with multiple accounts wrong
Hi: I have the following email accounts setup in Outlook: user1@mydomain.com (set as default account) user2@mydomain.com user1@mywork.net Everything is POP/SMTP. I select different accounts to send. Here is the behaviour: If I send an email to anyone it always goes out as coming from user1@mywork.net , regardless of what email I chose to send from. (yes, I've checked the email settings in Outlook, all email addresses are correct--I even tried setting a reply address as well--no joy). So all my replies come back to user1@mywork.net. Weird thing is--I can only get the FROM address to sh...

copy a workbook without copying any data
I there a way to copy a workbook without copying any data. I would like to copy all tabs,micros,links and formulas without any data in the cells or notes for the cells being copied. Thank You Hi Rusty, One way: Sub Tester() Dim sh As Worksheet Dim strName As String strName = "ABCD.xls" '<<==== CHANGE ActiveWorkbook.SaveCopyAs strName Workbooks.Open Filename:= _ Application.DefaultFilePath & "\" & strName For Each sh In Workbooks(strName).Worksheets On Error Resume Next sh.Cells.SpecialCells(x...

multiple Archive Folders in file list
Outlook 2002: I suddenly have an additional archive folder in the file list. Not Archive1 or some variation, but two separate Archive folders. And I can't delete the new one. If I click on it, the cursor automatically jumps to the original Archive folder (the good one). But I can click on the subfolders and even delete some of the contents. What can I do to get rid of it? Do you see the extra archive.pst listed if you go into File | Data File Management? Can you remove it from there? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answere...

tricky data entry question
I need to limit a cell's entries to numbers, commas and dashes, so entries such as 1,2,3,4-10,17 could be entered (these are just examples, the actual figures can go up to 999999). The tricky part is that I would also like to only allow an entry in this cell if another cell on the sheet is not empty. An example to summarise: I want to be able to say "allow an entry in this cell if cell A1 is not empty, and only allow the characters to be numbers, dashes or commas." Of course it may not be data validation that I need, (maybe a combination of number formatting and data vlidati...

Log data in a shared workbook
Hi all Is it possible to have a spreadsheet which logs data from anothe without over writing each other's data, even though several colleague are inputting and saving in this shared sheet at different times. Eg. Sheet 1 has various data Sheet 2 - A1,2,3, etc = first save Sheet 3 - B1,2,3, etc = second save Etc. ...for the day Cheers Joe -- Message posted from http://www.ExcelForum.com Joey Would you consider writing to a CSV file? You could use VBA's text file functions to write directly to the file, but the file would still open in Excel. One advantage would be that...

Code to look at range and extract data.
If the column of data has the text "Back On" I want to copy the adjacent columns to another sheet for each instance. This will be a loop since this text occurs more than once. If I understand you correct ? Start here http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JDJacobs" <JDJacobs@discussions.microsoft.com> wrote in message news:5B3B0D6A-CE19-426B-BBB6-F68B9AD6C2DB@microsoft.com... > If the column of data has the text "Back On" I want to copy the adjacent > columns to a...

Trouble applying data set to other shapes #2
Hello, I posted this earlier, but it may not have been sent. So I am sending it again. I have put together a data set for one of the objects in my Visio 2007 Professional drawing. For simplicity, let's say that the shape is a circle. I then want to apply the same data fields to another shape that's already on my drawing. Let's say that this other shape, that does not yet have a data set attached to it, is a square. So here is what I am doing, but apparently I am making a mistake somewhere: I click on the circle and look in the Shape Data Sets window, and I see the name...

Basic data filtering in Excel 97
Okay, what I need is pretty basic. I have a spreadsheet with 6 columns. The first column features keywords. Data related to that keyword is contained within the cells to its right. I need to know how I can filter the keywords based on criteria I specify. Basically I would tell it to display keywords and their data that contain the word -cell phone- (for example) and it would hide all the other data not containing the world -cell phone-. Seems like a pretty basic feature but I know almost nothing about Excel. Thanks in advance. :) ------------------------------------------------ ~~ Mes...

How do I create a rent balance sheet for multiple units
...

Special character in Input Message
I'm building a message in the "Input Message" tab of the "Data Validation" dialog. Manually, I can add a special character that is a large black triangle pointing to the right like a bullet. However, I can't seem to produce this character using code. Can someone please post an example where I can insert a special character using code? Thanks! ...

How do i have a check display with any data entry in a cell
I am creating a simple spreadsheet for teachers and want to have only a check mark displayed regardless of what they type into a cell. Insert the check mark symbol you want from the Insert-Symbol menus int a reference cell for your formula. Then your formula in B2 would be. =IF(A2>0,$H$2,"") A2 is where the data is entered by the teachers, $H$2 is where yo inserted the check mark symbol for your reference. You can then dra this down for each row of data. Cheers, Stev -- Steve ----------------------------------------------------------------------- SteveG's Profile: ...

Question about data in SMTP header
Does anyone know what the message below means? X-OriginalArrivalTime: 16 Dec 2005 18:53:28.0827 (UTC) FILETIME=[00B8B4B0:01C60272] -- Mike C On Mon, 19 Dec 2005 08:34:03 -0800, Mike C <MikeC@discussions.microsoft.com> wrote: >Does anyone know what the message below means? >X-OriginalArrivalTime: 16 Dec 2005 18:53:28.0827 (UTC) >FILETIME=[00B8B4B0:01C60272] What's to know? The field is explained here: http://www.ninebynine.org/IETF/Messaging/HdrRegistry/mail/X-OriginalArrivalTime.html and the only other part of that is the UTC thing, by which they really mean GMT. ...