vlookup?

Hello and thanks for looking at my project. I would like to create a
form on sheet 2 getting data from sheet1 and I am using vlookup but
I can only get 1 row. The first column (A) of my data range is Team
Name The 2nd column (B) is Player and each column after that has
some sort of stat. There are 150 rows of names and 10 Team names and
all sorted by Player. The first sheet must remain sorted by Player.
On sheet2 in cell A1 I would type the name of the team that I would
like stats on. Row 2 cell A2 would start with player, B2 C2 D2 and so
on would have stats all from sheet1. Vlookup did great for row 2 but
how do I get to players 2 thru 15. When completed my form should
return, when I type a team name in cell A1, all players and stats in
15 rows for that team so I can print each team separately. Thanks
again for looking.    carri  :)

-- 
CARRIANN


0
jrocha38 (2)
1/13/2004 5:29:48 AM
excel 39879 articles. 2 followers. Follow

2 Replies
685 Views

Similar Articles

[PageSpeed] 1

Why not just do a Data > Filter > Autofilter on row1 in Sheet1?

You could then select the specific Team from the drop menu in col A,
 and print the filtered rows.

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------
"Johnny1r" <jrocha38@comcast.net> wrote in message
news:gnLMb.39950$nt4.74308@attbi_s51...
> Hello and thanks for looking at my project. I would like to create a
> form on sheet 2 getting data from sheet1 and I am using vlookup but
> I can only get 1 row. The first column (A) of my data range is Team
> Name The 2nd column (B) is Player and each column after that has
> some sort of stat. There are 150 rows of names and 10 Team names and
> all sorted by Player. The first sheet must remain sorted by Player.
> On sheet2 in cell A1 I would type the name of the team that I would
> like stats on. Row 2 cell A2 would start with player, B2 C2 D2 and so
> on would have stats all from sheet1. Vlookup did great for row 2 but
> how do I get to players 2 thru 15. When completed my form should
> return, when I type a team name in cell A1, all players and stats in
> 15 rows for that team so I can print each team separately. Thanks
> again for looking.    carri  :)
>
> --
> CARRIANN
>
>


0
demechanik (4694)
1/13/2004 6:07:13 AM
Hi

Sorry, but I don't have enough time to adjust my example below (it is a
copy-paste from my answer in one Excel-NG's from last year) to your task.
Anyway I think they are similar enough, so you can use it to get some
ideas - a part most interesting for you begins with OrderRepTemp sheet.

*******
On fly, something like this has to work for you (probably you have to
replace all ; in formulas with , - it was too much of them for me to do it
here):

With empty workbook, you create worksheets Orders, Details, Items, OrderRep
and OrdRepTemp

Define named range (Insert.Name.Define)
VAT=YourVAT%
p.e. VAT=0.18

On sheet items, you have columns
Item, MeasuringUnit, UnitPrice - you have to fill the table.

You create 2 named ranges
Items=OFFSET(Items!$A$2;;;COUNTIF(Items!$A:$A;"<>")-1;1)
ItemsTable=OFFSET(Items!$A$2;;;COUNTIF(Items!$A:$A;"<>")-1;3)
(when you have more columns in Items table, replace number 3 in second named
range with your number of columns)

On sheet Orders, you have columns
Order, Date, Order, Customer, OrderSum, OrderVAT
(first Order column is hidden, I needed it while I wanted Date entered as
first, but for lookups I need Order sa leftmost)
A2=IF(C2="";"";C2)
C2=IF(B2="";"";IF(ROW(C2)=2;1;IF(C1="";"";C1+1)))
Define named ranges
Orders=OFFSET(Orders!$C$2;;;COUNTIF(Orders!$B:$B;"<>")-1;1)
OrderTable=OFFSET(Orders!$A$2;;;COUNTIF(Orders!$B:$B;"<>")-1;6)
D2=IF(C2="";"";ROUND(SUMPRODUCT((DetOrder=C2)*(DetSUM))/5;2)*5)
E2=IF(C2="";"";ROUND(SUMPRODUCT((DetOrder=C2)*(DetVAT))/5;2)*5)
NB! 2 last formulas round to 0.05. When you need otherwise, adjust formulas.
Format the cells in range A2:E2 and copy down for so much rows you think you
do need

