I have a sheet containing a list of client name,the date they've been assigned, the type of account they have, the status of the account, and the last update date.
What I'm trying to accomplish is to extract the client name and the type of account on that table that shows as "complete" in status. However, it gets complicated since I need to separate the data that had been 'completed' under the same month, with those completed the other month.
For example, I have been assigned 2 clients this January. I closed one in the same month, and the other closed at February. The first client would be listed under the first table (Completed within the month) and the other would be on the second (Completed on previous months)
Since this would be monthly, I am thinking if it would be more efficient to include the table per sheet (as one sheet corresponds to a month), or consolidate everything to a single sheet... I'm not sure which one would be best.
I have created a drop down cell based on the cells in the first row of a particular table. Now when i select a particular cell from the drop down i want to fetch all the values in the respective column of the selected cell on to a different group of cells. While fetching the data to those group of cells, i want to ignore all the null, blank cells. All this actions shall be automated and shall happen simultaneously on selecting the drop down.
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
How would I write a sumif function to only add consecutive cells that match the criteria? Here is the formula I am using right now, but it adds all of the cells that match the criteria, not just the consecutive ones. Also they need to be consecutive cells from the current cell (in the formula below i would be referring to A80)
Column A is a group of dates, but not in order, they are slightly mixed up Column H is a monetary value.
I want to read information from the "Mapping" sheet, then find information in the "GLEX" sheet that correspond to the read information, then copy the information that was found in "GLEX" to the "Calc" sheet and add it all together and display the sum in a particular cell in the "Report" sheet.
The "Mapping" sheet is a mapping of information to show what makes up a particular total and where it must be entered in the "Report" sheet. The "GLEX" sheet is the output from a financial system and the source of the information that must be "reformatted" to the "Report" sheet. The information is only an extract as the whole totals to around 10MB. I have also deleted the "Report" sheet as it is fairly big on it's own, but the principal will be the same, I think. (If i should upload the "Report" sheet,)
Step 1: "Column A" and "Column B" of "Mapping" are the starting points. The macro must read the values in "Mapping" "Column A" and "Column B" and remember them. Then it must find the row in "GLEX" "Column A" where the information from "Mapping" "Column A" matches and where the information from "Mapping" "Column B" matches with the information from "GLEX" "Column E".
I am using the below code to count records in a range that meet multiple criteria.
myCount=0 For Each dCell In Range("MyRange") If dCell = var1 And dCell.Offset(0,-1) = var2 Then myCount = myCount + 1 Next dCell
Is there a faster way to count multiple criteria in a VBA procedure than what I'm doing, but without using worksheet formulas? I tried "WorksheetFunction.Sumproduct" like you would in a worksheet formula, but that didn't work (unsurprisingly).
On my worksheet (which I have stripped down significantly to be able to upload here), I am looking for a way to copy certain cells over when the TCR (machine) numbers match. So, on the spreadsheet, if there is a 1 on the De La Rue sheet in the A column, it will then copy the value that is in cell B (directly next to it) and paste it into the balance sheet tab in column J in the row that has the same number in it. It would then do this for all machines in the balance sheet.
Then it would go to the "Ecoin Amounts" sheet, and do the same match, copy, paste, but this time it would be if the machine number matches, it takes the value from column D and the value from column H and pastes these into the balance sheet sheet in columns C and E respectively.
As you can see from the balance sheet tab, I tried using a VLOOKUP function, but when I import my initial data to the sheets, and manipulate it the way that I want, it gives be a reference error. I could possibly circumvent this by writing the VLOOKUP amounts as part of my macro, but I wanted to see if this could be done any easier.
We have a Job List, which is a workbook dedicated to all the jobs we have been involved in this company. The Job List has information such as job numbers, job names, client etc etc. For this forum the information of interest is job number an job names. In a seperate workbook is our timesheet. Is it possible that when i type a job number, in say cell A1, that the job number is recognised and then corresponding job name will be fetched from the Job List workbook and placed in cell A2?
I was trying to define a function that could fetch data from MS Access Db to Excel using the below code (from a very old thread in Mr Excel) but am getting no return value in Excel. The value displayed is #NAME? ...
I have an output table that looks like the followings:......
There is 1 input for the spreadsheet: What month? Enter 9, 10, 11 etc. I need it to pull from a data sheet that is set up as such:......
So effectively it needs to vlookup the name in column A, (A, B, or C), and then it needs to match that to the column that has the same name as the output table (Crit.1 or Crit.2 and the number of the month above it (10,11,etc). I attached an excel sheet to better demonstrate
I need to match data with specific criteria. Details of the problem are as follows:
I have 2 sheets: "Main" and "Sub"
In "Main" there are 3 Important Columns: Col "C", "I" and "AF" In "Sub" there are 2 Important Columns: Col "I' and "N".
Value in column "C" of "Main" is same as value in column "I" of "Sub", but not in same order.
Now the specific criteria:
If value in Column "I" of "Main" is not equal to "Zero", then the macro should pick up the value from the corresponding column "C" of "Main" and search for the same in column "I" of "Sub". After matching the same, it should find corresponding value in column "N" of "Sub". Then the macro should pick up the value from column "N" of "Sub" and put the value in the column "AF" of "Main".
This is basic requirement.
I am unable to retrieve the right value for the same.
Fine tuining will be:
If the value in column "N" of "Sub" is "Fully Automated" then it should only write "Automated" in column "AF" of "Main". And If the value is "Manual + ...." then it should write "Manual" in column "AF" of "Main".
Modifying the following line by replacing the row number in AC2 and Q2 by an incremental variable? I mean something like “AC”&Rw , “Q”&Rw, &Rw, Rw being my incremental variable used to go through the rows.
How do I return an offset value within a named range using a formula? For example, my range "RngTest" is from C3:J43. If a value of "Product123" is found within the range, (lets say its found in cells C3; E5 and E7), I need the accumulative values in the cells 'below' (in cells D3; F5 and F7) added ( SUM) and returned to cell L3. Therefore if Cells D3; F5; and F7 have the respective values of 3; 4 and 5, the value of cell L3 should be 12. Also, do I need to express "RngTest" or "C3:J43" or does it matter?
I am trying to solve a small problem that i have, I am not that good with VBA and need somehelp. heres the situation. Sheet 1 is filled in col.1= title, col.2= table# (ex. 1,2,...11)col3.= grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10). I will enter a title then pick a table then a grid location. On sheet 2 also known as table 1. col.1 =grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10) col. 2 = title.
I want to create a macro or function that can evaluate sheet1 and pick out when col.2=table 1 and col.3 = grid loc. A-1.... through D-10 then give me what is in col.1. this will have to be done for 11 sheets 1 sheet for each table.
I have two sheets where I dump data into. Widget sales and Widget Accessory sales. I'm trying to display for the widget salesmen the amount of dollars they sell in widget accessories ONLY for the transactions in which they also sell a widget at the same time.
So I need a formula or process (taking and comparing the data from both sheets) to do the following
IF 1. The dates of a widget sale and the accessory sale match (in both sheets) 2. The salesman ID in both sheets match 3. The names of the customer in both sheets match THEN 1. Add up the total dollars by salesman for sales that match the above criteria
I've tried using sumproduct... I've tried using countif and concatenating pieces of data to make unique sales codes... I've exhausted my meager excel skills .
I need to compile a 'sumifs' formula to add sales for the salesmen.
Below is an example table. My dilemma is determining that in addition to the sales I only want to 'sum' the 'CANCEL' sale of a customer that initially purchased a 'GOOD' Status and not a 'PENDING' status.
How can i write a formula to determine that (for example) Customer3 'cancelled' the 'pending' sale, therefore I do not want the 'cancel' to sum in my calculations.
Date Salesman Customer Product Status Sale Jan 8th John Customer1 Apples Good $500 Jan 9th Mark Customer2 Pears Good $200 Jan 8th Kevin Customer3 Oranges Pending $250 Jan 15th Mark Customer2 Pears Cancel $200 Jan 16th Kevin Customer3 Oranges Cancel $250
setup a vba code to find values which were separated by a certain value, thus there would be pairs of numbers sought after. What occured was that the first value is bolded, followed by the second not bolded, this pair would be separated by 75. Now I want to apply the same code, but instead of copying just the two cells which were separated to someone on the current sheet, I would like it to copy the entire row (as there is more data associated with it now) with the same bold/not bolded pairing to a new sheet, called "Numbers". I would imgine it be straight forward, yet I am very new to this and still tinkering.
I have been asked to analyse the contribution / performance of each student in a team. What i am trying to do is to count the number of times each student has work together and then try to work out their contribution. Got stuck on the 2nd bit for like 3 weeks flat. have been trying but not luck... seems to give me 1 or 0. so i assume my attempt is only a true or false answer! All i need is a count of how many times each student work with each other, and the best backbone formula is "count". This situation is best explained by my example, D15 is the closest i got
I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. Please note that there is no fixed number of inputs for any one group i-e the solution has to be dynamic.The groups are numbered from 1 to 6 and number of groups is fixed i-e 6.
A sample file illustrating the inputs and required output is attached.