Centralized data

I have a main costing work sheet that needs to be populated by individual 
costing sheets that went out to all of the buyers to populate. 

Main Costing Sheet
A. Item #       B.Desc    C. QTY      D. Cost  E.....F.........G......H (all 
contain irrelevant information to costing activity. 

There are 5 buyers (Nick, Tanya, Doug, Skip and Forrest) that I have divided 
up the main costing sheet and sent them thier individual items to cost, now 
that I am getting the individual costs back, I need to populate my "Main 
Costing Sheet" with each cost, however, the items are all together and not 
separated on this sheet. I created the following Vlookup and keep getting 
error messages. Can someone please help me fix this formula or figure out an 
easier way to take the costs off of each individual sheet and populate my 
Main Costing Sheet?
0
Utf
5/13/2010 7:32:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
590 Views

Similar Articles

[PageSpeed] 16

And the formula is???


Gord Dibben  MS Excel MVP

On Thu, 13 May 2010 12:32:01 -0700, Candida
<Candida@discussions.microsoft.com> wrote:

>I have a main costing work sheet that needs to be populated by individual 
>costing sheets that went out to all of the buyers to populate. 
>
>Main Costing Sheet
>A. Item #       B.Desc    C. QTY      D. Cost  E.....F.........G......H (all 
>contain irrelevant information to costing activity. 
>
>There are 5 buyers (Nick, Tanya, Doug, Skip and Forrest) that I have divided 
>up the main costing sheet and sent them thier individual items to cost, now 
>that I am getting the individual costs back, I need to populate my "Main 
>Costing Sheet" with each cost, however, the items are all together and not 
>separated on this sheet. I created the following Vlookup and keep getting 
>error messages. Can someone please help me fix this formula or figure out an 
>easier way to take the costs off of each individual sheet and populate my 
>Main Costing Sheet?

0
Gord
5/13/2010 11:05:56 PM
The formula is as follows: 

