I have minutes of meeting and have to divide them into different sheets against completed, and different departments and dept HOD responsible for it. How do I do it without "Copy/Pasting" the code? Also I have to remove the ones that are done. Any macro or something that can be written for these.
format of sheet
Sl No. | Date | Remarks | Dept | Dept Head 1 | Dept Head 2 | Status
I have to get data into different sheets against Dept, Dept Head 1, Dept Head 2 and remove the "Done" status to different sheets.
I have two sheets. Sheet A (Receiving Log) contains all orders with NAME (D1) and QTY ORDERED(E1). Sheet B(Component Reject Tag) contains all rejects with NAME(D1) and QTY REJECT(E1). Sheet A and B are Many to Many relationship
What I am trying to figure out is:
Match the names from Sheet B to Sheet A and sum up the QTY REJECT from Sheet B divide by the sum of QTY ORDERED for the NAME from Sheet A.
Business would like to get QTY Reject percentage by each NAME (Supplier)
In a final table lets say Sheet C I would like to see NAME, QTY Ordered, QTY Reject, Ratio in which will allow my to perform a Top 10 suppliers of highest reject percentage.
Another note to mention, Sheet C presumably to have all the formulas as in Sheet A and B will be overwritten and pasted with new data.
I am working on my thesis empirical part now I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
I'm trying to divide the value in column A by the data in B:AO using VBA, however I need to repeat this for many rows (the values differ for each row).
There could also be a different amount of rows with data in each time, and there is also a single blank row after every 18 rows with data.
I would normally sort out using Text to Columns, but I can't in this case as the remaining elements can vary.
I have a list of names with Forename and Surname and then the elements after that vary.
Names are written in one cell under a single column like so:
John Smith 13/04/2000 James Smith 123 123 1233 Jane Smith 07122 12345 Another Smith AB99 9CD
All I want to do is end up with 3 columns of data, Forname | Surname | Other
Is it possible to use a formula to make the breaks into each column at the first 2 spaces only, therefore the contents after the second space would just end up in one cell?
I would like to use VBA to search a folder and copy data from tabs within the excel files there. The data will be pasted to a tab of same name in the the main file. All the files are in the same format.
So far I have only managed to list the files in the folder using code I found on your site!
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode() ' 'Sheets("Summary"). Select If Range("a7") = "complete" Then ' Sheets(Array("1", "0")).Select Sheets(ArrSh(1)).Activate
on sheet1 I have a button I need to do the following when clicked: (1) name the next 30 sheets based on cell values in sheet1 (2) for those 30 sheets, hide some of them based on a y/n input in sheet 1
To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.
Need to move data from 14 sheets (1 pay period=14 days)(2 showing on attachment for example purposes) to time cards for each person (number of employees will vary).
Key data to move to the time card is the date of the hours, #Reg Hours, #OT hours, the ticket# and job# for those hours.(ie Chris Adams Aug 15, need Chris's Reg and OT hours (8 Reg and 2 OT) on his time sheet, and the corresponding ticket number and job number (in this case from I1, I2).
Unknown number of ticket/job numbers, but the pattern of cell entries will remain the same along the top of the date sheets.(starting at I1, then L1, O1, the next would be R1)
Unknown number of employees but they will continue filling down where the names are on the date sheets (column A). Each employee would have a time card sheet as well.
I have conditional formatting on a cell based on a formula, when I entered the solution of "if then 0 " but the cell doesn't see the result as a 0, if I manually enter the 0 the conditional format works.
I even tried putting the formula in another cell and then referencing to that cell, still didn't work...
The "divide by zero error won't trigger the conditional either......
If I have a value of lets say 53, I need it broken down into equal and whole numbers across the spread sheet with the last one being the remaining value.
Currently for example in cell F5 I have a value of 41 that I need to break down so I have done the following: In Cell P5 =IF(F5>=25,F5-21,F5) Then in Cell Z5 =IF(P5>=25,P5-21,P5) and this goes on until I have zero value from the original value in the last cell can i not do this all in one formula and have the results populate in other cells.
I am creating a 'weekly average spreadsheet' (Excel 2007) for a teacher. It has a column for each student's name and his test grade for each day (M-F). The last column gives his weekly average.
She also needs an average for all the students' for the week (vertically). My formula is correct, i.e., =AVERAGE(H2:H11). However, I'm getting a divide by zero error because I have some blank rows above my formula (so she can add new students' names as they come.
How can I get rid of the divide by zero error so she can see the running averages as she inputs grades (but still leaving empty rows for new names)?
I have just started to try and learn VBA for excel. the resource I have been using are books and the WEB. Some how, not sure how there was/is some code where all enteries in all worksheets are divided by 100, i.e if I enter 2222 after clicking the enter button the result in the cell is 22.22. I have deleted all macros and code however the problem still exist.
I have a database that requires me to find the average of some data over several columns, however if one of the values I have to divide by is a zero I get a 'Can't Divide By Zero Error' is there any way to override this ? So that if a Divide by Zero Occurs, instead of returning a 'Can't Divide By Zero Error' it just replaces it with a Zero instead ?
Excel has a QUARTILE function to divide a population into quartiles. I'm trying to do something similar but divide a population into deciles (i.e., every 10%). Is there any easy way to divide a large population into deciles and to assign a decile to each value in the population (i.e., top 10%, 11-20%, 21-30%, . . . bottom 10%)?
I'm lost on VBA, but I have been successful at recording a few extremely simple macros. I have a number of pdf statements that I have run through OCR, and the OCR often misses the decimal point so that 43.85 comes out as 4385. (this is in dollars, so everything has 2 decimals).
These cells are usually pretty easy to spot, so I want to select that cell, press a keystroke or 2, and divide the value in that cell by 100. I assumed that by selecting "relative cell references", and recording a macro with the keystrokes "<F2>/100<HOME>=" (without the quotes, of course), I would accomplish that. But instead, it places a constant in that cell, with the value obtained in the cell where I recorded the macro.
I may not even be calling it the right thing. but let me explain what i'm looking to do. i've got three columns on a spreadsheet and i need to be able to add two columns together and then divide the sum by another column. so something like this:
What I want to do is to divide the sum of those cells with the number of cells with a value > 0 (but all cells must be controlled to check if the value > 0).
In real life it's easy to see that I want to do 14/3 (A1>0, B1>0 and E1>0 equals three cells), but how to write this formula in Excel?
I have to divide two columns. Sometimes there is a zero in one of the columns. Now, I realize that nothing can be divided by zero, but isn't there a way to show a zero instead of the #ref symbol???
And i would be extending it to the rows below. Now, obviously, if C2 contains a 0, it gives a divide by 0 error. How to modify the formula which makes it valid only if C2!=0(not equal to 0)
