Year to date of workforce

try

=INDEX($A$2:$L$2,MATCH(99^99,$A$2:$L$2,1))

Hi Bigern87,

Welcome to the forum.

So when making a monthly report for August I need the August workforce there and next month when I make a monthly report for September I need the entry in September filled in the YTD column

For above, try beloow formula:-

Formula:

Please Login or Register  to view this content.

See attached:- YTD Workforce count.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

sorry yes my formula only shows the last entry.

the above will work though so guess you have a neat solution

although if you are makeing a report for may inseptember you will need to alter that (using a secondary cell)

to =INDEX($A$2:$L$2,,MATCH($N$2,$A$1:$L$1,0))

(i also added data validdation to get a list in cell N2 for ease)


YTD Workforce count (1).xlsx

i updated my post above wich you may be interested in.

ill explain how they all work

first

Please Login or Register  to view this content.

match looks at an range of cells and returns the matching value, however in this case (99^99) will never be matched so using the ,1 highlighted you return the value of the last row/column in the field (the last one to returns no error), index then uses this to return the corresponding value in that range.

second

Please Login or Register  to view this content.

here the highlighted text causes excell to look at todays date and return the month we are in, it then uses this in the same way as the match and index above works.


lastly

Please Login or Register  to view this content.

works exactly as you'd expect, it matches the row/column with the value in N2 (a list that i generated for ease) and indexes as already mentioned.

hope this helps. remember once a thread is solved then mark it as solve (thread tools at the top of the page) and star tap thouse that help.

---------- Post added at 05:33 PM ---------- Previous post was at 05:29 PM ----------

also your one

Please Login or Register  to view this content.

again similar thing COUNT(A2:L2) counts how many cells have numbers in then uses that for the index.

I feel this is a bit wrong though as if a cell was blank this would not return the correct column. (test this by leaving may blank and your aughust report will show the value for july)

Hi Bigern87,

MMMM in match is matching the month after expanding it to complete month name.. for example Text(Date(2012,01,01),"MMMM") will output the month as "January" and this logic is further used to match the month in your headings.. Hope this helps. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>