Posts by darth_chunk

  • Check for non blank cells in a specified date range across 2 work sheets

    Hi NBVC,


    Thanks again for helping out, and especially so quickly!


    I haven't seen the Index:Index formula before, it's interesting. I'll be filing that info away for future reference.


    Re the conditional formatting... I can't believe I overlooked something as simple as formatting based on the formula result! I'm pretty much banging my head against the desk right now in a mixture of embarrassment and frustration. Got it into my head that I'd need to reproduce the formula to achieve what I wanted. So yeah, thanks :0ops: :D


    Regardless, to summarise:


    Points 1 through 3 are working exactly as I had intended, thank you.


    Point 4 is not quite there yet. VBA code is copied and the formula you created is entered as an array but it's returning a NAME? error. I'm trying to trace the source but I'm not familiar enough with the code/formula to identify the source of the error.


    Any ideas?


    Thanks again.

  • Check for non blank cells in a specified date range across 2 work sheets

    Hi all @ Ozgrid,


    I've a spreadsheet I'm expanding and need to help with formulas that I can't quite wrap my head around.


    In the attached example, sheet 3 there are 3 adjacent cells where I want to pull information from sheet 1 using an Index/Match formula to return values based on the intersecting cells. I have added a description to each of the cells in the attached that fully explains what I am trying to achieve, but in summary it is this:



    Sheet 1:


    Has a table array, with dates 1st Jan through 31st Dec in row 1:1. Beneath this row cells may or may not contain data.


    Sheet 3:


    Column 1 has a list of values, these are consistent between sheet 1 & sheet 3 so can be used to index the results.


    The user will specify a date range in sheet 3 in cells C2 & C3 respectively for a "From" and "To" date range. I.e. the user may want to specify a range from 01/01/2018 to 31/01/2018.


    I want to calculate the following based on that specified date range:

    • In column C, check whether any cell in the range of intersecting cells on sheet 1 are blank or not, and return a Yes/No answer. (i.e. if date range 5th Jan to 15th Jan contains even one cell that is not blank, a positive result will return)
    • Conditionally format the active cell based on this answer (so a similar formula as point 1 to be applied to a conditional format)
    • In the 1st adjacent cell (column D in the attached example), count the number of non blank cells in the specified range and return the result
    • In the next adjacent cell to that (column E in the attached example), display the date, or the dates where the cells are non blank


    I've a concept floating in my mind about combining the following formulas, except I can't see how to nest them with an index search to yield the results I'm after;


    COUNTIFS(, ">="&$C$2,Sheet1!F$1:AJ$1,"<="&$C$3)


    to calculate the result of point 1 & 2.


    COUNTIF(Sheet15:5,"<>"&"")


    To count the non blanks in the same range of dates for point 3



    ... and I have absolutely no clue how to achieve point 4 on the list!


    Can anyone help me with the above? I have a feeling I'm on the right track but I can't figure out how to match the date ranges specified to the array of row 1:1 in sheet 1.


    Hope this all makes sense, if not let me know and I will clarify as best I can.


    As always, thanks to all for looking! :thanx:

  • Conditional Formatting: based on whether 2 separate cells contain any value

    Sorry, to clarify further: the column which lists "A-Z" will actually have different values than "A-Z" (a list of names). Your proposal is precisely what I am trying to achieve.


    The dates may not necessarily match between sheets though. The version I uploaded is much simplified which is why I was looking into checking the intersection range of cells.


    Query though, you mentioned not using absolute references in my current formulas, however I can't see a way to prevent this form happening with conditional formatting. Regardless of whether I make the reference absolute ($F$5) or not (F5), and then apply it across multiple cells of the same row to test it, the conditional format formula always refers back to the cell I initially entered as part of the formula. That's one of the reasons I was looking for an Index solution as I can't figure out how to make it dynamic...


    Hope that makes sense.

  • Conditional Formatting: based on whether 2 separate cells contain any value

    Hi NBVC,


    Basically yes. It may not be "A" precisely in the final sheet but yes, it has to return against whatever value is in that cell at the time. However, can Match be made dynamic in that way? I.e. looking at whatever the content of the cell is rather than matching specifically against the test "A"?


    Some further context, the left hand most column (listed A-Z) will be identical on both sheets if that helps.


    Thanks.

  • Conditional Formatting: based on whether 2 separate cells contain any value

    Hmmm... No takers so far.


    I've been further trying to resolve this and have made some progress, hopefully someone can help me past the finishing line with this one! I have attached a new sample spreadsheet, which is much simplified.


    In it I have successfully achieved the formatting I want but so far have only managed to make it apply to a single cell. In the attached example this conditional formatting can be found applied to Cell F5 in Sheet2. I have stacked to rules as follows;


    1. =AND(NOT(ISBLANK(Sheet1!$F$5)),COUNTBLANK($F$5)) (Set format 1) - Stop if true
    2. =AND(COUNTBLANK(Sheet1!$F$5),NOT(ISBLANK($F$5))) (Set format 2) - Stop if true


    This now works as needed, in that if there is data in Cell F5 of sheet one, but not in F5 of sheet 2 then then format 1 is set. Vice versa, if there is data in cell F5 of sheet2 but NOT in F5 of sheet1 then format 2 is set. Is the cells in both sheets are blank, then no format is set.


    Why am I looking for blanks instead of matching values you might ask? Simply because the data that is entered into sheet1 will never match that of sheet2, but the sheet user needs to know if the corresponding cells in either sheet contain or do not contain data.


    What I have not been able to do is apply this formula to the entire range of cells in sheet 2. Effectively, the conditional format needs to check the active cell it is referring to and compare it to the corresponding cell in sheet 1 before deciding which conditional format applies. I was thinking that a formula using INDEX & MATCH may be required but I cannot see how to get the conditional formatting to check against dynamic cells (as the formulas I list above use an absolute reference to the cell in question.


    Can anyone please help me with this? I am slowly going bald from all the hair pulling... :angrypc:



    Thanks for looking :thanx:

  • Conditional Formatting: based on whether 2 separate cells contain any value

    Hi all @ Ozgrid,


    I am trying to implement a conditional format which will apply to a range of cells and it's proving trickier than I had thought.


    The objective is as follows;


    I have a workbook with multiple sheets, each with a similar array of data. What I want to achieve is a validation check between the sheets, with the aim being that should a cell in one worksheet have data, and the same corresponding cell in the second worksheet NOT have data then the colour of the cell is changed to highlight this and therefore making it easier for the user to see where gaps appear.


    E.g.


    A1 (sheet1) is BLANK, and A1 (sheet2) is BLANK = No formatting
    A1 (sheet1) is NOT BLANK, and A1 (sheet2) is BLANK = Cell A1 in sheet 2 turns Red
    A1 (sheet1) is BLANK, and A1 (sheet2) is NOT BLANK = Cell A1 in sheet 2 turns Orange



    Problems I have:


    1. Cell A1 in sheet 1 may actually refer to Cell C1 in sheet 2 so I need to incorporate a MATCH function into this somehow (in the attached example, cell F5 on Sheet1 refers to cell E4 on Sheet2)
    2. The second cell always refers to itself and I am not sure how to work around this (have tried using INDIRECT but can't seem to get it working)


    The formula I was using seems not to do the trick.


    I have attached a simple spread sheet as an example.


    If anything in my description is unclear please let me know and I will clarify.


    P.S. The attached example is extracted from another workbook in which I received awesome help with a VBA problem from RoyUK here at ozgrid. The VBA is already in the sheet but is not relevant to this problem, I include it in case someone thinks of using VBA to tackle the problem in case the code clashes.


    However I would prefer it if a "simple" formula were the solution.


    If anyone can offer help with this I would be most appreciative :thanx:

  • Auto Hide Columns and/or Rows using VBA based on cell value (validation list)

    Hi Roy,


    Just testing out the spreadsheet now and works like a charm, thanks :) I'm sure it'll be well received!


    I noticed that merge & centre is disabled on the spreadsheet now (for good reason no doubt - as I understand it M&C can seriously screw up VBA codes). And for that reason I will be encouraging everyone who needs that aesthetic to instead use the 'centre across rows' formatting tool instead. However, I have noticed that this does not extend the formatting of the originating cell to all the others.


    Do you or does anyone else know a way around this? It's not exactly a show stopper but would be great to understand why.


    Thanks again for taking the time to help and resolve this problem, it's much appreciated :yourock:

  • Auto Hide Columns and/or Rows using VBA based on cell value (validation list)

    Hi,


    Attached is the latest version of the workbook with Carim's latest revision of the code ("SuperHero Patrol_VBA_TEST V2"), and also Roy's version of the code ("SuperHero Patrol_VBA_TEST V3"). Both work on the examples I have attached here.


    I'm just reading the examples of the date pickers Roy has linked to, looking at how I can integrate them.


    Cheers.

  • Auto Hide Columns and/or Rows using VBA based on cell value (validation list)

    Hi Roy,


    Thanks, I'll try that out as well. I was actually wondering how it would affect the sheet if I needed to add rows/columns to the mix, and this may do the trick :)


    I have a follow up request: feedback has been good for this spreadsheet, but I have been asked if I can make the filters more dynamic. To clarify, this is being used by colleagues of mine in our office and is not something I'm charging for or anything like that.


    Specifically, I have been asked if it is possible to be more selective in the date range to apply the filters to. I was thinking along the lines of adding a date picker with a to & from range which would then filter which columns are hidden to those specific dates.


    I have read that date pickers can be added to excel, but are restricted to 32 Bit versions of the software. Is it possible to add a date picker to the file, and could the code be altered to work with that date picker?


    I'd really love to hear people's thoughts on this.


    Thanks.

  • Auto Hide Columns and/or Rows using VBA based on cell value (validation list)

    Hi Carim,


    Apologies for my absence, away for a long weekend and then had to catch up at work.


    I've just reviewed the second version of the macro you submitted and it works perfectly - exactly as I had intended it to work. Many thanks for your assistance, it's greatly appreciated :yourock:


    Now I just have to sit and figure out how that code works. It's not something I would have ever thought to look at. Long way to go with learning VBA still it seems ^_^.


    Thanks again.

  • Auto Hide Columns and/or Rows using VBA based on cell value (validation list)

    Hey,


    Thanks @ Mumps and @ Carim for your help.


    @ Mumps, the code seems to do the trick for the column (months) filter I need although it takes a while to run through the process. I can't seem to get it working with the row (superhero) filter. It's also stopped working (I can't figure out why).


    @ Carim, thanks. Much cleaner than my code, but I run into the same problem. The code works up to a point but doesn't return the results I was expecting. So it works perfectly for example when I select 'march' from the list, but 'jan' doesn't work at all, and 'Feb' only hides the month of March and no others. It's baffling, it seems to work as a single code but stacking multiple variations 'breaks' it as far as I can tell.


    I am going to play around with both versions of the weekend and hopefully make some progress. I'll report back next week with results.


    Thanks again to you both for your help! :yourock:

  • Auto Hide Columns and/or Rows using VBA based on cell value (validation list)

    Hi,


    I'm wondering if anyone can help me with this VBA problem I'm having.


    To lay the backdrop; my understanding of VBA is limited and I've been reading tutorials to implement the functions I want. However, when trying to stack multiple 'If' arguments the code isn't behaving the way I expected it to.


    In short, I am using VBA to auto hide columns and or rows depending on the value of a cell which has a pre-determined list using a validation drop down box. The aim of this is to hide a range of columns, or multiple ranges of columns.


    So in the attached example, "SuperHero Patrol" when selecting 'Jan' from the drop down in Cell A1, I want the sheet to auto hide all columns where the date isn't January. When selecting 'Feb' it auto hides everything not February and so on. The first time I added the code it seemed to work exactly how I wanted it to; i.e. it would auto hide all columns that weren't January. But when I add the next logic string to auto hide everything that isn't Feb it seems to not work the same way. Instead of hiding the target range, it seems to hide a completely different range. Additionally the first logic argument now seems not to work at all.


    So the initial code is as follows;


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 And Target.Value = "Jan" Then
Range("AG:NH").EntireColumn.Hidden = True
Else
Range("AG:NH").EntireColumn.Hidden = False
End If


    This achieved what I wanted it to which was to auto hide all columns that weren't January. However, once I expanded it as follows, the code didn't work;




    Same is true to the code to hide the rows.


    Can anyone help to explain what is wrong with the code and why it isn't behaving the way I want it to?


    Thanks :)

  • Search for text within string and return preceding numerical value from same string

    Re: Search for text within string and return preceding numerical value from same stri


    NBVC,


    The only patternI can really point to is that there are alwasy two numbers in the string, and it is always the second number I need to extract.


    That formula works perfectly in the example data. Though quite honestly I don't even begin to understand how that formula works :stare:. Would you be able to explain the steps so that I can adapt it to other sheets?


    I.e., the "SEARCH(" "&$D$1:$D$5&" ",A1))-10,255) section. If I were to list the lookup criteria (bar, Radius, etc) as a named range, say "ozgrid", how would I adapt the fomula to suit; "SEARCH(" "&ozgrid&" ",A1))-10,255)?


    What significance does the "ROW($1:$30)))+0)" at the end of the formula mean?


    This is superb so far, thanks for the help. I just need to understand how I can adapt it to other spreadsheets where necessary.


    Thanks :)