=IF(ISNA(VLOOKUP(B2,'Tonya''s 
response'!A$2:P$264,12,FALSE)),VLOOKUP(B2,’Nick 
Costing’’!A$2:P$160,12,FALSE),IF(ISNA(VLOOKUP(B2,’Nick 
Costing’!A$2:P$160,12,FALSE)), VLOOKUP(B2,’Skip 
Costing’!A$2:P$160,12,false),IF(ISNA(VLOOKUP(B2,’Skip 
Costing’!A$2:P$160,12,FALSE)),VLOOKUP(B2,’Doug Costing’, 
A$2:P$299,12,FALSE),IF(ISNA(VLOOKUP(B2,’Doug 
Costing’!A$2:P$160,12,FALSE)),VLOOKUP(A2,’FORREST 
Costing’!A$2:P$160,12,FALSE)),IF(ISNA(VLOOKUP(B2, ‘FORREST 
Costing’!A$2:P$160,12,FALSE)),VLOOKUP(B2,'Tonya''s 
response'!A$2:P$264,12,FALSE)))

"Gord Dibben" wrote:

> And the formula is???
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Thu, 13 May 2010 12:32:01 -0700, Candida
> <Candida@discussions.microsoft.com> wrote:
> 
> >I have a main costing work sheet that needs to be populated by individual 
> >costing sheets that went out to all of the buyers to populate. 
> >
> >Main Costing Sheet
> >A. Item #       B.Desc    C. QTY      D. Cost  E.....F.........G......H (all 
> >contain irrelevant information to costing activity. 
> >
> >There are 5 buyers (Nick, Tanya, Doug, Skip and Forrest) that I have divided 
> >up the main costing sheet and sent them thier individual items to cost, now 
> >that I am getting the individual costs back, I need to populate my "Main 
> >Costing Sheet" with each cost, however, the items are all together and not 
> >separated on this sheet. I created the following Vlookup and keep getting 
> >error messages. Can someone please help me fix this formula or figure out an 
> >easier way to take the costs off of each individual sheet and populate my 
> >Main Costing Sheet?
> 
> .
> 
0
Utf
5/17/2010 8:40:01 PM
=IF(ISNA(VLOOKUP(B2,'Tonya''s response'!A$2:P$264,12,FALSE)),VLOOKUP(B2,’Nick 
Costing’’!A$2:P$160,12,FALSE),IF(ISNA(VLOOKUP(B2,’Nick 
Costing’!A$2:P$160,12,FALSE)), VLOOKUP(B2,’Skip 
Costing’!A$2:P$160,12,false),IF(ISNA(VLOOKUP(B2,’Skip 
Costing’!A$2:P$160,12,FALSE)),VLOOKUP(B2,’Doug Costing’, 
A$2:P$299,12,FALSE),IF(ISNA(VLOOKUP(B2,’Doug 
Costing’!A$2:P$160,12,FALSE)),VLOOKUP(A2,’FORREST 
Costing’!A$2:P$160,12,FALSE)),IF(ISNA(VLOOKUP(B2, ‘FORREST 
Costing’!A$2:P$160,12,FALSE)),VLOOKUP(B2,'Tonya''s 
response'!A$2:P$264,12,FALSE)))

"Gord Dibben" wrote:

> And the formula is???
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Thu, 13 May 2010 12:32:01 -0700, Candida
> <Candida@discussions.microsoft.com> wrote:
> 
> >I have a main costing work sheet that needs to be populated by individual 
> >costing sheets that went out to all of the buyers to populate. 
> >
> >Main Costing Sheet
> >A. Item #       B.Desc    C. QTY      D. Cost  E.....F.........G......H (all 
> >contain irrelevant information to costing activity. 
> >
> >There are 5 buyers (Nick, Tanya, Doug, Skip and Forrest) that I have divided 
> >up the main costing sheet and sent them thier individual items to cost, now 
> >that I am getting the individual costs back, I need to populate my "Main 
> >Costing Sheet" with each cost, however, the items are all together and not 
> >separated on this sheet. I created the following Vlookup and keep getting 
> >error messages. Can someone please help me fix this formula or figure out an 
> >easier way to take the costs off of each individual sheet and populate my 
> >Main Costing Sheet?
> 
> .
> 
0
Utf
5/17/2010 9:30:01 PM
Reply:

Similar Artilces:

How do I create a mailing list with current data in Excel?
I have a list of names, addresses and I would like to make it a list that can be used in a mail merge. Is this possible and how? http://www.mvps.org/dmcritchie/excel/mailmerg.htm -- Regards, Peo Sjoblom (No private emails please) "Amanda" <Amanda@discussions.microsoft.com> wrote in message news:095EF751-B349-4C6C-BA5B-9D91ADF7DA9E@microsoft.com... >I have a list of names, addresses and I would like to make it a list that >can > be used in a mail merge. Is this possible and how? ...

Utilizing a portion of data in SUMPRODUCT
Based on prior input from the group, I am using the following formula: =SUMPRODUCT(--(Log!B3:B80=1997),--(Log!E3:E80=17)) The data in Column B is actually a full date, entered yyyy/mm/dd. With the data in this format, I get a formula result of 0. If I eliminate the /mm/dd, I get the correct result of 1. I have tried the formula with and without quotes around the Condition. The person for whom I am creating this spreadsheet has asked that the date be kept together, rather than separating out the year. Is there some way I can maintain the data in Column B and change the formula to ge...

Collecting data
I have an evaluation sheet created in Excel 2003. I have a number of questions which have responses eg. poor, fair, good, excellent etc. These will be answered by ticking in an ‘ActiveX Check Box’. Beside each question the respondent can add comments, for this I used the ‘ActiveX Text Box’ with a scroll bar. We usually only send this form out to random attendees and getting about 5-10 responses. It was not difficult to collect the data for such a small number. What has happened now is that a colleague has sent the form out to everybody that attended one of our presentations and ...

Data Organized
Dear All, I have list of names (column A) and every time I put a code (example: 2), instantly the function will create a list in another column. For example Data: A B NAME CODE AA BB 2 CC DD 2 EE Result: BB DD And if I put another code (3 for example), the function will create another list at next column. Please help. Best Regards, Gusur Try this array formula =IF(ISERROR(SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1))),"", INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1)))) copy down and across as required -- __________________________...

Transfer data from sheet to sheet
I'm currently working on a quotation and I have 3 seperate worksheets I'm working with. Sheet 1 has all the model name, elevation, part description, quantity, cost per piece and total. This worksheet is for internal use only. Sheet 2 has all the products and prices listed. (Probably has no purpose for my question) Now Sheet 3 is where I am trying to pull only certain data from Sheet 1. What I am trying to do is transfer only the columns labeled "model name", "elevation" and "total" Obviously the easy way to do it is to just cut and paste but since...

hiding control textbox when no data
Hi, I am wondering if there is a way to hide the whole textbox control and does not leave big space when it has no data/value in the Access report. Any help would be greatly appreciated. Thank you in advance Define "big space". Do you mean horizontally or vertically? Have you set the text box Can Shrink property to Yes? -- Duane Hookom MS Access MVP "Associates" <Associates@discussions.microsoft.com> wrote in message news:4964B131-5BBE-4249-8F0C-2C00ED0AA012@microsoft.com... > Hi, > > I am wondering if there is a way to hide the ...

Linking rows of data to another worksheet
Worksheet One contains survey data pertaining to customer satisfaction for all of our building communities. The data is entered in each row as follows.. .. community name, lot number, buyer name, etc. Therefore, this worksheet contains all the survey results for all of our buyers, and then based on the survey responses, an overall rating is calculated. I would like to then link each row to its corresponding worksheet per community. By doing this, I can calculate the survey ratings per community as opposed to the overall rating calculated on worksheet One. I would greatly appreciate any as...

Using formula to calculate data source range
Is there any way to referencing to data source range with formula? For example I would like to insert this formula to the data surce page (if it would be possible) : =Address(32;12;1;True;"DEF_Montly")&":"&address(32;DEF_Monthly!$A$1;1;True) its value is: "DEF_Monthly!$L$32:$Q$32" So this way I could expand or reduce the range dinamically. Can anyone help me? Edit. You can't link the whole data range of a chart to a dynamic range like this; Excel accepts the name but converts it into a non-dynamic cell address. You can link the X and Y values of...

Sorting more than 3 pieces of data??
Wow, I've _never_ had this happen before. I have a spreadsheet that has 4 columns, 3 that are often extremely similar. It's the 4th one that has the most variation in the data, and it's not getting sorted. I must have then sort by the four, unfortunately. As only 3 fields come up in the data sort option, was wondering if anything can be done? Thanks. First sort all data only by that column that you'd indicate as the last (4th) field in the sort by tool. Then sort all the data again by the first 3 columns. Hi Sub-sorts stick. So 1) sort by criteria 3, 4 2) sort by c...

Taking all BU data offline
Hi I am trying to find a way that a user can take all account - contact data offline in the outlook client relative to the Business Unit that they belong to. I have looked through all the options in the Local Data filters but am so far unable to find a way to do this as the Business unit field is not available in the options. Has anyone managed to do this ? Cheers Brian Here you go: Under the new Data Group: Account >> Select "Related Owner" >> Business Unit Do the same for contact and other objects you want to bring down Frank Lee Workopia, Inc. http://www.w...

Excel 2007: Conditional Formatting and Ribbon Data
I have several cells have conditional formatting equations which refer to a date value in cell H1 (for example). I want to be able to move the value stored on this cell to a control on a Ribbon. 1) Is it possible to put a Date Picker control on a Ribbon? 2) Is it possible to refer to the value of this or any other control on a Ribbon from a Conditional Formatting equation? Thanks! JJ ...

back up data to outlook
Hello I have a friend that did not back up his outlook data from outlook in the export fasion. He made a back up of the documents and setting folder and dragged it to a cd. Is there any way to restore the outlook data to a new install of outlook???? Thank you again in advance. Yes, search for pst-files and make sure you include hidden files when searching. In fact copying the pst-file is a better backup than exporting the pst-file. Also see; http://www.howto-outlook.com/howto/backupandrestore.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Na...

Importing private calendar data to a public calendar
Hi, I have exported a private calendar to a PST file and I'm trying to import it into a public calendar I have created. I get a message saying "You have chosen to import a folder whose type differs from that of the selected folder. The items will be imported into a new folder with the same name." However it doesn't import it to anything I can find. I am able to import the data into another private calendar though so I no the data is good. Any suggestions on how to transfer private calendar data to a public folder calendar ? Thanks, Tony Tony Tuccillo wrote: > H...

office 07: auto powerpoint charts using named range data in excel
i am trying to create code that goes down a list of named excel ranges and creates a complete ppt chart (title with graph and annotated text), one for each excel range. the chart will be customized based on the contents of the named excel range. there may be easier ways, but i thought that if i could reference and change the ppt chart data directly from the excel file procedure, i can set it equal to the excel file data range name that i want. i have been trying to use sourcedata and chartdata but i keep getting compiler errors. any help is welcome, even if it is a reference book that...

Problem with Filtering Form with Subform in Acces Data Project 200
When I aplly filter (by form) to a form with subform in Acces Data Project 2007, subform loose data; subform does'n show any data; Moveing to next/prev. record in main form doesn't synchronise subform wich is still and always blank; I must close and reopen form to get again subform data... Main form contains about 50000 records and subform contains 30-60 records related to main form selected within 1,500,000 records in table. Moveing to next/prev. record work correctly when filter is not applied. Acces Data Project 2007 use SQL Server 2005 Express SP2 installed on Windows Vista...

Data Migration Framework #2
Hello, somebody can migrate email with DMF ???... somebody can put a sample off record in DMF table's fopr email migration... My problem is that the email was migrated but the Sender and To is blank on MS CRM... so the field Regarding Off is empty en MS CRM... thanks a lot ... Marcos Paione ...

Copying data without blank cells, and only certain cells
I have 3 questions: 1). I receive large data sets from scientists in excel format that' been transferred from some instrument. In column A are numbers usuall starting from 200 to 780 counting up every one unit. In the column is the data corresponding to the numbers in column A. I'm trying t figure out a formula that will copy the number 200 and every 5 unit thereafter (200, 205, 210...etc) in one column plus the data tha corresponds to that number in other column. 2). Also, in some data sets I receive, there are an even amount o blank cells between each filled cell. So A1 w...

