hep with this code

  • =MID(CELL("filename",A25),SEARCH("[",CELL("filename",A25))+1,SEARCH(".",CELL("filename",A25))-1-SEARCH("[",CELL("filename",A25))) & "-" & RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1),1)) & "-" & TEXT($A25,"dd") & "-" & TEXT(ROW($C25)-1,"000")

    this code allow me to write the row data in column C with this format:
    file name + sheet name + column A + ###
    it works mostly what i wanted, but there are 2 things i love to get better and need helps from you all.

    lets say I drag this code from C2-C20

    1. I have fill in on A1 & B1, and i need extra 4 rows to write details for A1 & B1 row,
    and I start a new row from A6, (so i have no data in A2, A3, A4, A5, nor B2, B3, B4, B5)
    I like to have C1 last 3 digital ### as 001 and C6 last 3 digital ### as 002
    (right now it will be 005)

    2. Similar concept when I delete data C13-C18, with column A1, A6-A20 fill-in with data,
    (i did not delete the whole row, still have A13-A20 data filled-in as it is),
    in C19, the last 3 digit ### will be 018, but I like it to be 009 (the reason it should be 009 is that C6 should be 002,
    C7---003, C8---004, C9---005, C10---006, C11---007, C12---008, C13-C18 deleted, so it is blank, and C19---009

    can any one help me to change the original code and make it works the way I wish it to be?
    (Ps. if possible it will be great just stick with formula without using VBA, thanks!)

    extra thing:

    is there anyway to also put change row font colour in certain places? for example i like to have A2 as black and then A3-A5 as white (kind of to hide it with font colour), and A6 as black colour, any fomular i can use to make it black or white?

    So you can say that if A2, A3, A4, A5, is the same data (eg, New York), I like to keep first data A2 as black and show New York, and the rest as white; and A6, A7, A8, A9, till A20 is same data (eg, LA), I like to keep A6 as black and show LA, and A7-A20 is white.