=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!)
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.