Sheet one contains a table of table from C6:AY7000
On Sheet 2, I want to create a summary of data from Sheet 1 based on 2 Criteria which applies to column I and N.
On Sheet 1, I have put in a formula to link to sheet 2 for the criteria to make things easier.
So BN3 = Sheet2C3 And BN4= Sheet2C4
I have managed to run my coding to filter the data that I correctly want to copy and paste onto sheet 2 - however I only want to copy specific columns.
This is the coding I have written to select and filter the correct data values.
Sub AddFilter() ' ' AddFilter Macro ' Dim rCrit1 As Range, rCrit2 As Range With Application .EnableEvents = False .ScreenUpdating = False
This now gives me the correct data in the table.
I want to copy the visible cells from this table but only for columns e, dr, r, v, w, o, z, AD, AG, AQ, AW, AY
The copied data will then be pasted into sheet 2 starting in cells B9
I plan to attach a button on sheet 2, so when the user updates the values in C3 and C4, this will update values in BN3 and BN4 on sheet1, they can run my macro which will go to sheet 1, filter the data on what they have entered and paste the correct values.
I have a very simple workbook in Excel 2000 with one page containing all my data which I update on a daily basis. Some of the data is linked to another workbook so that it updates each time I open it. Then I have 12 tabs, one worksheet for each month of the year with 10 simple graphs on each worksheet.
As you can see, it is a very simple worksheet. Each month, I copy and paste the graphs from the previous month into the current month and simply alter the Source Data in the graph to gather the current month's data. At the start of this month, it allowed me to copy and paste, but when I tried to alter the Source Data to the current month, I got a window on screen with a yellow triangle containing an exclamation mark and the words "No new fonts may be applied to this worksheet". If I click the OK button or the cross, it responds but the whole programme is locked. The only way I can get out is to give the 3 fingered salute and close Excel down.
I have a workbook with 33 worksheets and more than 150 charts. Now, when I right-click on a chart to access "Source Data" for example, I get a message saying "No more new fonts may be applied in this workbook". Then I have to click "OK" about a dozen times before I get access to the dialog box. Curiously, it happens for some charts and not for some others.
If filtering column C, just showing 'A's, by pressing the add row button, I want to copy the last cell in column C and pasting it in row 73, since it's the next unused cell when the worksheet is unfiltered.
I have a auto filtered applied to 1000 rows of data and the actual quantity of rows I currently see is 55 rows. When I do a filldown command to apply this formula, "=CONCATENATE(X$1,I51)" the result I get is wrong becuase where the "I51" value is at the formula is picking up cell values that are hidden due to the applied autofilter.
What do I need to add to this formula so that it only picks up cell values that are not suppressed by the autofilter? I want it to disregard the hidden rows when I do a filldown and only apply the formula to the rows that are not hidden due to the applied autofilter.
How would I write a formula if I was trying to get the cell to count all expired certifications (based on a range of cells and their expiration dates) as well as all blank cells? This is what I have so far and all it's doing is displaying "FALSE"... =COUNTIF(D2:D95,"
I have a spreadsheet that I want to sum the data similar to SUMIF or SUBTOTAL after a filter is applied. So I have data in column "F" that I appliy a filter and based on that filter I want to sum only the filtered totals in column H. Is there an easy way or is creating a new function the only way to go?
I have the following code (below) which calls a function to return the sum of values in a specific part of the array.
I haven't set the function up correctly as it is causing an object not defined error(424), but I can't see where and have been going round the houses changing it. I'm wondering if it's my confusion over when to use By Val?
Sub getDataIntoArray() Dim arrDataTot() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False
I have a worksheet that I'm trying to rank program selling. The range has subtotals that I need to exclude, so I'm trying to rank over multiple ranges. I've thought about LARGE in an array function, but can't seem to make that work. Is SUMPRODUCT a possibility? I've attached the worksheet, trying to get the top programs (again excluding the subtotals in bold).
I have a problem in applying Data Validation on cells containing Formula. Eg: cell A1 has a value 10 and cell B1 has a value 15. i have a formula "=A1+B1" in cell C1. If i apply a Data validation to the cell C1 so as to accept values only in the range of 0 to 10, the cell C1 is still accepting values grater than 10 when i change the values in the cells A1 and B1.
I can select an individual cell or range, select Format->Cells->" date" format, hit okay, and nothing actually changes unless I click on a cell so that it brings up the edit cursor. I can then hit enter (not changing the content of the cell in any way) and then Excel recognizes it as a date. The cell values are all in the standard "3/14/2001" format, with the exception that single digits are 0-padded, and there are no spaces preceding or following the date string. The issue here is that without the cells being recognized as actual dates, they cannot be properly sorted, eg: the original range:
01/04/2005 01/01/2005 01/02/2004 01/03/2005
is sorted, incorrectly, to:
01/01/2005 01/02/2004 01/03/2005 01/04/2005
...obviously for ascending order, the single 2004 date should be placed at top. Any ideas other than a custom date-text parsing macro? I could do that, but the size of each sheet, the number of sheets per batch, and the frequency of individual batches makes that idea completely impractical. ...Technically, my macro couldn't sort the above ranges correctly, but on further investigation, I noticed I couldn't do it manually either.
I have a thoroughly outdated system to keep a log of call centre stats. In order to facilitate a marginally easier workflow, I have rearranged a few things so that a user simply has to copy and paste data from reports into the worksheet.
The worksheet is set up with all the number formats like time and percentage which are extremely important in totaling and averaging hours and availabilities, however the pasted data doesn't match the destination format and it therefore affects the total.
I have tried many different special pasting options to no avail.
The only way i seem to be able to do it is to go into each line and retype my percentages (as 100 becomes 10000% ) and click the tick next to the formula bar so 00:00:16 is calculated as a quantity of time instead of text. With 30 people and 15 stats each day that exercise becomes a little bit tedious and counterproductive.
We have a macro that formats a report from our accounting program quite extensively (mostly moving text around). The last step is to select the entire worksheet and set the cell format to a custom setting #,##0.00_);(#,##0.00). We haven't been able to include this in the macro - we end up manually doing this step after the macro has completed. Is there any way to set this custom format as part of the macro?
I have a large spreadsheet from which I need to create separate Reports by hiding various rows and columns. I have followed instructions for creating a couple of Custom Views (View tab, Custom Views, Add, Inserted Name, OK, Saved the document) but nothing happens apart from getting the message "Some view settings could not be applied". In fact none of the view settings were applied.
I have the data sheet, which is the result of another function. It produces 10 columns with numbers in each cell, there can be any number of rows, but the key is that the rows are outlined as follows. The first row has the first column number bolded, followed by the second row not bolded and then a space and a repeat of the bold cell, followed by the non bold cell. Each row fills up all ten columns. I have attached an example of the sheet.
What I would like to do is create a function which says brings up a box that says "Input Row" and the user would input the bolded number of a row. From here the code would take that value entered and divide it by the inputted bolded numberís adjacent cell, to the right, aka B2 column. This value would then be multiplied by every other bolded rows second cell, B2 column, essentially giving rise to a normalization of that column. The output would then be displayed in a new column, column 11 in italics. Each row would now have its italics value corresponding to the bolded numberís row and not the other rows.
VBA in Excel 2007. Essentially, what I am trying to do is this:
I have to two columns (A and B) that have a drop down list in each cell containing text options. I created the drop down lists using simple data validation. Let's say that A has the following options (East, West, North, South) and B has (Up, Down, Left, Right). For each combination of these, e.g., A1 = East and B1 = Down, I want C1 to pull a unique piece of data (a $ amount) from a separate worksheet. I can get this to work in its most basic form but I want to be able to be able to do this for cells in rows 1 - 300 without having to write separate lines of code for each.
Additionally, I am having trouble getting C1 to update automatically once A1 or B1 are changed.
I want to return a value based on once off time related data which is captured at 4 time periods throughout a day. I have another set of data which does not line up with this data but I want it to return the value closest earliest value.
Given this data: 27-6-14 3:00 12 27-6-14 7:00 18 27-6-14 11:00 19
If I have a time like 27-6-14 5:00 I want to return a value of 12 (Previous time). How would this be possible?
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
I have a workbook that has worksheeets for every day of the month. The data in the worksheet consists of columns (employee #, name, clock in/out times, and break penalty).
What I am trying to do is create another worksheet that searches all the other worksheets for a "yes" in the "break penalty" column and then create a list of all the employees that received a break penalty for the entire month. I would like this to be able to auto populate throughout the month as data is entered and not have to use a filter every time I want to compile this list.