Search:

Type: Posts; User: JustynaMK; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.02 seconds.

  1. Replies
    10
    Views
    530

    Re: Select last 12 items in a drop down list

    Assuming that your items are in Sheet1, column A (starting in cell A1), you can create a Named Range using the following formula:


    =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-12,0,12,1)

    This will...
  2. Re: VBA Code to Find Cells with a Particular color and copy those values to a new sheet

    Hi & welcome to the forum.

    Note that in order to retrieve a background colour created by Conditional Formatting you need to use .DisplayFormat.Interior.ColorIndex (or .Color) instead of...
  3. Replies
    3
    Views
    130

    Re: Error message on opening

    True - once you do it, you'll remember it forever :) cheers
  4. Replies
    3
    Views
    130

    Re: Error message on opening

    Hi Simon,

    You can disable it by going to File > Options > Add-ins > "Manage" drop-down list (at the very bottom) > select either Excel or COM Add-ins (you'd need to look for QuotesAddIn64.xll),...
  5. Re: ActiveWindow.Zoom - Need to change value but not sure how

    That won't be easy as your colleague used "Worksheet_SelectionChange" event. It means that every time you change a selection the Zoom will automatically adjust to either 80% or 52%.

    a) Keyboard...
  6. Replies
    1
    Views
    132

    Re: Issue with Index Match with duplicates

    Hi Richard,

    Try this Array formula (Ctrl+Shift+Enter):


    =INDEX($B$2:$B$11,MATCH(0,COUNTIF($F$1:F1,IF($C$2:$C$11=G2,$B$2:$B$11,"")),0))

    Final result:

    https://i.imgur.com/EmAU5M2.png
  7. Re: Find last value in row that is not a percentage value

    Hi Ian,

    A small edit to my original formula (I got inspired by Peter's answer):


    =SUMPRODUCT(INDEX($A$3:$H$3,1,MAX(IF(ISNUMBER($A$3:$H$3),IF($A$3:$H$3>1,COLUMN($A$3:$H$3))))))

    This is an...
  8. Re: Find last value in row that is not a percentage value

    Something like that should ignore text values (also an array formula -> Ctrl+Shift+Enter):


    =SUMPRODUCT(INDEX($A$3:$H$3,1,IF(ISNUMBER($A$3:$H$3),MAX(IF($A$3:$H$3>1,COLUMN($A$3:$H$3))))))
  9. Re: Find last value in row that is not a percentage value

    Ahh I think I know what you mean.

    You can use the following formula in cell J3 (row 3, price change for 2019/04):


    =1-(SUMPRODUCT(INDEX($A$3:$H$3,1,MAX(IF($A$3:$H$3>1,COLUMN($A$3:$H$3)))))/I3)...
  10. Re: Find last value in row that is not a percentage value

    Hi Ian,

    Just to clarify - would you like to find out what is the most recent price for each row?

    For example: row 2 = 25, row 3 = 25
  11. Re: Get weekdays between two dates in specific columns

    :) glad I could help.
  12. Re: How to return 'string A' if 'cell range X' contains 'strings B, C, ...' and other strings for other conditions?

    :) glad you figured it out. Take care!
  13. Re: Get weekdays between two dates in specific columns

    Hi & welcome to the forum.

    I needed to replicate your setup on the same sheet, but you can easily change that by pointing your "source" and "Target" to different sheets.



    Sub foo()
    Dim...
  14. Re: How to return 'string A' if 'cell range X' contains 'strings B, C, ...' and other strings for other conditions?

    Hi & welcome to the forum.

    You can create a following setup:

    1. In cells A2 and below (A3, A4...) you have your dates (03/06/2019, 04/06/2019...).

    2. in cells B2-H2 you enter the names of...
  15. Re: Count Public Holidays that falls on a Saturday

    @Fluff, oops, that was silly! Thanks, I'll adjust my formula now.
  16. Re: Count Public Holidays that falls on a Saturday

    Alternative solution - assuming that a list of your public holidays is in cells A1:A10:


    =SUMPRODUCT(--(WEEKDAY($A$1:$A$10,2)=7))

    (adjusted after Fluff's comment)
  17. Re: HELP: How to calculate time difference including weekends within time range

    Yup looks even better! :) + one new thing for me to learn.
  18. Re: HELP: How to calculate time difference including weekends within time range

    Steve you're absolutely right! Many thanks

    @realjam try this updated function:

    ...
  19. Re: HELP: How to calculate time difference including weekends within time range

    Alternatively, given that your dates are in cells A2 and B2, try the following formula:

    ...
  20. Re: show percentage formula result as a general number

    Can you do the following in cell AG10:
    =AG9*100
    and format this cell as General?
  21. Re: Create / Consolidate Data Range from Full Values

    You're welcome Andrew.

    And Peter, many thanks for sharing! It's great to learn something new.
  22. Replies
    4
    Views
    175

    Re: Countifs with Or tidy-up

    You're welcome :) jtakw's solution will also work perfectly fine. Cheers.
  23. Replies
    3
    Views
    178

    Re: Vlookup from external workbook

    Hi,

    You can use the following formula to "produce" the adjusted Workbook Name for a given day (TODAY()):

    ="IBT PRINT LIST...
  24. Re: Create / Consolidate Data Range from Full Values

    Hi Andrew,

    Assuming that your example is presented in cells A1:B10 (i.e. row 1 is a header, rows 2-10 are values), you can use the following formulas in columns D&E:

    Cell E2 - array formula -...
  25. Replies
    4
    Views
    175

    Re: Countifs with Or tidy-up

    Try something like that:


    =SUMPRODUCT(--($C$2:$C$3000="GATED"),($G$2:$G$3000=1)+($G$2:$G$3000="#")+($G$2:$G$3000="No Date"))

    Please note that I replaced "01/01/1900" with "1", as this is the...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4