Hello, I have some numeric data contained in two worksheets in a workbook. I would like a macro that consolidates (sums) the data from these two sheets into a summary sheet. I know the very basic elements of VBA but I don't have the skills to create a macro to sum the data. Hopefully the...
Thanks - yes, I understand. Sometimes "management" change their mind on these sort of things (change of the range break points, etc). If one range changes, then the next range would also change in a corresponding way. I was trying to see if it is possible to automate these sort of changes but...
HI vletm. Very good, thank you. I like the idea of the text boxes giving the background colour. Do you know if there is a way to link the size of these text boxes to the range values if the range values change (i.e. if the first range was originally 0-20, could the size of the text box change...
Hi Chandoo colleagues. I'm attaching a file with a bubble chart (which I think is appropriate for a certain application we're working on) but I would like to do some modifications to it without using VBA (if at all possible).
I think a lot of what I'm looking for is in the formatting of the...
Hi Chandoo colleagues. For some time now we’ve been using a 3-sector Speedometer chart in our monthly presentations to management (please see attached spreadsheet). The three sectors each represent progress (between 0% and 100%) of unrelated stages of a larger process. This works well...
Hi Kelli. Here is an alternative (more cumbersome calculation) that does the same thing as Chirayu. I've also used the first sheet. Is your estimate of Vicki's Desired result correct? You say this should be 20 years but the Age Result is actually 19.81 - I think this puts it into the "10 years"...
Hi Vletm - I agree that a VBA solution is going to be a lot quicker on this sort of thing.....unfortunately I am not very good at VBA. :)
Eloise - is there a reason why there are so many duplicates in this list? If you strip out the duplicates, you are left with approx. 1320 individual TV model...
Thanks Guido - yes, my explanation was not totally clear. It is only when the Start and Finish dates are the same (duration 0) AND there is the "Specific Text" in column B do we need to show the location of the Red Cell. I was thinking that this might need to be done in three stages (1st = row...
Hello. I am trying to create 3 conditional formatting calculations to give a table like the one below:
The Start and Finish dates will always be the same (these are Project Milestone tasks so have a duration of zero days). I'm able to get the horizontal yellow row using CF and the Seach()...
OK, thanks G. I hadn't looked at the Value Field Settings information. Is it possible to put a formula into this area to do the comparison I mentioned earlier? To make a fair comparison, there must be a positive value in each month for 2017 and 2018. So, I think it is something like: If Jan...
I've been looking at the Pivot solution a bit more. Can you explain how the calculated field is prepared? I would like to show the month-by-month difference (e.g. for each month, what is the difference between 2018-2017). But the calculation is a bit more complicated. To make a fair comparison...
Thanks guys. I do agree that the Pivot Solution is the better method in this sort of example but I just couldn't seem to get it to work. Also, I had a similar set of formulae in another worksheet and I thought I could tweak it. Thanks again for you replies.
Hi Guido - I see you have taken the Pivot Table / Chart route to solve the problem. I tried to do that too but I could not get it to work. I am more familiar with the formula method of constructing the data for the charts. But your solution does indeed answer the question. Is there a formula...
I have a simple question which I am trying to solve with a SUMIFS() formula. Attached is an extract from a Charity database. It contains a list of dates and a quantity of members that joined on particular dates. I would like to create a chart that shows how many members joined in Month X 2017...
Hi Alan. After I tried to do a few pivot tables on the data, I realised that the structure was not correct. I hadn't used the normalising function before but that might be one thing I can do in the future. I haven't used Access for many years and the people I share the spread sheet with have...
As one of my "non-work" activities, I am involved with a small charity. We can claim Gift Aid from the UK government on any monies (membership fees or cash donations) to the charity. We summarise these cash gifts and submit a claim to the UK government. The government then pays us 25% of all...
Thanks. I normally use a technique similar to the one in the Peltier example. A lot of my charts are dates on the X-axis and % on the Y-axis. Through trial and error, I have found that when using an X-Y scatter plot for the chart, it is fairly easy to insert a vertical bar at Today's date by...
Chihiro, this is a neat solution and very clearly explained. I had thought that I might need to use a LOOKUP function since the area of the data is not the same as the area of the output. I had thought of countif(s) too and also sumproduct but I could not get the calculations to work out. My...