Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. DAmoss

    Excel 2010 Charts - Change a specific bars colour based on its label value

    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...
  2. DAmoss

    Excel 2010 Charts - Change a specific bars colour based on its label value

    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...
  3. DAmoss

    Excel 2010 Charts - Change a specific bars colour based on its label value

    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...
  4. DAmoss

    Excel 2010 Charts - Change a specific bars colour based on its label value

    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...
  5. DAmoss

    Excel 2010 Charts - Change a specific bars colour based on its label value

    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...
  6. DAmoss

    Excel 2010 Charts - Change a specific bars colour based on its label value

    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...
  7. DAmoss

    Excel 2010 Charts - Change a specific bars colour based on its label value

    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...
  8. DAmoss

    Excel 2010 Charts - Change a specific bars colour based on its label value

    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...
  9. DAmoss

    Excel 2010 - Ribbon bar button

    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...
  10. DAmoss

    Excel 2010 - Ribbon bar button

    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...
  11. DAmoss

    Excel 2010 - Ribbon bar button

    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...
  12. DAmoss

    Excel 2010 VBA - Disable Ribbon Button?

    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...
  13. DAmoss

    #VALUE! error when trying to use 2 criteria in SUM() function

    ...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...
  14. DAmoss

    #VALUE! error when trying to use 2 criteria in SUM() function

    ...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'...
  15. DAmoss

    Excel: Return the neighbouring cell ref using a formula

    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
  16. DAmoss

    Excel: Return the neighbouring cell ref using a formula

    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...
  17. DAmoss

    Excel: Return the neighbouring cell ref using a formula

    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)...
  18. DAmoss

    Excel: Return the neighbouring cell ref using a formula

    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...
  19. DAmoss

    Excel: Return the neighbouring cell ref using a formula

    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...
  20. DAmoss

    Excel: Return the neighbouring cell ref using a formula

    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...

Part and Inventory Search

Back
Top