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

Similar Articles

[PageSpeed] 22

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:

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...

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...

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...

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...

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. ...

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
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...

incorporate paste value in vlookup
is there a way to incorporate "paste value" in a vlookup formula ? I have the vlookups in the column working and feeding off of the multiple worksheets. the data will be updated weekly (and new columns with the updated data added) and i would rather just copy new data on top of the old in the worksheets instean of having to add new worksheets with new data... as to keep the previous column from changing. -- problem ...

vlookup inside an if statement?
-------------------------------------------------------------------------------- Hi everybody! I have to do a vertical lookup of an object that might or might not exist, in case it doesnt exist normally it gives back Ref! but the thing is that if this objet doesnt appear on the data base is because is zero. This Ref! is not allowing me to make a sum since this objet is part of it. Maybe with an example would be easier to explain. i have to pull from a database the number of cars sold of three brands A,B, C, In the database unless is one car of the type it will not appear. lets say we hav...

Nested vlookup
My formula is: =((VLOOKUP($W$5181,Download!$A$1:$X$3403,3,FALSE))+(VLOOKUP($W$5182,Download!$A$1:$X$3403,3,FALSE)))*0.001 The formula looks up the value in "$W..."and returns the amount of orders. I alwayshave values for the first part of the lookup, but niot the second. THe issue is that if there nothing to report back for the second lookup, it gives me an "#NA" errror instead of at least returning the value for the first lookup. Any help? Thanks, TJ Wrap both Lookups in their own ISNA() wrapper to trap for that error:- =(IF(ISNA(VLookup1),0,VLookup1)+IF(ISNA(VL...

Vlookup?
I am trying to sum the cells in column "AY" for each row that has a positive value in column "S" I am very confused and this is what I have so far. Can you help me please? =SUM(VLOOKUP($S$3:$S$502>0,$S3:$AY502,33,FALSE)) Try =SUMIF(S3:S502,">0",AY3:AY502) Hope this helps, Hutch "Doug" wrote: > I am trying to sum the cells in column "AY" for each row that has a positive > value in column "S" > I am very confused and this is what I have so far. Can you help me please? > > =SUM(VLOOKUP($S...

Why #NA when using VLOOKUP?
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C7C7AD.F7E2E420 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I'm trying to use VLOOKUP to find lowest value in a small group. The = exact sample is below: A B 1 3.0001 A=20 2 2.9442 B=20 3 2.9610 C=20 4 2.9055 D=20 5 2.9630 E=20 The formula I'm using is =3DVLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to = get it to return the lowest cost, from column A. If I take out Row 4, = it wo...

VLookup in other document
Here is my problem. Please help I have two documents A and B. In document A I have a table existing of codes and descriptions Range A1: B100 Column A contains codes, Column B contains descriptions In document B I want to lookup the description for a given code. I have a cell C10 containing the code and a cell C11 containing a formula =VLOOKUP(A1;'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200;2) Till so far it works !! But I use this formula many times in my excel sheet and in many Excel documents, so if Ithe lokation of document A changes, I have to change all the for...

#N/A error with VLOOKUP
My VLOOKUP was working fine, but now I have added some text boxes and pictures around the sheet itself. I have allowed for the extra column, but the VLOOKUP isn't working anymore. Does it not like pictures? I haven't included these in the area it is looking at, so it shouldn't get confused. Unlike me... -- Michelle Tucker Hi Michelle the #NA error can occur when the VLOOKUP has nothing to lookup ... so my first suggestion would be to check that the VLOOKUP is still referencing the correct cells in your workbook. if that doesn't help, please post your exact VLOOKUP sta...

Vlookup no working
I have only 2 worksheets in column A is the item # which sometimes has a letter associated with it as in 815NB in column B is the desctiption. This formula works fine when the cell only contains a number but will not including the letter. I can manually retype the cell info and the formula will find it even with the letter association. Worksheet 1 has 14000 rows and Worksheet 2 has 3000 rows, so I only need some of the information from the 1st worksheet. The formula I'm using is =IF(ISNA(VLOOKUP(A1,LKF!A:B,2, FALSE)), "", VLOOKUP(A1,LKF!A:B,2, FALSE)) Sorry for the length. I ...

VLOOKUP and multiple rows ?
I have two worksheets: #1 contains client numbers and data. client no. data 1 xyz 2 abc 3 def 4 wed #2 contains client numbers and the employees attached to the client. client no employee 1 joe 1 tom 2 jim 3 ann **Here's my problem! Is there a way to do a lookup on spreadsheet # 1 to bring in the employee data from spreadsheet #2 (a normal VLOOKUP) and if there are mutiple employees to either concatenate the data-> client no employee 1 joe,tom or put it...

VLookup?????
=IF(ISNA(VLOOKUP(B2,'TableB'!$Y$2:$Y$100,1,FALSE)), "",Column A in tableB) This is the code i have. I want to search tru all column B in table A. If i find an identical value in table B i want the field in table A to show the corresponding field in Column A in table B. Also if there is 2 occurences of column B in table B, i want the field in table A to show both values. Any ideas? (if u can understand what im asking) -- Hazy ------------------------------------------------------------------------ Hazy's Profile: http://www.excelforum.com/member.php?action=g...

VLOOKUP
Hi All I have the following formula: =IF($D$9="","",VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)) which works fine. Problem: How do I stop the value *0* displaying, if there is no data in worksheet List! column 7 ? Many thanks George George, =IF($D$9="","",IF(VLOOKUP($D$9,List!$A$1:$G$500,7,FALSE)=0;"";VLOOKUP($D$9,L ist!$A$1:$G$500,7,FALSE)) -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "George Gee" <george.nomaps@ntlworld.xom> wrote in message news:Otk8AZ4$DHA.1...

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. Do you want the data for all rows wit...

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 ...

vlookup probs
hello I want to put in a vlookup formula so that i can change a referance number in a cell and it will give me the relavent information ie 3000 will refer to bill 3002 will be harry etc. The formula i have put in is :- =VLOOKUP(D3,data,3) where d3 is the cell with the numbers i want to change to get different information. So ideally it will look for the value i type in d3 in the range i have called data and give me the value in column 3 on the same row within the data field. I hope i have explained clearly. thanks jim I should add that data is on sheet 1 of the workbook, i think this is w...

VLOOKUP #45
I am running a vlookup for part #'s at my company. The table that I have may sometimes contain the same part numbers multiple times. I want my vlookup to look at all of the same part number and give me a total of the column that I'm looking in. For example PART # QUANTITY 15300001 100 15906231 52 15308526 78 15300001 69 Now when I do a lookup on a separate worksheet underneath the part number 15300001 I want my quantity to show 169. Is this possible?...Please help. Thanks in advance. Travis Dahlman ...

=vlookup
I m preparing invoice for my company which have multiple sheets like Customer List and Pipe Size detail with rate of specific pipe. I want to locate rate of specific pipe in Invoice. For this purpose I m using vlookup function but when in that position second row is blank or same then it returns #N/A. I just want only want that it should be returned blank when row of Pipe detail remain blank. My worksheet have this type of data. A B C D E F G Description No. of Pipes (Qty in Mtr) Total Meter Rate Amount 1 063mm x 4Kg TKT 25 6 150 17.42 2613.00 2 075mm x 4Kg TRN 25 6 150 21.76 3264.00 ...