Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.18 seconds.

  1. Replies
    8
    Views
    246

    Re: Unique in Range

    Something that I just tried in another thread inspired this idea.

    Using the same layout as Peter in the previous reply, try this array formula in H2, confirmed with Ctrl Shift Enter
    ...
  2. Re: How do I get Excel to return a value based on the max value in a table?

    With newer excel versions, perhaps this with wrapped text (array confirmed with Ctrl Shift Enter)

    =TEXTJOIN(CHAR(10),1,IF($B$2:$F$5=MAX($B$2:$F$5),$A$2:$A$5&"&"&$B$1:$F$1,""))
  3. Replies
    3
    Views
    173

    Re: Macro for DATEVALUE

    If you're simply trying to get around the problem of selecting a column, running the macro, selecting the next column running it again, etc. Then try this with the whole range selected.

    Sub...
  4. Replies
    3
    Views
    173

    Re: Macro for DATEVALUE

    Try copying an empty cell, then paste to your table of dates using PasteSpecial > Add.

    I think it should work with your dates if text to columns is working, if it does then you should be able to...
  5. Re: Listing holidays separately and finding values in aggregate

    As before, these will need array confirming.

    Excel 2007

    =IFERROR(SMALL(IF(ISERROR(MATCH($A$2:$A$10,$I$2:$I$10,0)),IF(WEEKDAY($A$2:$A$10)<6,$A$2:$A$10)),ROWS(C$2:C2)),"")

    Excel 2003 or older...
  6. Replies
    6
    Views
    53

    Re: Laptops and large Excel files

    This could be worth a read. https://docs.microsoft.com/en-us/office/troubleshoot/excel/clean-workbook-less-memory

    You might find that there is not one specific thing causing the problem, but an...
  7. Replies
    6
    Views
    53

    Re: Laptops and large Excel files

    What is the actual file size?

    While graphics (embedded images) might inflate the size of the file, I doubt that they should affect performance too much.
  8. Replies
    6
    Views
    111

    Re: Problem with NETWORKDAY.INTL Function

    Ignore that, I had overlooked the most simple fix

    Array confirmed as before.
    ...
  9. Replies
    6
    Views
    111

    Re: Problem with NETWORKDAY.INTL Function

    Can you modify the layout slightly?

    I have just noticed that your holidays are spread over 2 columns, so the change will not work. I'm going to try a couple of other methods, but I think that it...
  10. Replies
    6
    Views
    111

    Re: Problem with NETWORKDAY.INTL Function

    Array confirmed with Ctrl Shift Enter

    =IF($K$7="","",IF(NETWORKDAYS($K$7+1,$E$8+1,$T$35:$V$67+1)<1,"-",NETWORKDAYS($K$7+1,$E$8+1,$T$35:$V$67+1)))

    If you have blanks in the holidays range then...
  11. Re: Add asterisk to string with number format

    Assuming that this is to format barcodes, a custom format of \*@\* should do it.
  12. Re: Listing holidays separately and finding values in aggregate

    For excel 2007, array confirmed with Ctrl Shift Enter.

    =IFERROR(SMALL(IF(ISERROR(MATCH($A$2:$A$10,$C$2:$C$6,0)),$A$2:$A$10),ROWS(E$2:E2)),"")

    For excel 2003, array confirmed with Shift Ctrl...
  13. Re: Excel 2010 formula to look up duplicates so I can contra them off

    Assuming references in column A and amounts in column B, try this formula

    =COUNTIFS(A:A,A2,B:B,-B2)

    This should give you a count of 1 for each record that has a positive amount and a...
  14. Replies
    8
    Views
    246

    Re: Unique in Range

    This came up as the result of using the search box at the top of the page
    ...
  15. Re: Need Help with Array Formula to return multiple results for specified criteria

    Looks like you almost had it right,

    The formula in A7 should be ROWS(A$7:A7) not ROWS(A$7:A16).

    A slightly shorter version that might be easier to manage when adding extra criteria, as long as...
  16. Thread: Formula

    by jasonb75
    Replies
    1
    Views
    71

    Re: Formula

    You don't need a vlookup formula for that

    =ISERROR(MATCH(F2,Sheet1!$A:$A,0))
  17. Re: Listing holidays separately and finding values in aggregate

    That's not quite how the forum works.

    An attachment should only be needed to clarify details on a complex question, your post should contain enough information to explain what you need without the...
  18. Re: Name manager - adding data manually as reference

    It might work if you set the the 'refers to ' range as an array constant, I haven't tried this so no guarantee. It looks like you're trying to make something simple more complicated than it needs to...
  19. Re: Listing holidays separately and finding values in aggregate

    An alternative to enter into C2 and fill down to 10, this one will pick up a holiday or weekend in A2 rather than assuming it is always a working day. It is also error trapped to stop at the last...
  20. Re: Position the "hyperlinked cell" in the middle for easy view

    Sorry to say this, but you heard wrong. It would only be possible with vba. See the answer provided here https://stackoverflow.com/questions/23318608/excel-center-screen-on-hyperlink-cell
  21. Re: "Choose" function for the Best Running Time

    It might be possible with choose, but I would look at aggregate.

    For 0 to 10, try

    =AGGREGATE(15,6,$B$1:$B$100/($A$1:$A$100> E1)/($A$1:$A$100< F1),1)
  22. Re: Concatenate Multiple Rows until Non-Blank in Adjacent Column

    If this is a one off then you really don't need a macro for the task.

    Assuming headers in row 1, data starting in row 2, enter this formula into C2 and fill down to the end of your data.
    ...
  23. Replies
    46
    Views
    1,034

    Re: Indirect alternative for external workbook

    To make it work in such situations, you would need to re-create the formulas to match the changes by repeating the process.

    One of your earlier replies implied that you might already have a macro...
  24. Replies
    46
    Views
    1,034

    Re: Indirect alternative for external workbook

    I didn't think that this method would be suitable based on the requirements set out in post #1 , but given the problems encountered I'm going to suggest it as an alternative anyway.

    This method...
  25. Replies
    46
    Views
    1,034

    Re: Indirect alternative for external workbook

    .FormulaR1C1 should be able to do it without looping.
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4