I'm currently working on a model that calculates the costs associated with various phases of a project. I want to allow the user to input the number of weeks that each phase requires as part of the calculations. The result will then be displayed horizontally in chronological order with the values associated with each phase underneath ex:
I will need to sort the data based on some number entries being presented in Periods 1 ,2 ,3 in chronological order.. i.e. move the data of period 1 to the top , then period 2 and then period 3.
In Cell A1 i have a text string of "ABS9E8C2D" i want something is cell B1that will arrange the string in Alphabetical order for Letters, then increasing order for numbers "ABCDES289".
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
I am finding that I am often using Max(if(... formulas to pull in Staff ID's that have seen activity in a large data dump. I then use a VLOOKUP to return name based on ID.
Trouble is, the names end up out of order. I want to be able to order them in one of two ways - either by work done, or alphabetically.
I've attached a spreadsheet. I want the order of the sentence to change based on the numbers and whether they are positive or negative. Below are more details and the spreadsheet has numbers.
"Total expense for September was 8 higher (lower) than prior year, driven by a higher (lower) claims expense and admin expense"
This might be hard to explain: For the second part (starting from "driven by", I would like the variable that explains more of the positive or negative variance (in the example, claims) to come first.
Here is a different example
Total expense for September was 6 lower than prior year, driven by a lower admin expense (claims is not included because it is higher)
I have some VBA (using Excel 2007) that deletes and re-creates a bunch (20-ish) checkboxes in a spreadsheet. Since i'm using the controls-based checkboxes, Excel defaults them to object names of "CheckBox1, CheckBox2, CheckBox3, etc" as I create them the first time around.
Later on, i clear all checkboxes and implement the SAME checkbox-creating code to re-create all or some of the checkboxes, but Excel doesn't number them in order the second time around! I end up with something like "CheckBox2, CheckBox19, CheckBox3, CheckBox12, etc."
What could be causing my code to create things out of order the second time around? Is there a way to reset Excel's Checkbox-numbering iterator?
The code i'm using to create checkboxes is:
Dim x As Long Dim BoxCell As String
BoxCell = "" x = 0 'j gets passed into the sub as a "Long" somewhere between the values of 5 and 35
I'm trying to make an excel spreadsheet for a Kareoke business - what I want to do is have a database of CDs that they have in 1 sheet - this will include Song Name, Artist, CD Title, Track Number and CD Number - then I want the user to be able to go into the next sheet and type in the CD Number into a specified cell and thus will import all the information for that CD number into the required fields. I have attached a copy of what I sort of want it to look like. I don't really know the functions of Excel, but I'm quite computer savvy and should be able to work it out with instruction - please note, I've never used a "macro" and don't know what they are - so if I need to use a "macro" would you be able to give me a link to somewhere that explains what they are...and how to use them?
Since I'm making a PivotTable with my macro, when it runs I always get this...
‘The PivotTable report will not fit on the sheet. Do you want to show as much as possible?’
I always choose yes, because in the end the user isn't going to use all of the data, they will be narrowing it down. Anyway, this comes up a few times througout the macro. Is there something I can put in the code that, if pop up box, always choose yes?
I have a problem with excel, let say i have excel file (named A) and i also have another excel file (named B), file "A" is database, and consist of around 14 columns. so when in B, i type 1234 then in B i will get all the data that related to 1234 that consist of 14 fields and it will be placed in different place and different order.
Let's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.
How can I create a report to show the TOP 20% based on sales to grand total.
Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?
Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.
I'm using Excel 2010. I have a spread sheet with sales data covering 3 years and multiple customers. I was able to create formulas such as this to calculate the figures for the entire sheet (all customers) by year.
I believe that in order to use filters that show this date for a specific customer I need to convert this to a SUMPRODUCT formula, I've tried this multiple times and had no luck.
I have an issue with a Pivot table: I am using the 'show report filter pages' function and prior to this I have ensured my character length of that field is equal or less than 31 however when I hit the function the tab names are shortened to 26.
I have a spreadsheet that holds salary information. At the moment it shows against each person the highest salary for a person of the opposite ***. What I would like to do is have another cell that shows the names of all people who have that salary.
E.g. if the highest comparable salary for someone of the opposite *** is 30000 then I would want a list of all the people with that salary.
Is it possible to do a look up that would show all the results that match certain criteria?
In column B I only want to show the results from Column A if the character count equals 3. In Column C I only want to show the results from Column A if the character count is greater than 3
I have copy 65'000 formula text cells from a software and paste it to excel. But, the formula in cell is not activated. In order to activate the cell formula, I have to click on each individu cell and press enter to active it. But i got 65'000 rows of the similar cells. So, what is the quick way to all the text formula at the same time.
(There are 500+ more lines like the ones below, in groups of approximately 17 lines per page of information.)
* C.B.A. Bank - 2nd reference to branch near Mike's store. (21/03/1956) * Berwick exams results. (06/06/1877) * Berwick exams (09/05/1877) * Steps taken for erection of Berwick. (11/07/1874) * Berwick tenders for work at home. (11/07/1877) * W. was a storekeeper at Berwick. (25/07/1881) * Berwick Brass. (27/06/1877) * Commercial erected for son. (27/06/1878)
I need to do the folowing to it: (not necessarily in the following order)
- Insert into Excel - Remove brackets - Sort chronologically - Copy back into program I am using again, in same chronological order.
The desired outcome is to be able to throw into Excel all the lines and press a button, (possibly the Sort Ascending one) and have a chronological list of all entries.
I have three search boxes in XL , two are search from and to dates and the other is search be ref.
I can currently search by dates and ref but would like to show all records in the specified dates if the ref field is left blank.
I am using the query writer and referencing back to cells in XL. I don't use the wizard or SQL. I have tried many options and think I should be using a OR statement with a wildcard but can't seem to get the result, does the wildcard just show the blank cells in the actual data?
I am trying to determine the best way to graphically show the results of a time/work study. Basically i have a list of tasks and each list has 2 sets of numbers assigned to it. One for how much time it takes on a scale of 1-3 and a second for how much value is added for the task, again on a scale of 1-3. I want to show some sort of graph/visual that shows the number of tasks as clusters (not name of task) in maybe quadrants. With the time on X axis and value on Y axis. I am envisioning something like a 9 box that would have low/low to high/high. I can only think to create a 9 box in excel and physically put circles(clusters) in each quadrant.
Column B has several card numbers (e.i. 7987654345678) Column C has a location name (e.i. madrid) Column L has a date (the date is when the card was used)
What I want to do is identify the cards that appear more then twice on the same date and the same place.
Card Number Location Date
123 LOC 18/01/2013
[Code] .........
I would like through a macro or pivot to show me the results of the 2 last rows, because those are the only ones that are exactly the same.
I have two enormous lists of dates. How do I automatically compare them to ensure the date on one comes after the date on the other one?
They're formatted as dates. Christmas Day 2000 (UK) did say 25/12/2000, and then when I reformatted it it automatically changed to 25 Dec 2000, and so on.
I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?
I cant get this one off the ground. Normally I am pretty good to find bits and pieces of advice from other posts and put something together, but this one has me stuck from the get-go. I want to create several lists based upon the row(s) containing at least one set of criteria (day and time, contained in two differnt columns). Each row allows for up to 3 sets of days and times (i.e. columns B and C, columns D and E, and columns F and G) Each row is date and time stamped as to when the data was entered in that row (column H).
I want one list for each set of the various day/time combinations allowed in columns B thru G. The list should provide the name(s) contained in column A, in chronological order based on column H, when a criteria match is made in columns B:C, D:E, or F:G. This is far easier to understand with the attached sample. The lower section of the sample represents my desired output that I cannot seem to achieve.
I added an attachment with an example. You will see a matrix, with in the most left column an ID, then the headers of the matrix has different numbers that mean something.
What I want to do is, you fill in a number in the combobox, then press on a button and a report will be made on a new worksheet. With a list of the numbers and dates in the matrix that the ID has. Like shown in the example sheet in the workbook.
I need to create a report on each friday as well as on every last workday of a month. I have to display both weekly and monthly data on the same graphs and I always arranged the week ends and month ends myself, but this is taking a lot of time.
So I need a chronological list of of week numbers and month names based on the date of Friday of a week or last workday of a month.
Input: A1 - either month name ("MMM", Jul, Aug, Sep) or week number (1-52) A2 - year number (2014)
Output: For example, for 30 in A1 and 2014 in A2 it should look like this:
BD BE BF BG BH BI BJ BK BL BM
1 May 23 24 25 26 Jun 27 28 29 30
And this should strech back all the way back to B1 in which I need to have 31.
Note if a month ends on last workday of a month the week number should come first and month name second.
