I am attempting to match data entered into two cells. The data input goes into Worksheet 1. In Column A Row 1 the user inputs data... Example: 5. Then in Column B Row 1 the user inputs separate data... Example: B. I would like the result returned to Column C Row 1. The lookup would be on a table in Worksheet 2.
Worksheet 2
A B C D E F G H I J K L M N O P
1 2 3 4 5 6 7 8
A Pig A Cat A Dog A Fish A Ox A Bat A Six A Pie
B Pie B Six B Bat B Ox B Fish B Dog B Cat B Pig
C Fish C Ox C Cat C Six C Dog C Pie C Pig C Bat
Therefore the Entry 5, then the entry B would Return: Fish
So it's a lookup and match Column A-P. Then using that match lookup Row 2-4 for the second match. Then Return the data from the cell next to it.
I am trying to summarise training data in a spreadsheet as per the following layout:
A B C D E F 1 Trainer name # of trainees # of assessments Total hours Avg hrs per trainee Pass rate 2 Peter
3 Paul
4 Mary
The intention is to have this summary fixed on one tab, with the raw data being dumped into a second tab alongside the summary (named “the data”).
The data for the summary comes directly out of an Access database and is in the following format (I’ve put an X against the headings that aren’t relevant to the summary):
A B C D E F G H I J K L M N 1 X X PersID Name X X X X X Trainer Assessment X Result Hours 2
12345 Jimbo
Peter Excel Competent 2 3
54321 Nelson
Paul Access Not yet Competent 3 Notes:
-The data can be up to 2000 rows long
-There will be multiple entries for each trainer of course, and multiple entries for some trainees (i.e. they will complete several training types)
-Result can only be Competent or Not Yet Competent
So, of the 5 fields I need to populate for each trainer, I’ve currently only got the following nailed:
# of assessments: =COUNTIF('The data'!$A$2:$A$114,A2) Avh hrs per trainee = D2/B2
These were the easy ones.
What I’m having trouble with is the following:
For the #of trainees field, the calculation must determine how many people were trained by each particular trainer – so only unique entries should be counted (PersID could be the unique identifier). Total hours will also need to be linked to the individual trainer – in other words, how many hours in total has the trainer spent training?
Pass rate is another tricky one – for each trainer, I’d like to see the percentage of trainees deemed competent out of the total number of trainees they trained.
I am having a difficult time with a look up. It would be very hard to explain so I'll attach a copy of the section of the worksheet that the problem lies on with comments so you can see whats going on. The problem there is a numbered list with a reference number i can't seem to figure out a lookup that will look in the chart above and find the row associated to the reference number and according to how many before it have that reference number find a secondary reference number listed in the column above. The attachment should clear it up.
I have created an index of documents in Excel 2003. I have one "master" worksheet containing a list of documents and their corresponding version numbers. On another worksheet, I record details of changes to the documents and hence changes to version numbers. This list will constantly grow over time, and in any order depending on the documents to be changed. However I want certain fields to automatically update themselves, namely the version number.
So for example, I have a column to evaluate if document changes are required, yes or no.
If "Yes" is selected, I want the version number in another cell to automatically update to the next in sequence (add one). So if changes are required, V1 becomes V2 etc. Now the next version number will depend on the previous version number, so before it decides if it adds one, or not, it needs to lookup the value of the current version number in a range. Again, as its a list of different documents this can only be done by first looking up the unique document number.
So to summarise, I need it to lookup the unique doc number in a range and find the one that has the highest version number against it, and return its version number. And then encompass all of this with the If statement to evaluate whether or not the version number needs to add one.
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age ------------------- Dog - Rover - 3 Goldfish - Tom - 1 Gerbil - Chewit - 4
I'm currently trying to sum across a row only if certain text is present. So, if A1 has CT8 and A2 has 5 and A3 has CT5, I want to just sum the cells with CT in them. I've already worked out how to strip the CT from the numbers in the sum, but not how to specifically only sum if CT is present. And of course, the sheet uses other letter/number combinations that need to be separately summed.
I have been attempting to copy a date & time with a specific format from one sheet to another and add " before and after it in the same field. This is being done to create a specific csv format for date.
The Formula I am using on sheet3 is - =IF(Sheet1!A3="","",Sheet2!I3&Sheet1!G3&Sheet2!I3)
The Values I am using are below Sheet1!G3 Format = yyyy-mm-dd hh:mm:ss Sheet1!G3 = 2014-02-11 07:00:00 Sheet2!I3 = " Formula Results = "40219.2916666667" Intended Formula Results = "2014-02-11 07:00:00"
I have used a similar formula for other fields within the xlsx, and the only one I am having issues is with the date field. It keeps converting to a serial number when the calculation occurs and the copy and formatting occurs. Formula to get it to come out with the intended results?
Had a bit of code I was working on for a guy on here, and came across a problem while putting the finishing touches on it. It's since been solved by someone else, but for future reference...
What I'm trying to create is a way to populate a quick inventory list with my bar-code scanner.
I've already learned that in order to have excel move to the next row each time a 12-digit SKU number is scanned, I'm going to have to utilize a userform text box.
My problem:
I don't even know how to begin coding the text box to accept a 12digit number, enter it into the next available blank cell (I'd like it to populate vertically) and then ready itself to accept the next available 12digit number.
My desired end-result:
My scanner is attached to a lengthy USB-Extension, so I can roam my store and quickly scan the inventory on display. For that reason, running back to my laptop and striking "Enter" each time makes the whole solution pretty useless.
Once finished, I'm assuming I could use excels count feature to tell me the quantity of the items I have on hand.
I have recorded 7 different macros and then combined them all into one macro to achieve one end result. I am not sure if you can just look at the codes to determine different ways to improve them or if you need the excel spreadsheet as well.
I"m attempting to generate a report to show the number of occurrences for a particular product within a calendar month.
Data is stored on the first sheet, output is populated on a separate sheet. Seems simple enough, but getting the "month" out of the date, whilst referring to another sheet seems to be the sticking point.
Basically it is another sports modelling experiment that I have had (I get the day off work and come up with things like this ), where I just had a curiosity to play out the last games for the top 3 teams in the English Premier League (just to see if I could get some percentages).
The attached, is just the values from one run of my model (I have taken the workings out to reduce the size and for other reasons).
Basically everything to the left of column K, is the model and I am happy with it (and I know goals have to be integers - this is basic);
This plays the games out, and feeds the data into the table at M36 (highlighted yellow), which feeds the data tables at M42 (highlighted green), which are counted to get percentages in the tables (highlighted yellow at M6).
I have put some example output from runs on the second worksheet (so you can see how it works - I like that it suggests Chelsea can win even though it won't be very likely )
Basically you can probably see the problem in cell O22. The percentage chance of finishing first should equal 100% when totalled (e.g. the sum of Liverpool, Chelsea and Man City finishing first, as it has to be one of them should equal 100%). I tracked this back to the fact that I had no way of splitting teams level on points. As a result I added Goal Difference to the model (crudely - e.g. decimal) and here's the question;
How would I go about using Goal Difference to break tie's effectively?
I am thinking I could do some kind of IF function in Q37 (and then have a tie-breaked rank in column R to feed the data tables), on the basis of if anything in N37 to N39 was equal but I am not sure how I would go about applying this.
copy or cut then paste - paste grayed out in excel 07 tried several popular fixes from the web without luck.
Tried: the Excel repair and diagnose tool
Tried: uninstall / reinstall Excel program as well
Tried: go into Hikey - user - software - Microsoft - excel - new - 32 binary and tried adding new rule
Tried: Close Excel.2. Go to C:Documents and Settings[userID]Application DataMicrosoftExcel3. Delete the XLB file.4. Open Excel (the XLB file will be recreated - like the normal.dot) ------> This one - I could not find the XLB file - looked everywhere.
Tried: Open excel hit alt-f11 (to get to the VBE) hit ctrl-g (to see the immediate window) type this and hit enter: application .command bars ("cell").reset Then back to excel to test it."
I have a two sets of data, both which I have calculated variances plan Vs Actual, I would like to compare the previous months variances of different sheets. I have put a sort on the variances in descending order.
I want to match two fields E.g. B1 & D1 in sheet1 AND if they match then bring back L1 data in sheet2? The both sheets have same column names and in same order.
I am looking for a fourmla to return a value in a column next to the closest value. Yes, this would normally be a vlookup example however in my application the numbers will not match exact as such in an client lookup application. I need to find the closest value to the value compared in the range and return the value to the right of it.
I have made up a small table attached with comparing what vlookup gives me and what I am looking for.
I am setting up a spreadsheet, that will have multiple sheets for each branch, this will than be referencing back to another sheet for data. As i will be replicating the sheets I want to make the formulas as easy as possible so I dont have to change them in every sheet. I have attached an example, the data sheet will have a table for each month, the remaining sheets will be each branch, ideally I would like to have a formula that looks up the data sheet and returns a value for each month and then be able to replicate the formula across each sheet. As the formula is looking at 3 different areas (date, branch and mortgage balance) i have tried using idex and match, but are not having any success.
I am trying to create a lookup table that will lookup parts of a colum and return the second column in the table. For example, column A has a lot of different titles in it, they each contain different segments and codes in them but I need to see if NM is in the list and if it is I want to return a 1.
Column A Lookup COLUMN Lookup Result NM01A NM 01 NM02B IN 02 IN01A IN02B
I need to see if what is in the lookup column is in column A, and if it is then I need to return the lookup result.
2 worksheets. The first has the raw data and I want to drag some stats onto the second.
I'm trying to make it look in one column on first sheet and identify matches for a range of numbers (say 100 to 199), then where there's a match, look across into another column and count instances of specific inputs (A). ie, 114 has A in column G as does 119 and 199, 115 has B = count will be 3 -
My attempts at stringing combinations of match, IF, Countif and lookup/vlookups have, frankly, been a tad pathetic!
I have exported an income statement report from the ERP system to Excel. Only items that are not equal to zero are populated. So if there has been no expense in the Central division for Consulting fees, it does not show up on the report. Here is the formula:use control shift and enter
A 2 East Division 3 Sales 4 Cost of Sales 5 Payroll 6 Lights 7 Consulting Fees 8 Net Profit 9 10 Central Division 11 Sales 12 Cost of Sales 13 Payroll 14 Lights 15 Net Profit 16 17 West Division 18 Sales 19 Cost of Sales 20 Payroll 21 Lights 22 Consulting Fees 23 Net Profit
Excel tables to the web >> Excel Jeanie HTML 4 So this formula picks up the first Consulting Fees after the Central Division. The problem is it is going to pick it up under the West Division. So, how would I modify the formula so that if the row number is also less than Central Division Net Profit (A15), it is zero. (no offset or indirect)
i'm trying to figure out how i could get the sumproducts or anyway to get this forumla work basically i'm trying to have the cost column to vary depending on the variable set on column d & e on the top basically ...
* if d7 is between d2 and e2 use cost 1 for the multiplication to generate H1. * if d7 is between d3 and e3 use cost 2 for the multiplication to generate H2.
I am looking for a fourmla to return a value in a column next to the closest value. Yes, this would normally be a vlookup example however in my application the numbers will not match exact as such in an client lookup application. I need to find the closest value to the value compared in the range and return the value to the right of it. I have made up a small table attached with comparing what vlookup gives me and what I am looking for.
how does the formula change when i want a new column to "bring in" ( lookup?) a corresponding value from the second sheet when it sees that the names match?
For instance: sheet1 FIRST LAST HEIGHT1 John Smith 5'9
sheet2 FIRST LAST HEIGHT2 John Smith 5'11
So, the conditional format would be a 4th column in the first sheet-- still comparing the first & last names-- but when they match it enters "HEIGHT2" into this 4th column.
This is still a conditional format, right? sorry if it doesn't belong here
I am trying to create a project on excel in which there exists four worksheets. I deleted "class 2-4" due to file size but are very similar. My question here is i need a macro to located the number from the "current" column in the Manual Input section in the "Main Page" worksheet. The current number must match column A into the appropriate sheet (depending on class) and then look for the volume in row 5 and then round up to the nearest number. My goal is to input the "proposed" number into the appropriate column and row. This will be done for four different classes. My goal is to develop this table so that i can look for trends.
Eventaully i would like to concatenate multiple proposed values for each cell as the macro will need to add onto what is located in the cell should the value already exist. Code must be a module so it runs when ever data is entered in the current, proposed, and volume cells in the manual input.
i did find a thread concerning my question but the topic was closed & the OPs best answer had been edited & removed for some reason...
i'm comparing annual pro sports stats for a project & want to do a vlookup contingent on pairs of columns matching between two sheets. ie if a first AND last name in one sheet match a first and last name in a second sheet then fill in x statistic. i dont know VB or anything, but i assume this can be done w/ a pretty simple formula
