Friends,
I have an excel sheet for employee attendance. Now I am using it for monthly base. But I would like to make it for an year. I am using the following formula to extract data from a worksheet. But this formula is capable only for a month. Can you please advice me a Macro or substitute formulas which can use for the whole Year.
=CONCATENATE(IF(Sheet1!$F$7=TODAY(),Sheet1!F8,IF(Sheet1!$G$7=TODAY(),Sheet1!G8,IF(Sheet1!$H$7=TODAY(),Sheet1!H8,IF(Sheet1!$I$7=TODAY(),Sheet1!I8,IF(Sheet1!$J$7=TODAY(),Sheet1!J8,IF(Sheet1!$K$7=TODAY(),Sheet1!K8,IF(Sheet1!$L$7=TODAY(),Sheet1!L8,IF(Sheet1!$M$7=TODAY(),Sheet1!M8,IF(Sheet1!$N$7=TODAY(),Sheet1!N8,IF(Sheet1!$O$7=TODAY(),Sheet1!O8,IF(Sheet1!$P$7=TODAY(),Sheet1!P8,IF(Sheet1!$Q$7=TODAY(),Sheet1!Q8,IF(Sheet1!$R$7=TODAY(),Sheet1!R8,IF(Sheet1!$S$7=TODAY(),Sheet1!S8,IF(Sheet1!$T$7=TODAY(),Sheet1!T8,IF(Sheet1!$U$7=TODAY(),Sheet1!U8,IF(Sheet1!$V$7=TODAY(),Sheet1!V8,IF(Sheet1!$W$7=TODAY(),Sheet1!W8,IF(Sheet1!$X$7=TODAY(),Sheet1!X8,IF(Sheet1!$Y$7=TODAY(),Sheet1!Y8,IF(Sheet1!$Z$7=TODAY(),Sheet1!Z8,IF(Sheet1!$AA$7=TODAY(),Sheet1!AA8,IF(Sheet1!$AB$7=TODAY(),Sheet1!AB8,IF(Sheet1!$AC$7=TODAY(),Sheet1!AC8,IF(Sheet1!$AD$7=TODAY(),Sheet1!AD8,IF(Sheet1!$AE$7=TODAY(),Sheet1!AE8,IF(Sheet1!$AF$7=TODAY(),Sheet1!AF8,IF(Sheet1!$AG$7=TODAY(),Sheet1!AG8,IF(Sheet1!$AH$7=TODAY(),Sheet1!AH8,IF(Sheet1!$AI$7=TODAY(),Sheet1!AI8,IF(Sheet1!$AJ$7=TODAY(),Sheet1!AJ8))))))))))))))))))))))))))))))))
Now,So this formula will search which is TODAY's date on 31st columns based on monthly dates which has already entered in the sheet. And will show the result accordingly.
I have 500 employees and I copied this formula in to 500 cells (ie From B4 to B500). now what I need is I have to make this for an YEAR. friends kindly suggest me a solution this..please
Bookmarks