On sheet Details, you have columns
Order, Date, Customer, Item, MeasUnit, UnitPrice, Amount, Price, VAT, Sum
Define named ranges
DetOrder=OFFSET(Details!$A$2;;;COUNTIF(Details!$A:$A;"<>")-1;1)
DetSum=OFFSET(Details!$J$2;;;COUNTIF(Details!$A:$A;"<>")-1;1)
DetVAT=OFFSET(Details!$I$2;;;COUNTIF(Details!$A:$A;"<>")-1;1)
Format enough of cells (starting from A2) in column A as combo using
Data.Validation.List with Source=Orders
B2=IF(A2="";"";VLOOKUP(A2;OrderTable;2;FALSE))
C2=IF(A2="";"";VLOOKUP(A2;OrderTable;4;FALSE))
Format in column D same number of cells (starting from D2) as in column A as
combo using Data.Validation.List with Source=Items
E2=IF(ISERROR(VLOOKUP(D2;ItemsTable;2;FALSE));"";VLOOKUP(D2;ItemsTable;2;FAL
SE))
F2=IF(A2="";"";VLOOKUP(D2;ItemsTable;3;FALSE))
H2=IF(OR(A2="";G2="");"";F2*G2)
I2=IF(OR(A2="";G2="");"";J2-H2)
J2=IF(OR(A2="";G2="");"";H2/(1-VAT))
NB! In my example, the VAT is calculated from endsum. When it's calculated
from Price, adjust last 2 formulas accordingly.
Format cells in range A2:J2, and copy down - again for so much of rows you
think you need.

It's all you need to enter Order and Order Detail info. Now you do need a
report to print one selected order.

On sheet OrderRepTemp, enter the formula
=IF(Details!$A2=OrdRep!$B$1;Details!A2;"")
and copy it to same range, as table on Details. You can add column names
into 1st row too.
Add 2 columns (Row and Rank)
K2==IF(A2="";"";ROW(A2))
Rank=IF(ISERROR(RANK(K2;K$2:K$xxx;1));"";RANK(K2;K$2:K$xxx;1))
where xxx is number of last row with formulas on Details table. Copy both
formulas too down.
Hide the sheet.

