Search results for query: *

Hi Skip, It's ok I've managed to solve it myself by looking at some examples given on the Peltier Tech Blog and altering my code accordingly ... Here's my revised version below, it could be shortened / tidied up even more, but I'm happy with as it as a working subroutine. The only thing I'm...

Hi, Unfortunately my solution only works for charts where the .XValue labels are in the same order as my ColourLegend lookup table! If I sort the chart data (say by rank), then the chart labels and the colour lookup list are out of sink! So this brings me back to square one unfortunately. So...

Hi Skip, apologies I should have noticed the trailing space and stepped through with a watch on the variables, my bad! Regardless the routine does work beautifully now and only changes the bar in charts that I choose. Just one final little question, can you give me a quick explanation on the...

I've altered the subroutine as you've said above to take in the SheetName and ChartName ... see sub below, but now its not working :-( I'm not sure if its SeriesCollection(1) for all, but my answer to the question is that all the charts I'm trying to change the colour bar for are pulling their...

Hi Skip, I did what you suggested above and replaced the code to make it work for a single chart, it works great, especially now as its picking up the legend cell colours now, it's so easy to undestand when you can see the actual code. But it's still not quite how I would like it to work. I...

Hi Skip, Thanks, that works really well. I notice the colours are hard coded and not being picked up from the legend cells themselves, is it possible to pick up the cell colour and pass it to the chart bar or does it have to be fixed in the code? I still have s slight problem, the code tries to...

Hi, Please see attached, the first sheet contains the list of wards and the colours I'm trying to pass to each data point, the dropdown above the ward list selects the ward bar that I want shown in red. The other sheet contains the chart data and the bar chart itself, the selected ward is...

Hi, Can anyone help me achieve the following? I want to be able to alter the fill colour of a specific bar (say to Red) depending on the label name which I want to choose from a from a dropdown list. eg. I have a list of wards which have population values attached and are shown in a bar chart...

Hi, That's a solution of sorts ... but I only want to turn off the 'Protect Workbook' button when a particular spreadsheet that multiple users use is opened and turn it back on when closing. Your solution leaves the Ribbon altered for all spreadsheet's that are opened there after. Hence I need...

Hi, It's because I need to stop users from clicking on 'Protect Workbook' by mistake when they should only be able to use 'Protect Sheet'. Why do you need to know my 'intent'? What a very strange response to a very straight forward question. If you're not able to help then I thank you for...

Hi, Can anyone show me how to disable / enable the 'Protect Workbook' button using VBA, the button is located under the 'Review' menu option on the ribbon bar. I've looked at the official Microsoft Control Names list and I believe the one I'm trying to toggle is the following: Control...

Hi, Can anyone show me how to disable / enable the 'Protect Workbook' button using VBA, the button is located under the 'Review' menu option on the ribbon bar. I've looked at the official Microsoft Control Names list and I believe the one I'm trying to toggle is the following: Control Name...

...of missing brackets ... hence it didn't work before. Shown below is the final solution that does work =SUMPRODUCT((INDEX(sysClaimsAll,,1)="ABG")*(INDEX(sysClaimsAll,,6)="Housing")*(INDEX(sysClaimsAll,,VLOOKUP($E$19,sysClaimsOffSet,2)))) But thanks to 'AnotherHiggins' little example above I...

...error because there are text & numeric values being returned, but how do I get around this annoying problem? =SUM((INDEX(sysClaimsAll,,1)="ABG")*(INDEX(sysClaimsAll,,6)="Housing")*INDEX(sysClaimsAll,,VLOOKUP($E$19,sysClaimsOffSet,2))) I'm trying to sum up all the rows that have both 'ABG'...

Just to end this one once and for all: Combo & Gruuuu I now know what you have stated is correct, easier and works, thank you for the example as well. Now I can actually see how it's done! Cheers

Hang on ... I come up with a solution that actually works and you say 'NO, that is not the case' ... It WORKS, Am I missing something here? My original question stated that I did not want any cell references to be used in my formula. A working solution is good thing in my eyes (be it slightly...

Solved: Not a problem now as I've solved it by using any of the following formulas depending on which neighbouring value I want to look at: NextCellUp: = INDIRECT("R[-1]C", 0) NextCellDown: = INDIRECT("R[1]C", 0) NextCellLeft: = INDIRECT("RC[-1]", 0) NextCellRight: = INDIRECT("RC[1]", 0)...

I'm not doing a simple reference because I have thousands of values that I would have to reference individually in the 'Conditional Formatting' option. When you set one up and copy it down, it still references the original cell in all cases. I would have to go into each one and manually alter...

Quick Explanation: The formula returns the value contained in the cell directly to the right of cell that has the Conditional Formatting formula. In my Conditional Formatting condition I am using the following choice: Cell value is not equal to =OFFSET(INDIRECT(CELL("address")),0,1) This...

Hi, I have written the following little formula which works well except for a known problem which I've now discovered whilst trawling the net: =OFFSET(INDIRECT(CELL("address")),0,1) The above formula always checks the last cell that was processed and not the one I always want it to check, in...