Data transfer
I am receiving data by a plc access program into a cell.How do I move it down a list as fresh data arrives. ...

Retrieving data with two variables
Hi, First time. Looks like you all have some great advice. This will probably be very easy for most of you. I would like to create one worksheet were I choose a certain doctor (Drop down box), the year and it matches the month, year and doctor and guves me the correct production $$ for that doctor. I have used the lookup formulas but can't seem to figure how I should write this formula. Thank you for any help. -- Many Thanks, Jennifer If you only give it the year, how will it match on the month? If you give it a date, too, then you could use something like this: I put my table o...

How can I convert data from excel to address labels?
I've been asked to convert a list of addresses that are in an excel spreadsheet into address labels. Would appreciate any help! Thanks. See Mail Merge in Microsoft Word. It has wonderful instructions "KC" <KC@discussions.microsoft.com> wrote in message news:4ECF5BD3-97C3-4978-838A-C6DA2D55FB4A@microsoft.com... : I've been asked to convert a list of addresses that are in an excel : spreadsheet into address labels. Would appreciate any help! Thanks. You may want to read some tips for mailmerge: http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org...

can i short data with excel formula
if column A1 has any value and column A3 has any value and column A7 has any value , so can i short these value with excel formula..... You'll need to provide a clearer explanation of what you are looking for. A1, A3 and A7 are cells, not columns. What does "short these" mean? Do you mean "show these"? If so, is this what you are looking for: =if(and(a1<>"",a3<>"",a7<>""),true,false) Regards, Fred Best idea is to provide an example of what you want done. Regards, Fred "sunder jangra" <s...

