Consolidate and Lookup

Hi all,
I receive 8 reports monthly, 1 for each area, with the all the activity

for that period. I would like to consolidate the data into one total
sheet. I have a master sheet with all the specialties where I would
like to have all the totals show. The activity will be different each
month so the the number of rows may change. The number of columns is
constant.

Below is what I have at the moment... but it cant be re-used as the
range is fixed. It also produces an N/A error if one produces an NA.


$B7 refers to a specialty code.
=3DVLOOKUP($B7,'Herstmere
PCT'!$B$5:$N$19,D$5,FALSE)+VLOOKUP($B7,Welhat!$B$5:$N$20,D$5,FALSE)+VLOOKUP=
=AD($B7,'N

Herts'!$B$5:$N$24,D$5,FALSE)+VLOOKUP($B7,'N
Herts'!$B$5:$N$24,D$5,FALSE)+VLOOKUP($B7,'SE
Herts'!$B$5:$N$20,D$5,FALSE)+VLOOKUP($B7,RBBS!$B$5:$N$6,D$5,FALSE)+VLOOKUP(=
=AD$B7,W3R!$B$5:$N$27,D$5,FALSE)+VLOOKUP($B7,Dacorum!$B$5:$N$22,D$5,FALSE)+=
VLO=ADOKUP($B7,Dacorum!$B$5:$N$22,D$5,FALSE)



What is the best way to achieve this

0
9/12/2006 2:18:49 PM
excel 39879 articles. 2 followers. Follow

1 Replies
738 Views

Similar Articles

[PageSpeed] 23

As you are looking for exact matches, you can make the ranges much
larger than you would expect (eg $B$5:$N$100), so that you do not have
to adjust them each time you do this.

A common way of avoiding #N/A is a formula along the lines of:

=3DIF(ISNA(vlookup_formula),0,vlookup_formula)

However, as you have 9 vlookups (or should it be 8 ? - your final one
is repeated), that would be a bit clumsy.

Would it be possible to return the values from the other sheets into
individual columns (which could be hidden) and then to sum these
columns to give you the value you have now? In other words, you would
have this formula in column D (say):

=3DIF(ISNA(VLOOKUP($B7,'Herstmere
PCT'!$B$5:$N$100,D$5,FALSE),0,VLOOKUP($B7,'Herstmere
PCT'!$B$5:$N$100,D$5,FALSE))

and this in column E:

=3DIF(ISNA(VLOOKUP($B7,Welhat!$B$5:$N$100,D$5,FALSE),0,VLOOKUP($B7,Welhat!$=
B$5:$N$100,D$5,FALSE))

and so on for the other sheets. A simple =3DSUM(D7:K7) in L7 would give
you the consolidated total, with columns D to K hidden.

Hope this helps.

Pete

ciara_daniels@yahoo.com wrote:
> Hi all,
> I receive 8 reports monthly, 1 for each area, with the all the activity
>
> for that period. I would like to consolidate the data into one total
> sheet. I have a master sheet with all the specialties where I would
> like to have all the totals show. The activity will be different each
> month so the the number of rows may change. The number of columns is
> constant.
>
> Below is what I have at the moment... but it cant be re-used as the
> range is fixed. It also produces an N/A error if one produces an NA.
>
>
> $B7 refers to a specialty code.
> =3DVLOOKUP($B7,'Herstmere
> PCT'!$B$5:$N$19,D$5,FALSE)+VLOOKUP($B7,Welhat!$B$5:$N$20,D$5,FALSE)+VLOOK=
UP=AD($B7,'N
>
> Herts'!$B$5:$N$24,D$5,FALSE)+VLOOKUP($B7,'N
> Herts'!$B$5:$N$24,D$5,FALSE)+VLOOKUP($B7,'SE
> Herts'!$B$5:$N$20,D$5,FALSE)+VLOOKUP($B7,RBBS!$B$5:$N$6,D$5,FALSE)+VLOOKU=
P(=AD$B7,W3R!$B$5:$N$27,D$5,FALSE)+VLOOKUP($B7,Dacorum!$B$5:$N$22,D$5,FALSE=
)+VLO=ADOKUP($B7,Dacorum!$B$5:$N$22,D$5,FALSE)
>=20
>=20
>=20
> What is the best way to achieve this

0
pashurst (2576)
9/12/2006 2:52:01 PM
Reply:

Similar Artilces:

Dynamic INDEX lookup?
Basically I am trying to run one statement then use the informatio pulled from to complete a second statement. The first statement is: ActiveCell.FormulaR1C1 "=MATCH(""StricklandT"",qperiodagentperformance!C[-10],0)" This will result in 206 - which will give me the cells below D206 an A206 The second statement (in theory): ActiveCell.FormulaR1C1 "=INDEX(qperiodagentperformance!D206:D13000,MATCH("Agen Summary",qperiodagentperformance!A206:A13000,0)) See the worksheet has a listing of 20 or so Agents and under each agen is their Agent Summary. S...

SOS! how do I write a customized function to do multiple-value lookup in Excel 2007?
I have two regions in exact same places of sheet 1 and sheet 2, I want to find all cells in the region in sheet 1, with cell content equal to 0, and then add the numbers at the corresponding locations in the region in sheet 2 up. For example, let's say in the region in sheet 1, there are multiple locations where the cell content = 0: A1, B3, C4, D2, etc... I would like to obtain sum(sheet2!A1, sheet2!B3, sheet2!C4, sheet2! D2), etc. How do I do that? I want to write a user customized function for that: The regions in sheet1 and sheet2 are fixed to be D7 to Z20. So the first step is to fi...

do i need an if statment or a lookup or other
Hi this is my first excel post so I�ll try hard to explain what it I� after I have 4 sheets that are updated every day with data; I would like t create a summary once a week of specific data to let me know wha going. Example in column f7 if there ia letter and there a 7 possible for a t j then take tha letter and any data across from it and display on different page in a summary. I�m working with sort codes and names and projects numbers across th screen, with a calendar with up to 7 letter representing an actio (late, finished, early, bad sample �L�, �E�,�,,�F�) so if on Dec th 09/12 I u...

Data Consolidation across workbooks
I am using data consolidation across workbooks using row labels and linked to source data. If I change numbers in my original workbooks, the consolidated workbook reflects those changes; however, if I change a row label, the consolidated workbook does not update. Please does anyone know if there is a way round this? ...

lookup table
I have a workbook with two worksheets in it. the first is a list of items a description and a purchase month, the other is a table by month. I an looking to create a method where I can select from a dropdown a product and it will populate the table on the second sheet. Any suggestions? Hi Without getting too complicated, I would suggest using a pivot table, which is designed to summarise data. You can find it under the Data heading. An introduction can be found here: http://www.cpearson.com/excel/pivots.htm Andy. "Brian" <Brian@discussions.microsoft.com> wrote in mes...

DNS lookup in Excel
I am trying to look up a long list of URL's that are currently stored in Excel. What I need is the DNS entry for this file. I have tried several approaches using VBA but none appear to work. in short system.net.dns does not appear to be referenced using the ping file technique the "Open File" command does not appear to be working. Any thought would be welcome. Is the DNS text INSIDE the URL's text? Please provide exact sample. "NS" wrote: > I am trying to look up a long list of URL's that are currently stored in > Excel. What I need ...

Lookup and sort issue
Hi chaps, I’m struggling to get this working if anyone could help please – here’s a simplified version of what I’m trying to achieve: A B C D E F G H I J 1 Job Nº Date Name Address Mark1 Mark2 Mark3 Total 2 1 5/7/09 Fred London 3 2 8 3 2 5/7/09 Bill Norwich 2 6 4 4 3 8/7/09 Matt Bristol 4 1 1 5 4 8/7/09 Jack Cardiff 5 5 7 6 5 9/7/09 Eric Poole 1 5 6...

Query for Multiple Lookups
I'm using Access as the reporting tool for a third-party-developed proprietary database (I can't change the structure of the database). The database has a "master" lookup table that includes every type of lookup value for the entire database (not separate lookup tables for State code, employment status, practitioner category, etc.) My query joins the code from the first field to the code field in the master lookup table and properly displays the description. When I attempt to join a subsequent field to another instance of the master lookup table (using same ...

Criteria Lookup based on Dates #2
I know I know, this was already posted in a nother group, but no one over there could help me, so I'm hoping someone over here will be able to help me that didn't see it on the group. The "Current Plan" is the formula I'm trying to create: Dates are all US (mm/dd/yyyy). 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 ...

Lookup and Count
Dear Experts, Looking for some help I have a list box in cell B3 from where I pick a date that is in the range starting in cell A17, to A41. Each cell from A17 to A41 represent a month, A17 = 01-Sep-03, A18= 01-Oct-03, etc For each month 28 entries, a number from 0 to 10 has to be entered into the adjacent cells, one entry per cell. If 01-September-03 is in A17 the entries would span from B17 to AC17. The same would apply for 01October-03 in row 18. If I select a month from the list in B3 I need to count how many entries in that month were between 1-5 6 7 8 9 10 and display each t...

LOOKUP across a whole table and not just first row or column
I am attempting to look up a particular value in a table and then identify the number in the first column of that table which corresponds to that row. For example: 111 678 666 475 987 232 565 687 4888 242 4543 123 3334 2727 590 From the above table, be able to enter a value in a formula within a cell which in turn, would return the value in the first column of the row which contains that value. For example, if I enter the value 687 in the cell against which the formula is applied, I wish the formula to provide the value "232" as the response. ...

Lookup?? Issue
OK, here's what i want to do. I have a spreadsheet (condensed below) Store Location ID Acc Amount A 67583 $2895 B 78594 $5000 C 26974 $3968 I want to be able to have a cell that has the max value (in this case 5000)formula and gives me, in another cell, the store name. i.e. cell a6 has =max(c1:c3) cell a5 automatically enters Store B (or B in this case) because 5000 is the max amount. I have tried the lookup wizard and can type in store A and get 2895, I want it to utilize the info in cell a6 in this example and give me the sto...

LookUp Value
How can I access the lookup guid value using client side scripting in 3.0? Check out the information at following url: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/CrmSdk3_0/htm/v3d0fieldtypelookuporpartylist.asp "CRM Chief" wrote: > How can I access the lookup guid value using client side scripting in 3.0? Sorry, where I must wrote example code from link below? "Jon Susiak" wrote: > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/CrmSdk3_0/htm/v3d0fieldtypelookuporpartylist.asp ...

Datasheet and lookups...
Here's what I want to do. I have two tables Materials and Manufacturers. The relationship is that a Material is made by a Manufacturer (Material Many, Manufacturer One). So the client want to Datasheet view to sort and edit the Materials and verify that the information is correct. They have come up with a scenario where they want to change the Manufacturer. However I am no sure how to do this. Can I make the Manufacturer field a Lookup within the datasheet? Any thoughts/pointers would be appreciated. Looks like you have a many-to-many relationship instead of a one-to-many. (more...

reverse lookup for my exchange server..............
I am using Exchange 2003 (name riley) on win 2003 AD domain controller. When I setup my AD, I used abc.com as my domain name. I figured it not a problem since I am only using it locally. I can send and receive e-mails with no problem. Now, since many are using reverse lookup to filter out spam e-mail, I am in trouble. When you do an nslookup for my exchange server from your server, it comes up as riley.abc.com with an IP address that belongs to abc.com (which is another website that does not belong to us). E-mails are being rejected by those who use reverse lookup because it does not match. ...

Match and lookup data #2
Hello I would like to look up data in another sheet of the same workbook onl when it matches data selected in a dropdown(s). Attached is a spreadsheet which better explains my predicament I tried the various lookup and match functions and even tried combinin them but my not so superior knowledge of Excel limits me. Please help guys Cheers Yusu Attachment filename: timesheet test 1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=56973 -- Message posted from http://www.ExcelForum.com ...

Hopw can I Reverse-DNS lookup an IP Address?
Hi all, I just got the (frustrating) task of taking the unresolved IP Addresses of visitors to our web site, and parsing them (resolving them) to find out how often our competitors are looking at it. I am fully aware of the myriad ways that they can get around being detected as our competitors. Is there any way I can, in Excel, take an IP Address formatted as xxx.xx.x.xx (variable length segments) and output a resolved hostname for it? Also, if I can only do it by visiting someone's web page, can someone point me at one that does allows mass lookups all at once? Thanks in advance, Aaron C...

Complicated use of INDEX and MATCH to lookup multiple columns
Hi, I am trying to do a version of the formula given for method 1 here: http://support.microsoft.com/kb/214142 Using Excel 2007, I have to retrieve a value in column D of sheet Media by matching the value in sheet Media column A to sheet Promo column A and also checking that the dates in sheet Media columns B and C overlap with the dates in sheet Promo columns D and E. I get an #N/A error for a value/values that should be found when using the following formula: {=INDEX(Media!$D$2:$D$4473,MATCH(Promo!A2,IF(OR(AND(Media!$C$2:$C$4473>=Promo!D2,Media!$C$2:$C$4473<=Promo!E2),AND(Media!...

IE8 Consolidate instances of explorer?
Ok...somehow I've got 7 instances of explorer running; each with multiple tabs. I'd like to streamline, I'll close a bunch of tabs...that part's easy; but, I'd like to keep some tabs open and consolidate them all into one instance of Explorer. can this be done? Thanks! Frank Hi Frank, Favorites>Add current tabs to Favorites Save each windows tabs set to the same Favorites folder. Unfortunately you cannot drag and drop tab leafs from IE window to IE Window. "Frank" <Frank@discussions.microsoft.com> wrote in message news:C6D1DCF8...

Any first-hand knowlege of Consolidated Invoicing?
I am looking at Trinity Partner's Consolidated Invoicing add-on. Would appreciate any feedback from users actually using this product. Does it work well for you? Easy to use? Any other products that work better? Thanks, David Trinity makes great products but I've never worked with this particular one. I have a client that uses Sales Document Merger Manager from Binary Stream and has been using it for a couple of years. They have a bunch of sales orders that are transferred to invoices. They merge multiple invoices into a single consolidated invoice. We created a Crystal Report...

Lookup with Unsorted Data ?
I am looking for a way to do a lookup on a range of data that cannot be sorted for other reasons, or I prefer not to sort. I can find this reference from M$ http://support.microsoft.com/?scid=http://support.microsoft.com%2Fservicedesks%2Fbin%2Fkbsearch.asp%3Farticle%3D181212 but I am wondering if anyone has anything "cleaner"...maybe a nice VBA function for example. Thanks Actually you can use VLOOKUP with unsorted date by including the "False" "range_lookup" in the statement. =VLOOKUP(xx,xx:xx,x,FALSE) See the help on the function for a full explanation. ...

lookups and ranges
I have a page of vlookups that look at data on other worksheets. At the moment, if I want to change the worksheet that contains the necessary range, I have to replace the name of the worksheet. eg =IF(ISERROR(HLOOKUP($B$1,'Business Multi'!$D$1:$EP$3000,HZ16,FALSE)),"",(HLOOKUP($B$1,'Business Multi'!$D$1:$EP$3000,HZ16,FALSE))) So if I want to look at the worksheet Business NonMulti, I use the find/replace wizard. Ideally, I'd like to set up named ranges within each worksheet and use a drop-down list (validation) to choose which range the formula looks at, bu...

Synchronized Multi Value Lookup Boxes
Total newbie to Access here, but I have done considerable work in Excel, VBA, etc. Anyway, I'm trying to make what should be a very straightforward database. Basically, each entry will contain an attachment (PDF, word, ppt, etc.), filename, file description, and 3 layers of categorization for each attachment. The categorization process will look something like this: Layer 1 (category): need ability to choose multiple categories Layer 2 (sub-category): each category in layer 1 has x number of sub-categories. I would like to populate the list of options for layer 2 ...

sum lookup
The control source of MINUTES is =[PRICINGID].Column(4) In form footer, I have =sum([minutes]). But it is not working. It is blank. Can someone please help! Thanks Hi Ashley, if you want to sum a control that is based on an equation, you need to sum the equation ... but you also cannot sum a column of a combobox. You can only sum fields from the RecordSource. Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.com/user/LearnAccessByCrystal Access Basics http://www.AccessMVP.com/strive4peace free ...

Employee Wage Lookup
I have a column that has a drop down list of employee names (50ea). I would like the cell in the next column to automaticaly insert the wage rate for the emplyee name selected. How do I it ? by using VLOOKUP see http://www.mvps.org/dmcritchie/excel/vlookup.htm You can probably put that next to the employee name in the table you use to create the dropdown, but you will still have to use VLOOKUP.. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie...