Posts by RSquirrel

  • Variable Holidays in Networkdays function

    The Holidays argument in Excel's Networkdays function is simple for those holidays with fixed dates (e.g. 12-24, 7-4, etc.) but how can those holidays whose dates are not fixed (e.g. Easter, Labor Day, etc.) be accounted for (I assume with some dedicated VBA code, as opposed to a formula)?

  • Identify 3 pay-period Months

    I have a spreadsheet that produces a monthly automated report for our HOA. By automated, I mean that it uses a combination of concatenated text and dollar figures to generate a series of annotation statements in the resulting report. The basis for adding one of the lines to the report is the number of bi-weekly pay periods (i.e. every other Wednesday) in a month, with the first one being January 13, 2021. Normally there are two per month, so nothing to report. But when there are three in a month (e.g. June and December for this year), a line is added to the report to explain that the “compensation overage is due to three pay periods for the month.”


    I need a sub to spit out the month names (or month numbers are okay, too) for the months that will have three pay periods, with the year as a variable. Since it’s a progressive 14-day increment, it cannot be based on a fixed date for each subsequent year.


    If what I’m asking is overly complicated, a simpler method that asks the user to input the starting date for the first pay period (e.g. 1/13 for this year, 1/12 for 2022, etc.) would be almost as good.


    Any help from someone who has tackled this, or can suggest how to configure what appears (given my limited Excel VBA knowledge) to be a rather complicated formula, would be greatly appreciated. Thanks for listening . . .

  • Vlookup output as a cell argument

    Dear Carim,


    Per you suggestion, I have attached a pared-down version of the file (most extraneous details removed). Also, here are some relevant notes (please pardon the cap's - not shouting, just copied the upper case notes from the spreadsheet, where they are also included). I greatly appreciate any assistance that you can provide.


    At cell C4, I have listed the formulas used and steps taken to build the code for Cell D7 and the results.


    MY QUESTION FOR THIS FORUM RELATES TO THE PROPER CODE TO RETRIEVE THE INDEX COLOR OF THE CELL ASSOCIATED WITH THE CONDO NUMBER ENTERED INTO CELL 'D5' ('43,' FOR EXAMPLE - WHICH IS THE BLUE CELL IN BULDING B - OR OTHER CONDO NUMBER AS QUERIED BY THE USER). ONCE IT IS WORKING, THIS CODE WILL RESIDE IN CELL 'D7.'


    ULTIMATELY (WHEN DEBUGGED) THE CODE FOR 'D7' WILL UTILIZE THE UDF "GetColor" WITH THE INDEX COLOR ASSOCIATED WITH THE CELL ADDRESS FOR THE SYMBOL REPRESENTING THE CONDO NUMBER ENTERED INTO CELL 'D5' AS THE Rng ARGUMENT AND "index" AS THE ColorFormat ARGUMENT.


    THE CELLS IN ROW 13 ARE ONLY COLORED TEMPORARILY FOR PURPOSES OF TESTING/VERIFYING CORRECT COUNTS.


    FOR PUPOSES OF A SIMPLIFIED .xlsm FILE (attached) FOR THIS POST, THE VLOOKUP TABLE (CV2:CW44) IN ONLY PARTIALLY POPULATED. THE FIRST COLUMN IS THE CONDO NUMBER, THE SECOND COLUMN IS THE CELL ADDRESS FOR THE SYMBOL REPRESENTING THAT CONDO.


    THE 'INPUT-QUERY INTERFACE' IS CURRENTLY NON-FUNCTIONAL (FUTURE IMPLEMENTATION).


    COMMENT CAPTIONS (I.E. INDIVIDUAL 1-, 2- OR 3-DIGIT CONDO UNIT NUMBERS) ARE NOT YET FUNCTIONAL (FUTURE IMPLEMENTATION).

  • Vlookup output as a cell argument

    I have a sub called GETCOLOR which retrieves the index color of a cell.


    If I type it directly as =GETCOLOR (AJ6,”index”) it yields the background color of cell AJ6, which is 14 (green)


    However, I need to get the background color of that same cell, but expressed as the result of a VLOOKUP function. AJ6 is the output of the function =VLOOKUP($D$5,$CV$2:$CW$314,2,FALSE) and I need to use that as the cell (first) argument for the GETCOLOR sub.


    If I use it this way =GETCOLOR(VLOOKUP($D$5,$CV$2:$CW$314,2,FALSE),“index”) I get a #VALUE! Error


    “Excel Formulas and Functions for Dummies” states that for the function =CELL(“contents”,A1), if the argument cell contains a formula, the function returns the result of the formula and not the formula itself. Since the output of the VLOOKUP is AJ6, I tried the following:


    =GETCOLOR(CELL(“contents”,(VLOOKUP($D$5,$CV$2:$CW$314,2,FALSE)),“index”), but it says I’ve entered too many arguments for this function.


    I suspect that it has something to do with the placement of parentheses, but none of the placement combinations I've tried have worked. I'm sure it's something obvious and simple, but I can't seem to solve it - can anyone point out my error(s)?

  • Apostrophe Issue with Character Count

    I have a cell (A1) whose contents (formatted as text) = Raley’s Xxxxxx


    If I enter the following formula in cell B1, it yields: Raley’s


    =LEFT(A1,7)


    If I enter the following formula in cell C1, it yields: Yes


    =IF(LEFT(A1,5)="Raley","Yes","No")


    But, if I change the formula in cell C1 (apostrophe added, 5 changed to 7), it yields: No


    =IF(LEFT(A1,7)="Raley’s","Yes","No")


    So an apostrophe is recognized by Excel in the cell B1 formula, but not in the cell C1 formula. What am I overlooking?

  • Phantom "User:" in UserForm-initiated Printout

    Re: Phantom "User:" in UserForm-initiated Printout


    Actually, you did help. It did turn out to be a footer. Until you asked the question, I never thought to look because I did not intentionally create a footer (I rarely do), but I obviously did somehow. I just jumped to the conclusion that it had something to do with the UserForm (my first one ever), and that it must be some default setting within the UserForm setup. Thanks for the assistance . . .