How do I copy a graph and use the data in the new worksheet?
I have new data in different worksheets all with the same data format. I want to make the same graph in each worksheet using the data in that worksheet but I was hoping not to have to recreate the graph and select all the data in each worksheet. Is there a way to do this as long as the data is in the same place in each worksheet? Copy the sheet with the chart. The chart points to the new sheet's data. Copy a new set of data, and paste it over the data that the chart is pointing to, and the chart will update. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Service...

Data Validation on Numbers
How do I restraint the user from entering odd numbers in the cell, that means only allow even numbers but these even numbers must be in multiples of 5? Example : 10,20,30,40................ Thank you. Regards, Ringo Tan Ringo Tan One way: Choose "Custom" (or similar) in the validation box and enter this formula =(A1>0)*(MOD(A1,10)=0) if negative numbers are also allowed, use =(A1<>0)*(MOD(A1,10)=0) if both negative numbers and zero are allowed: =MOD(A1,10)=0 -- Best Regards Leo Heuser Followup to newsgroup only please. "ringo tan" <ringotan@di...

Excel, DB data and RTF
Good morning to you all I have a sql server table in which there's an attribute containing an rtf string; something like {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fnil\fcharset0 Verdana;}} \viewkind4\uc1\pard\ul\b\i\f0\fs32 pippo\ulnone\b0\i0\f1\fs18 \par } Well, using a query to an external source I get the string, but... exactly as you read it here above. I do need to transform it in something readeable. I'd love a function like: "=GetRTF(A1)" ;) but I'm not able to find it and at the same time I cannot f...

Data Validation Input message position
A workbook I've created has quite a few Data Validations. By accident I found out that when the input message is showing, you can use the mouse to shift it to wherever you want on the sheet. Once that is done, all other data validation messages will appear in that same spot (which is not always a bad thing) BUT..... The messages will no longer be shown in their default position (next to or near the active cell that causes the validation input to appear). Is there a way to restore that default setting? Rob AFAIK, once the input message has been moved, you can't reset it to its ori...