Search:

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

Page 1 of 5 1 2 3 4

Search: Search took 0.01 seconds.

  1. Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

    Or just =LEFT(A1,8)&MID(A1,13,2)&"/"&MID(A1,9,3) where A1 is the cell containing your source string.
  2. Replies
    3
    Views
    99

    Re: Odd Pivot Table Behavior

    You obviously have very well structured data as a rule ;).
    Glad I was able to help.
  3. Replies
    4
    Views
    172

    Re: Insert New Worksheet

    Try using the INDIRECT function to look at the sheet before the current one. Find a cell in all sheets (say $A$1) that you can reference to give you the sheet number of this sheet, then use something...
  4. Replies
    3
    Views
    99

    Re: Odd Pivot Table Behavior

    You get Count Of rather than Sum Of if there are ANY blank cells or text values in the column. Excel the treats the whole column as a text field, so you are seeing Field Settings rather than Value...
  5. Re: one pivot table from two different excel table

    Consolidate the sales data into a single table using Power Query or Data, Consolidate. Then start a Pivot Table based on this, and Add this data to the Data Model when you are creating the Pivot...
  6. Replies
    12
    Views
    202

    Re: Using a Named table in Data Validation

    Glad you managed to sort it out, Gary, and thanks for letting us know!
  7. Replies
    9
    Views
    189

    Re: Counting events

    Lovin' the signature, #Gerald , and totally agree! Pleased to say I knew what it would do before I ran it.
  8. Replies
    9
    Views
    189

    Re: Counting events

    Wow Gerald, that maths is way out of my league! but sticking with my formula, counting events in a table, try this:
    ...
  9. Replies
    9
    Views
    189

    Re: Counting events

    I will give it some thought, but it'll have to wait till the morning now!
  10. Replies
    9
    Views
    189

    Re: Counting events

    If you can make a column of the times for your events, you could use the formula shown here:

    <b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px...
  11. Replies
    7
    Views
    244

    Re: Conditional formatting

    Put a $ before the G in your conditional format formula.
  12. Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    Thanks, glad I was able to help. :)
  13. Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    If the formula only returns the #NA error value then =IFERROR(
    INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0)),"") returns the value, or a null text string "" if the result is an error....
  14. Re: Data Validation List With Offset Formula Allows Free Text Entry

    I am intrigued - as I use this forum, I see many uses of the OFFSET function to produce dynamic lists, as presumably you are doing here. But why not just use Tables? You cannot directly use a Table...
  15. Re: Conditional Formatting: everything but "X", "Y", or "Z"

    Me too, I am in awe of so many of the contributors on this board, and I find it a great learning resource!
  16. Replies
    12
    Views
    202

    Re: Using a Named table in Data Validation

    I always use Tables as sources for Data Validation - the advantages of Tables far outweigh the faff required to create the Data Validation.

    The solution is simple - just apply a traditional range...
  17. Re: Conditional Formatting: everything but "X", "Y", or "Z"

    I agree with Shane. I would make a Data Validation List - then you could put all valid values into the list and limit entries to those. Then you don't need to use very complex - or rather general -...
  18. Re: IF due date cell is < today = "" unless completed cell contains a date

    Good call! I put it in because I was editing from the formula in #13 which used different logic. Not necessary in mine. :rolleyes:

    Also, weird how the quote changed the formula, adding a spurious...
  19. Re: Conditional Formatting: everything but "X", "Y", or "Z"

    Yes it will: if you add Data Validation to existing data, you can Circle Invalid Data to highlight records that do not meet the condition.
  20. Re: IF due date cell is < today = "" unless completed cell contains a date

    You're very welcome - happy to help.
    :cool:
  21. Re: IF due date cell is < today = "" unless completed cell contains a date

    Here you go :)

    <b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col...
  22. Replies
    10
    Views
    191

    Re: Conditional Formatting Not Working

    Happy to help :)
  23. Replies
    10
    Views
    191

    Re: Conditional Formatting Not Working

    That could be the problem. Set up your conditional formatting to look for values less/greater than 0.0525. The percent format multiplies the number by 100 and sticks a % sign on the end - but the...
  24. Replies
    10
    Views
    191

    Re: Conditional Formatting Not Working

    Could you post some sample data and your formula to show the problem, also your Conditional Format settings.
  25. Re: Up and Down arrow (and =) indicator when the value is more or less than the previous cell

    Not that I can see: you cannot use cell references in icon set conditional formats. I would put it in the next column, base the formatting on a formula that subtracts the number from the one above,...
Results 1 to 25 of 116
Page 1 of 5 1 2 3 4