Posts by FDibbins

    Seems to me (as I have already commented on another site with pretty much the same post from the OP) that OP will be sending out spam to private cell phone numbers.


    As far as Im concerned, I just feel that people (myself included) get enough unwanted and unasked for spam and other advertising crap on their phones, that it just would not sit right to assist in it's proliferation

    Re: Excel not linking external files


    Not really sure what the problem might be, I constructed this with no problems, both with the source open, and closed...
    =VLOOKUP(B1,'H:\\path1\[name-or-file-that-I-used.xlsm]Formulas 1'!$A$1:$C$22,2,0)

    Re: Increment number within variable length alphanumeric string


    This turned into a beast because of the variable nature of your dats...
    [Table="width:, class:grid"]

    [tr][td]

    [/td][td]

    A

    [/td][td]

    B

    [/td][td]

    C

    [/td][td]

    D

    [/td][/tr]


    [tr][td]

    1

    [/td][td]

    T247-e

    [/td][td]

    T248-e

    [/td][td]

    T249-e

    [/td][td]

    T250-e

    [/td][/tr]


    [tr][td]

    2

    [/td][td]

    Transect6

    [/td][td]

    Transect7

    [/td][td]

    Transect8

    [/td][td]

    Transect9

    [/td][/tr]


    [tr][td]

    3

    [/td][td]

    17

    [/td][td]

    18

    [/td][td]

    19

    [/td][td]

    20

    [/td][/tr]


    [tr][td]

    4

    [/td][td]

    Tran24w_b

    [/td][td]

    Tran25w_b

    [/td][td]

    Tran26w_b

    [/td][td]

    Tran27w_b

    [/td][/tr]


    [tr][td]

    5

    [/td][td]

    19(b)

    [/td][td]

    20(b)

    [/td][td]

    21(b)

    [/td][td]

    22(b)

    [/td][/tr]


    [tr][td]

    6

    [/td][td]

    Transect 6221

    [/td][td]

    Transect 6222

    [/td][td]

    Transect 6223

    [/td][td]

    Transect 6224

    [/td][/tr]


    [/table]


    Quote

    b1=LEFT(A1,FIND(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))),A1)-1)&LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))+1&MID(A1,FIND(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))),A1)+LEN(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))),99)


    copied down and across


    However, this can be shortened considerably if you are willing to use a helper column to ID the number (you can hide this if needed)...
    [Table="width:, class:grid"]

    [tr][td]

    [/td][td]

    A

    [/td][td]

    B

    [/td][td]

    C

    [/td][/tr]


    [tr][td]

    10

    [/td][td]

    T247-e

    [/td][td]

    247

    [/td][td]

    T248-e

    [/td][/tr]


    [tr][td]

    11

    [/td][td]

    Transect6

    [/td][td]

    6

    [/td][td]

    Transect7

    [/td][/tr]


    [tr][td]

    12

    [/td][td]

    17

    [/td][td]

    17

    [/td][td]

    18

    [/td][/tr]


    [tr][td]

    13

    [/td][td]

    Tran24w_b

    [/td][td]

    24

    [/td][td]

    Tran25w_b

    [/td][/tr]


    [tr][td]

    14

    [/td][td]

    19(b)

    [/td][td]

    19

    [/td][td]

    20(b)

    [/td][/tr]


    [tr][td]

    15

    [/td][td]

    Transect 6221

    [/td][td]

    6221

    [/td][td]

    Transect 6222

    [/td][/tr]


    [/table]


    B10=LOOKUP(99^99,--("0"&MID(A10,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A10&"0123456789")),ROW($1:$10000))))
    C10=LEFT($A10,FIND($B10,$A10)-1)&$B10+COLUMN(A1)&MID($A10,FIND($B10,$A10)+LEN($B10),99)
    both copied down AND ACROSS as needed

    Re: show only B6 drop down menu Invoice no. only


    Im not really sure what you are asking here, could you provide some sample answers in your file?


    To get Inv No in B7, I *think* you want this...
    =INDEX('Purchase Accounts'!$D:$D,MATCH(Payments!$B$6,'Purchase Accounts'!$E:$E,0))

    Re: SUM not working in certain cells only


    Quote

    R10 is pulling through £12,531 and R11 £112,137


    If that is exactly what shows in the formula bar (not just the cell), then that is text, not a number - excel does not include currency symbols in the formula bar for real numbers.


    If that is the case, then you need to track back and find out why the £ is being included in the cell

    Re: Formatting


    Quote

    I'm getting 01/01/1900 18:05:46


    That probably means that the cell is Custom formatted as DATE/TIME. Try formatting just as TIME

    Re: If statement


    Sorry, I did not understand a word you just said there, plus your file really does not contain much info or data :(


    What you need to do, is...
    - give some sample data
    - provide some sample answers
    - if necessary, explain how you got those answers.


    Keep in mind, you know exactly what you have and what you want, while we have no clue :)

    Re: Trying to create formula to exclude any cells that contain the word "D/C"


    Try this...
    [TABLE="class: grid"]

    [tr]


    [td]

    [/td]


    [td]


    L


    [/td]


    [td]


    M


    [/td]


    [td]


    N


    [/td]


    [/tr]


    [tr]


    [td]


    2


    [/td]


    [td]

    D/C aaa

    [/td]


    [td]

    [/td]


    [td]


    2


    [/td]


    [/tr]


    [tr]


    [td]


    3


    [/td]


    [td]

    bbb

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    4


    [/td]


    [td]

    D/C ccc

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    5


    [/td]


    [td]

    ddd

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    6


    [/td]


    [td]

    D/C eee

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [/TABLE]


    N2=COUNTIFS(L:L,"<>D/C*",L:L,"<>")


    @ skywriter, it is not working because all the empty cells are also <> "DC*"