On OrdRep sheet, you must have some cell formatted as Data.Validation.List
with Source=Orders
Get rest of info from Orders Sheet (Date, Customer, etc), using VLOOKUP
function. P.e. with Order in B1:
Date=IF(ISERROR(VLOOKUP(B1;OrderTable;2;FALSE));"";VLOOKUP(B1;OrderTable;2;F
ALSE))
Customer=IF(ISERROR(VLOOKUP(B1;OrderTable;4;FALSE));"";VLOOKUP(B1;OrderTable
;4;FALSE))
You can put them into any cell on sheet, and move them frpm one location to
another.
To get details list into order, enter into some cell for first item code the
formula:
=IF(OFFSET(Details!D1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";OFFSET(
Details!D1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
into same row
Unit=IF(OFFSET(Details!E1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";OFF
SET(Details!E1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
UnitPrice=IF(OFFSET(Details!F1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"
";OFFSET(Details!F1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
Amount=IF(OFFSET(Details!G1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";O
FFSET(Details!G1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
Price=IF(OFFSET(Details!H1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";OF
FSET(Details!H1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
VAT=IF(OFFSET(Details!I1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";OFFS
ET(Details!I1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
SUM=IF(OFFSET(Details!J1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0)="";"";OFFS
ET(Details!J1;MATCH(ROW(D$1);OrdRepTemp!$L$2:$L$30);0))
Copy the range with those formulas for so much rows you need maximally on
your report
Add summary - you can sum details on report sheet, or you can take them from
Orders sheet using VLOOKUP, or you calculate them from Details sheet using
SUMPRODUCT.
Format report sheet as you like, and add any texts you need to it.

That must be all!
*********


"Johnny1r" <jrocha38@comcast.net> wrote in message
news:gnLMb.39950$nt4.74308@attbi_s51...
> Hello and thanks for looking at my project. I would like to create a
> form on sheet 2 getting data from sheet1 and I am using vlookup but
> I can only get 1 row. The first column (A) of my data range is Team
> Name The 2nd column (B) is Player and each column after that has
> some sort of stat. There are 150 rows of names and 10 Team names and
> all sorted by Player. The first sheet must remain sorted by Player.
> On sheet2 in cell A1 I would type the name of the team that I would
> like stats on. Row 2 cell A2 would start with player, B2 C2 D2 and so
> on would have stats all from sheet1. Vlookup did great for row 2 but
> how do I get to players 2 thru 15. When completed my form should
> return, when I type a team name in cell A1, all players and stats in
> 15 rows for that team so I can print each team separately. Thanks
> again for looking.    carri  :)
>
> -- 
> CARRIANN
>
>


0
garbage (651)
1/13/2004 6:40:17 AM
Reply:

Similar Artilces:

VLOOKUP #N/A Error
I keep getting a #N/A error even though I think my syntax is correct. I am entering: =VLOOKUP(CELL REF TO VALUE I'M LOOKING FOR, PAGE AND ARRAY REF FOR POTENTIAL VALUES, # COLUMN THAT CONTAINS RESULTS, FALSE) Does anyone know why I would get a #N/A error? I have tried sorting the data and starting with an array value greater than my lookup value, and I know that all of my lookup values have matches in the array. Any ideas? If you're certain that there's a match, then the usual suspects are: Leading/trailing/extra spaces in either the cell reference (or the key column in ...

error (bug?) with VLOOKUP function
Hallo, I have a problem with VLOOKUP function (excel 2007). I tried what follows several times and i always get the same error/ considere table (B5:C9) sorted according the first column: name salary baba 400 bibi 600 bobo 200 bubu 300 cel B13 contains: bibi cel B14 contains: baba in D13: =VLOOKUP(B13;B$5:C$9;2) and i drag it to D14 Result: in D13: 600 (this is correct) in D14: #N/B Each time, with the first name only of any sorted table, i get that error. Any explanation? Thanks Luc see answer in other newsgroup -- Kind regards, ...

Vlookup across 3 worksheets
Hi, I know there are probably a 100 answer's already posted about this but I can't get one to match. I have a Vlookup formula that i can get to work looking across 2 worksheets but when i add the 3rd it says "too many arguments". my existing formula is: =IF(ISERROR(VLOOKUP($A97,'sheet1'!$A$3:$AR$99,11,FALSE)),VLOOKUP($A97,'sheet2'!$A$4:$AR$98,11,FALSE),VLOOKUP($A97,'sheet1'!$A$3:$AR$99,11,FALSE)) I want to add sheet 3 in there but it returns the error. I'm using consistent formatting $'s & the worksheets are uniformly set o...

Vlookup 04-05-10
COL of codes in A (full List of codes of about 2,000) COL of codes in B (selection of COL A codes of about 850) IF COL B code matches COLUMN A code, then true, else false Hi, in C1 enter =IF(SUMPRODUCT(--(B1=$A$1:$A$2000))>0,"True","False") copy formula down "Katerinia" wrote: > COL of codes in A (full List of codes of about 2,000) > COL of codes in B (selection of COL A codes of about 850) > > IF COL B code matches COLUMN A code, then true, else false I need to basically widdle away data in a worksheet, using the criteria...

Vlookup with Images
I'm currently using vlookup to select paragraphs of information depending on a dropdown list. The vlookup refers to an array in another sheet. Is there any way of showing an image as well so that when I pick from a dropdown list, it selects an image from one of the cells in the lookup array? Thanks Mike Mike Yes, there is a way. You can employ event code and VLOOKUP with Data Validation list selection to choose which picture to show? See JE McGimpsey's site for more on lookuppics and a sample workbook. http://www.mcgimpsey.com/excel/lookuppics.html Gord Dibben MS Excel ...

Vlookup question #5
I have to search and put values in one of my sheets after looking in a field of a field. I believe i need to to a nested vlookup, but im not sure how to go about it. I copied the relevant portions of the data below: Department PriceType Monthly Average NY NY Average $1,000.00 NJ NJ NJ NJ Average $1,500.35 I have to search within a department (for example NJ), and then search where average is occuring in the Price Type field, and then pick the corresponding Monthly a...

Vlookup #11
I am trying to use a vlookup to find a id number from a range of cells and if found have it insert the data form the column 2. It seems to be working but it is not pulling the exact number. here is my formula. =VLOOKUP(E6,$BM$2:$BN$22,2) Column E6 contains all the Id numbers The array also has the id's with a Grade in column 2 How can I make it pull the exact number that is looking up. Thanks for your Time If not sorted by number use ,0 or ,false at the end. Look in the help index for VLOOKUP =VLOOKUP(E6,$BM$2:$BN$22,2,0) -- Don Guillett Microsoft MVP Excel SalesAid Software d...

Vlookup and if formula 04-14-10
EUR GBP USD Jan-09 1.1205 1.0000 1.4500 Feb-09 1.1261 1.0000 1.4573 Mar-09 1.1317 1.0000 1.4645 Apr-09 1.1374 1.0000 1.4719 May-09 1.1431 1.0000 1.4792 Jun-09 1.1488 1.0000 1.4866 Jul-09 1.1545 1.0000 1.4940 Aug-09 1.1603 1.0000 1.5015 Sep-09 1.1661 1.0000 1.5090 Oct-09 1.1719 1.0000 1.5166 Nov-09 1.1778 1.0000 1.5242 Dec-09 1.1837 1.0000 1.5318 I need the formula to return a correct value based on month and currency. I have came up with this =IF(B28=B$1,VLOOKUP(A28,A$2:E$25,2,0),IF(B28=C$1,VLOO...

Help Please with Dropdown lists and Vlookup
How can I integrate using a multiselect dropdown list and vlookup function? I have gone to "Contextures" website but do not see this type of example. I am trying to setup a form with a couple of list boxes where if I select the first box and select an item(s) they will be filled in the adjoing cell and the other list boxes will be populated with the data pertaining to the item selected in the first list box. Any help or reference to any site will be greatly appreciated. Hi Totally Confused, You say that you have looked at the Contextures site, but did you look at Debra Da...

VLOOKUP Problem in EXCEL 2010
I have a reference table (named SPOTS) that looks like this Date GoldSpot Silver Spot 2/9/2012 $1,722.00 $33.59 2/21/2012 $1,753.00 $34.15 2/22/2012 $1,754.90 $34.19 2/23/2012 $1,780.00 $35.35 2/24/2012 $1,781.00 $35.41 12/31/2099 In a second table, I want to use VLOOKUP to find a match in Column A and return either Column B or Column C on the row that contains the match. The second table (VALUES) looks like this (much simplified) after update: Date GoldSpot Quantity Value 2/9/2012 $1,722.00 3 $5166.00 2/21/2012 $1,753.00 5 $8765.00 2/22/2012 $1,754.90 1 ...

Vlookup ref!
We are doing a vlookup for a date cell in both workbook which is in column D this is the formula =VLOOKUP(B10,'C:\Documents and Settings\Administrator\My Documents\[calling_.XLS]Customers'!$A$4:$I$58,4,FALSE) but we are getting a #REF! both are date fields also it is in row 30 column d? Did you type that reference in? If so then open up both workbooks, tile them so you can see them both and then use the mouse to select that range on the calling_.xls workbook from within the formula. Close the target workbook and it will automatically create the correct range reference for you...

Matching and VLookups
I have just used some vlookups to find some information on a Unique ID. Unfortunately each week a few of these ID's are deleted and some are added- so a vlookup can not find information pertaining to them. The good news is that the data is sorted in such a way that when a vlookup returns a NA, b/c it can't find info on the related to the ID, that all data above it is what I need. For instance, after running a Vlookup I left with: ID's Region location 1 NY NYC 2 NA NA 3 CAL LA 4 ...

Vlookup and min formula
I have a worksheet that has dates in column B and file # in column A. In cell J12, i have this formula =min(B2:B100), in L12, I have a vlookup to return the information in column A that matches the date =vlookup(J12,A2:B100,1,0). I receive a #name error. Any ideas? VLOOKUP goes left to right. Are you able to move dates to A, and file names to B? what about having filenames in both A and another column to the right of the dates column (say in column C enter the formula =A12 in cell C12... then change the range in your VLOOKUP to B:C)? since your lookup value is a date returned wit...

Excel 2007: Vlookup Problem
I don't use Vlookup much and it's not working for me. I know it's user error so can you tell me what I did wrong? I have two worksheets in my file. In the first one, I have a list of addresses with a full unabbreviated US state name in column T, row 2. Column U is blank because I want to put the two-digit state code in there (starting with U2). In the second worksheet I have the full state name in Column A and the two-digit code for it in Column B (rows 1-50). Here's my formula starting in U2: =VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE) What is wrong? ...

vlookup returns na
Hi all! Sheet1 contains the following: Col_H Col_O 302 33937 158 32840 3582 73345 39760 73455 4412 AGL92 2941 AHW92 136 30044 Sheet2 contains this: Col_H 34132 34332 33532 31032 34433 34633 33937 This is the function I use along Col_I: =IF(ISNA(VLOOKUP(H1;Sheet1!$H$1:$O $7;1;FALSE))=TRUE;VLOOKUP(VALUE(H1);Sheet1!$H$1:$O $7;1;FALSE);VLOOKUP(H1;Sheet!$H$1:$O$7;1;FALSE)) For H7 I would expect the function to return 302, but it only retuns #N/A for all rows in Col_H. I use concatenate to generate the data in Col_O in Sheet1. I also tried to copy the valu...

Keeping Source Formatting with Paste Link and VLOOKUP
I am creating a paste link from worksheet A to worksheet B. The cell on worksheet A is formatted with one word in regular arial font and the second word in italics. Is there a way to keep this formatting in a paste link at the destination (worksheet B)? So far, I have tried the paste link and immediately followed with the paste special format, but this has had no effect. Likewise, I am attempting the same exercise with VLOOKUP. Would anyone have a solution? Thanks! First, formulas can return values--they can't change formats. And second, excel doesn't support that kind of ...

Sorting help-vlookup?
Worksheet2. Column A is the name of a project Column B is a date Column C is word count Column D is photo count There are about 400 rows of information under those headings. My problem is that Column A is messed up. The same project may be named two or three different ways. I need to find the name of a project by keyword (vlookup?) and by the latest date. Then I need that row to appear on a new Worksheet3 under official names that I define. If this is a vlookup, I'd really appreciate a formula that allows me to look up the keyword "Clack" and put that row under my row called...

combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...

Vlookup Help #6
Hi, please help me out: i have two excel files: First File contains few numbers in A column: eg: 5106134 5123866 773832 Now, i have another file which conatins some codes for these numbers but please note that the this file may conatain thie same number mor than 1 time as well, and all the times the code may be same or may not eg: Second File: 5106134 SRST 5123866 ACSS 5106134 RPRG 773832 CHFO 5123866 SRST 5106134 CUED 5123866 ACSS 5123866 CUED NOW, I AM APPLYING THE FORMULA IN FIRST FILe (VLOOKUP), AND I M GETTIN THE RESULT AS: 5106134 SRST 5123866 ...

copy formula, VLOOKUP
Unable to copy formula to other cells =VLOOKUP(A2,Sheet2!$A$1:$B$4,Sheet2!$A$1:$A$4,FALSE). I have trie everything I can think of to copy this formula. Thank yo -- Message posted from http://www.ExcelForum.com The third argument should be the column number from which you want to return the result. Since your range is only two columns, your formula could be: =VLOOKUP(A2,Sheet2!$A$1:$B$4,2,FALSE) BRustigian < wrote: > Unable to copy formula to other cells, > =VLOOKUP(A2,Sheet2!$A$1:$B$4,Sheet2!$A$1:$A$4,FALSE). I have tried > everything I can think of to copy this formula. ...

VLOOKUP Headache
Hi all, I have a major problem with using VLOOKUP, here is a simple example of what i want to achieve... I have a spreadsheet with 5 worksheets; MAIN ARSENAL ASTON VILLA BIRMINGHAM BLACKBURN on the 'MAIN' worksheet the columns are A DATE B TEAM1 C TEAM2 D TEAM1 AVG E TEAM2 AVG There are hundreds of lines of data for 'DATE','TEAM1'&''TEAM2' I want to do a VLOOKUP in column D 'TEAM1 AVG' that looks at the date in 'DATE', looks at the team in 'TEAM1' and goes to the relevant worksheet for that team then looks for that date and...

vlookups and hyperlinks
Hi, I am a conservation biologist working in Australia and unfortunatel I do not have a comprehensive knowledge of excel. I am in the proces of creating a series of workbooks each of which contain lists of large number of vegetation types which occur in particular geographi regions. Currently I am manually inserting hyperlinks from each typ to a detailed description of that type (which are all held on separate worksheet in the workbook). I have been attempting t automate this somewhat by using vlookup to reference to a list o hyperlinks. However rather than returning a hyperlink referen...

vlookup
I have an excel XP workbook with multiple worksheets where I need t lookup values in one spreadsheet and put the values in a cell i another worksheet. I have many columns that I need to lookup within my lookup range an certain of the columns are entered in alpha order. However, if I wan to insert new columns all of the existing column references in m lookup formula are now incorrect if they appear to the right of th inserted columns. Is there a way of refering to the coulmns relativel (e.g. 4 cols the the right of col A, which then get renumbered as ne columns are inserted to the left of them...

Vlookup hyperlink
Dear All I am using Office 97 :( And want to try to do a vlookup but the value I want to retrieve is a hyperlink, when doing this it returns the text but the hyperlink doesn't work Is there anyway this can be done Thank Adam Hi Adam try something like =HYPERLINK(VLOOKUP(....)) -- Regards Frank Kabel Frankfurt, Germany Adam wrote: > Dear All, > > I am using Office 97 :( And want to try to do a vlookup but the value > I want to retrieve is a hyperlink, when doing this it returns the > text but the hyperlink doesn't work. > > Is there anyway this can be done? ...

Deleting Formulas... After using =VLOOKUP
Hi, I'm trying to delete the formulas in an entire speadsheet all at once. The cells are linked to another file, and I just need some of th information from cells in the other file, and now when I open the ne one, It requires an update from the second.... This is what I did. 2 separate reports... Report A (the one I need) containing certai columns of information needed, and is constantly input into by man users.. roughly 500 employee's worth of data.. and Report B .. exported from our database.... with a total number of about 300 employees... containing information that is and is...