Sheet lookup

Hello,

I have values that I would like to look up on different sheets rather 
than the same one, so I can't use vlooup(). So far I have been defining 
named ranges, but my formula is getting too long. Here is what I do:

SUM(IF(ISERROR(VLOOKUP($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),0,VLOOKUP
($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),...

Basically, see if the name can be found on the sheet, and if it can, add 
the vlookup'ed value in. On another note, why is vlookup() designed to 
return an error rather than a number?? That's my number 1 hated 
"feature" of excel.

Thanks,
Andrew
0
nospam7515 (2084)
3/14/2005 2:11:30 PM
excel 39879 articles. 2 followers. Follow

5 Replies
443 Views

Similar Articles

[PageSpeed] 33

Why do you think you can't use VLOOKUP to find data on another sheet?

0
eliffman (2)
3/14/2005 2:33:19 PM
VLOOKUP is fine over another sheet.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Andrew Clark" <nospam@nospam.com> wrote in message
news:1110809492.ee85fcbc1448f65b6fee4492873d4d5e@teranews...
> Hello,
>
> I have values that I would like to look up on different sheets rather
> than the same one, so I can't use vlooup(). So far I have been defining
> named ranges, but my formula is getting too long. Here is what I do:
>
> SUM(IF(ISERROR(VLOOKUP($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),0,VLOOKUP
> ($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),...
>
> Basically, see if the name can be found on the sheet, and if it can, add
> the vlookup'ed value in. On another note, why is vlookup() designed to
> return an error rather than a number?? That's my number 1 hated
> "feature" of excel.
>
> Thanks,
> Andrew


0
bob.phillips1 (6510)
3/14/2005 3:24:15 PM
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in 
news:e1SLknKKFHA.572@tk2msftngp13.phx.gbl:

> VLOOKUP is fine over another sheet.
> 

Yes, sorry. The problem I have is that to I will have many sheets like 
this and I need to include them all in the expression. When I put all the 
vlookups in the sum, the length exceeds the formula length limit. I have 
this expression:

IF(ISERROR(VLOOKUP($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),0,VLOOKUP
($A3,mar_03_batting,COLUMN(C$1)-1,FALSE))

repeated for each day, substituting _day_ for _03_. I was just lokoing 
for a way to simplify the above expression so I could include all of the 
days in one formula. As it is now, I can include 9 such expressions, then 
the formula becomes too long.

Thanks,
Andrew
0
nospam7515 (2084)
3/14/2005 4:06:42 PM
Either put the repeated formulas, such as

VLOOKUP($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)

in another cell and refer to that, or create a named range referring to that
and use the name in the final formula.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Andrew Clark" <nospam@nospam.com> wrote in message
news:1110816403.1bbaec0be1507ed52f9d157193d7e12a@teranews...
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
> news:e1SLknKKFHA.572@tk2msftngp13.phx.gbl:
>
> > VLOOKUP is fine over another sheet.
> >
>
> Yes, sorry. The problem I have is that to I will have many sheets like
> this and I need to include them all in the expression. When I put all the
> vlookups in the sum, the length exceeds the formula length limit. I have
> this expression:
>
> IF(ISERROR(VLOOKUP($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),0,VLOOKUP
> ($A3,mar_03_batting,COLUMN(C$1)-1,FALSE))
>
> repeated for each day, substituting _day_ for _03_. I was just lokoing
> for a way to simplify the above expression so I could include all of the
> days in one formula. As it is now, I can include 9 such expressions, then
> the formula becomes too long.
>
> Thanks,
> Andrew


0
bob.phillips1 (6510)
3/14/2005 4:34:32 PM
Andrew Clark wrote...
>I have values that I would like to look up on different sheets rather
>than the same one, so I can't use vlooup(). So far I have been
defining
>named ranges, but my formula is getting too long. Here is what I do:
>
>SUM(IF(ISERROR(VLOOKUP($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),0,VLOOKUP
>($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),...
>
>Basically, see if the name can be found on the sheet, and if it can,
add
>the vlookup'ed value in. On another note, why is vlookup() designed to

>return an error rather than a number?? That's my number 1 hated
>"feature" of excel.

If the name in A3 would appear at most once in the first column of
mar_03_batting and all other such ranges, don't screw around with
VLOOKUP. Enter all these table ranges in another range, easier to
maintain if this is a single column, multiple row range, and name the
*transpose* of this range of range names something like ListOfTables.
Then try the array formula

=SUM(SUMIF(OFFSET(INDIRECT(ListOfTables),0,0,,1),$A3,
OFFSET(INDIRECT(ListOfTables),0,COLUMN(C$1)-2,,1)))

0
hrlngrv (1990)
3/14/2005 6:44:16 PM
Reply:

Similar Artilces:

keyboard shortcut for moving between sheets
does anyone out there know of a keyboard shortcut for moving between sheets in a workbook? or, how i could create one? thanks, steve Hi CTRL+PGUP and CTRL+PGDOWN -- Regards Frank Kabel Frankfurt, Germany Steve wrote: > does anyone out there know of a keyboard shortcut for moving between > sheets in a workbook? or, how i could create one? > > thanks, > > steve Frank - you are my HERO! Thanks!! Steve "Frank Kabel" <frank.kabel@freenet.de> wrote in message news:OM7R5FpyEHA.2040@tk2msftngp13.phx.gbl... > Hi > CTRL+PGUP > and > CTRL+PGDO...

Excel 97 Hyperlink click hides sheet?
Haven't seen this happen before, but am using a sheet (put together with a copy paste from a web page) with multiple hyperlinks on it. When I click on a link, IE starts up dutifully and the proper page is displayed. When I select Excel again, the worksheet is hidden and I have to click around to display it again. Can't find anything in the help file that describes a way to turn this behavior on or off. I'd like the worksheet to still be visible when I come back. Any ideas? thanks in advance. Tom Try this: make an insignificant change to the worksheet. Undo it. (Now exce...

Sheet Level Names
How can I use the same name to define ranges on several different sheets? For example, I already have a named range on "Sheet X" called 'rng'. How do you use 'rng' for named ranges that will be on "Sheet Y" and "Sheet Z"? Can this need to be done through the Define Name dialog box or can it only be done using code? Thanks in advance. Keith Hi Keith, You can define local names using the Define Name dialog box: select sheet Y and the name is for example 'Sheet y'!rng and the refersto is for example ='Sheet Y'!a44+Globals!Z4...

Import Multiple files into separate sheets based on list
I have a list of file names in sheet1 of a workbook. I am trying to figure out how to use a macro to go down the list and for each one, add a new sheet, import the file, and rename the tab to the name from the list, then move to next cell in sheet1 until all the files have been imported. Can anyone give me a hand? My results keep trying to take the cell down on the each newly added page. On Sep 20, 11:46 am, mma40 <maria.as...@bellsouth.com> wrote: > I have a list of file names in sheet1 of a workbook. I am trying to > figure out how to use a macro to go down the list and for...

Criteria Lookup based on Dates
The "Current Plan" is the formula I'm trying to create: Starting Point: 1 A B C D 2 NAME PLAN Effective Date Current Plan 3 ---- ---- -------------- ------------ 4 ID1 1 1/1/2005 5 ID1 3 2/1/2005 6 ID2 2 5/1/2004 7 ID3 2 2/1/2005 8 ID3 4 9/1/2005 9 10 AS OF DATE: 11 3/1/2005 What I'm trying to get to is the "Current Plan" based on the "AS OF DATE" I put in. Ending Point: 1 A B C D 2 NAME PLAN Effective Date Current Plan 3 ---- -...

Recognising the user switch between Form view and Data sheet view
My Uncle has an Access application where he would like to sometimes switch from a Form's Form View to Data sheet view and back. He would like certain settings for each view (eg form maximisable when in Data Sheet view but not when in Form view,and certain fields hidden in data sheet view but visible in text boxes in Form view. So,is there an event which is fired when the form's view style is being changed,so that I could put appropriate coding there? Either Access 2003 or 2007 could be used,preferably 2003. I realize I could put a button on the form in form view, say,whe...

v lookup
I am inexperienced with excel so any suggestions are welcomed. I have two different spreadsheets with columns of name, hours worked, total wages. Each name has a different row for each type of wage. A person can have several rows. There are no subtotals. I want to know how to compare each spreadsheet and find the differences. Essentially I want to know if the total wages on the first spreadsheet match the second spreadsheet. If wages have been partially or completely dropped, or duplicated etc... a report would generate. Similar to balancing a checkbook. Thanks. This can be done diff...

Bridge Score Sheet Creation
I need to keep score for a bridge group. I need to create a diagonal line in A1 & A2, form top right of A1 to bottom left of cell A2. Scores will be in A1 & A2 on similairly paired cells across and down for teams and different weeks. The probelm I am having is if the column width changes on A1 & A2, the diagonal lines no longer meet to cell at the corners even using graph with connectors in the cell. Any solutions to this problem? ...

Printing four copies on one sheet
Hi, I have a document in A4. I would like to print out four copies of this document, their size reduced four times, on one A4 sheet of paper. My Canon iP4200 offers this option but will only print out one reduced copy, whatever option I choose. I cannot find Publisher to do the job. Selecting everything and manually reducing the document will not reduce the fonts, which remain at their original size. I would then have to enter each text box and manually choose a smaller font size, which is a real pain. Any idea? Thanks. Sam The font issue can be overcome by selecting AutoFit Text in t...

How to control the Sheet tab position
Hi, how can I control the position of a Sheets when adding one? For instance always before "SHEET1". Thank's in advance. Sheets.Add ActiveSheet.Name = "TMP" Bobby wrote: > Hi, how can I control the position of a Sheets when adding one? For > instance > always before "SHEET1". > Thank's in advance. > > Sheets.Add > ActiveSheet.Name = "TMP" Bobby, this is how you do it! Sheets("TMP").Select Sheets("TMP").Move Before:=Sheets(1) or Sheets("TMP").Move After:=Sheets(1) When adding a new ...

F9 vs. Calculate Sheet
Folks: I know that I can configure my spreadsheet to manually "re-calculate" by doing: TOOLS >> Options >> Calculation Tab >>. * What is the difference between "Calculate Now(F9) " and "Calculate Sheet" ? Thanks, John. F9 (calculate now) will calculate the workbook while shift+f9 (calculate worksheet) just calculate the worksheet One extra note: suro is right, but if there are too many dependencies then trying to calculate a range or a sheet will be escalated by Excel to the workbook. There's an excellent MSDN artic...

could someone help me with design of a sheet
Hi I want to add a background to my sheet and I wonder if it possible t remove the outline of the cells? So you don't have a "light grey grid over the background -- Message posted from http://www.ExcelForum.com One way: Tools/Options/View, uncheck the gridlines checkbox. In article <mowen.17iyza@excelforum-nospam.com>, mowen <<mowen.17iyza@excelforum-nospam.com>> wrote: > Hi > I want to add a background to my sheet and I wonder if it possible to > remove the outline of the cells? So you don't have a "light grey grid" > over the backgr...

Trouble with my Lookup Code
Hi Everyone. I’m adding a cost lookup to my db, based on the example in the Tradewinds database. I am trying to add a “CostPerFoot” value to the form automatically, based on the “MaterialType” entered by the user. Both of these fields are maintained in the “MaterialCostTable”. Here is what my ‘after update’ looks like on the “Material Type” field of the entry form: Private Sub MaterialType_AfterUpdate() On Error GoTo Err_MaterialType_AfterUpdate Dim strFilter As String ' Evaluate filter before it's passed to DLookup function. strFilter = "MaterialType =...

Lookup based on criteria in 2 columns
Hi, I am trying to use a vlookup or other function to return the value in the amount column based on the location and date. Here is a sample of my data: Location Date Amount 101 9/15/8 10 101 9/16/8 20 101 9/17/8 15 102 9/15/8 50 102 9/16/8 75 102 9/17/8 67 For example if I wanted to return the amount for location 102 on 9/15/8, what formula would I use? I tried using variations of vlookups but had no luck. Thanks, =SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50) -- Regards, Peo Sjo...

Totalling data from several workbooks to a Summary Sheet
I am trying (unsuccessfully) in creating a formula, that will allow me to total data from several workbooks, to a Summary sheet within Excel. Does anyone know how to complete this? A guess, although may not do what you want...? Maybe gives you a hint depending on your ultimate requirement. =SUM(Sheet2!F1:F5,Sheet3!G1:G5) HTH regards, Howard "Phillip Anderson" <PhillipAnderson@discussions.microsoft.com> wrote in message news:F1AC386F-B890-4132-BD26-CA3BBE158FC4@microsoft.com... >I am trying (unsuccessfully) in creating a formula, that will allow me to ...

How to add totals in one sheet to another sheet
How do I add cells in one sheet to cells in another sheet in the sam workbook? For example: Sheet one name is draw 1. Sheet two name is draw 2. I want to copy totals in D4 in sheet one to sheet two d4. Thanks for your help. Kati -- k.floy ----------------------------------------------------------------------- k.floyd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2867 View this thread: http://www.excelforum.com/showthread.php?threadid=48352 D4 on Draw 2 should be ='Draw 1'!D4 - Search "k.floyd" wrote: > > How do I add cells in o...

I want to email only 1 page of my excel sheet how do I do that
I have an excel sheet that has three pages to it. I want to be able to email only one of the pages to a client. Is there any way I can do that? You can send the selection or range Maureen See my site for a example http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Maureen" <Maureen@discussions.microsoft.com> wrote in message news:EB8883BB-90FB-424D-AC6A-D7899C1C5F96@microsoft.com... >I have an excel sheet that has three pages to it. I want to be able to email > only one of the pages to a client. Is there any way I can do that? ...

Lookup table form data ERROR
Ok, here's a new bug I can't find anywhere... It's MSPS2007 SP2 and a new install. When accessing the Lookup tables from PWA, the screen loads the boarder and trim as normal, but the grid for the actual values fails to load. The lower left of the window indicates 'Done', but with errors, clicking on the error for the details indicates something like "problems with this page might have prevented it from displaying properly..." the errors listed in the details are Line: 1330 Char: 8 Error: 'ExcelBtn' in undefined Code: 0 I have seen a vast nu...

Use userform to get input and compare to a hidden sheet
I am using a userform to get employees id number and passwords. I have the id numbers and passwords on a hidden sheet. I need to code in the submit button on the form a way to search the hidden sheet for there id number and password match and then if it does match take them to a specific sheet. Should I be going about this in a different manner or is this possible? Thanks First, worksheet protection is pretty easy to break. In just moments, everyone can see your list of id's/passwords. And the protection for the VBA project is easy to break so that users can see your code, too. ...

how to set up same name range in different sheets for different d.
please repost with a brief subject line and the question in the message body as it has been truncated. Cheers JulieD "kailash" <kailash@discussions.microsoft.com> wrote in message news:7740EA1A-9D11-4C3D-8499-3C7CDBC92D65@microsoft.com... > Hi see: http://www.xldynamic.com/source/xld.Names.html -- Regards Frank Kabel Frankfurt, Germany "kailash" <kailash@discussions.microsoft.com> schrieb im Newsbeitrag news:7740EA1A-9D11-4C3D-8499-3C7CDBC92D65@microsoft.com... > ...

Lookup Question 04-17-10
I have a worksheet that has Zip codes that needs to be matched with a master zip code list with assigned names. One worksheet has a column called “Ship Zip” the other worksheet has two columns that I need to relate to the “Ship Zip” column, the first is a column “Zip Code” and the second is a column “Sales Person” the hope is to set a formula in the worksheet with the “Ship Zip” that would pull both columns from the other worksheet, if not I would need to pull the column “Sales Person” that provides the exact match of the zip codes in both worksheets. Without ...

Lookup text with multiple search criteria
I know how to look up data and text using vlookup and hlookup, what I' like to know is whether there is a way of looking up data based on mor than 1 search criteria; for example If I wanted to look up an item in a 4 column database, I'd us something like this: =VLOOKUP("Apples",A2:D10,4,false) But that only looks for Apples. Say I wanted to lookup data based on subcategory of Apples, e.g. colour. If it was a number, I could use SUMPRODUCT and (assuming named range were in use) do it like this: =SUMPRODUCT((Fruit="Apples)*(Colour="Red")*(Total)) But obvio...

Merging sheets...
I've got a workbook with two sheets. One sheet contains all the fields and data I need except it only lists one status report per event, but some events have multiple status reports. The extra reports are listed on the second sheet and each status report is linked by a key (number) field (one-to-many relationship, but since it's a spreadsheet there isn't an actual relationship beyond organization). What I need is to get one sheet that has a single entry for each status report. Say the first event had three status reports, I need all the information from each field on ...

copy a formatted cell to another sheet as text without format
I have a formatted sheet with an end result in a formatted cell. I want to take that column and copy it to another sheet but minus the format since I get the "value" error. How can I just get the numbers as numbers? What does the format have to do with the "value" error? Do you mean a formulated result? I take it you are trying to paste the result of a formula into an empty cell? In that case simply copy the cell with the formula and right click where you want to paste, and then select paste special. Select values and hit ok. This will paste the result of the ...

Automatically update pivot table when switching to sheet that the pivot table is in
What would the code be to automatically refresh a pivot table when you open the sheet that the pivot table is in? Today, I changed data in the sheet that the pivot table draws from and forgot to refresh the pivot table. Fortunately, I caught my error in time and refreshed the table before I gave it to the boss. I would like the pivot table to refresh whenever I switch to the sheet that the pivot table is in. Thanks Try using the Worksheet_Activate event to update the PivotTable like: Private Sub Worksheet_Activate() PivotTables("PivotTable1").RefreshTable End Sub hth, jay <b...