G'day there One & All,
I'm trying to develop a sheet to assist with record keeping of hours worked & amounts paid. Basically my paychequeue records. I've got most of it working, but there is one part that has me stumped. I've tried cursing & swearing, and even stamped my feet but to no avail. I hope someone here can see where I've gone wrong, as even Mr Google has let me down on this occasion. I'm sure it something both simple & obvious but that I simply can't see. Here goes:
I have a number of columns formatted as a yearly calendar. I can't even claim credit, as I modified one I found online.
On the same worksheet I have dates entered in "3/10/2017" format. I use =DAY(A3) to extract the day. First date entry is in A3. This works fine. I also use =MATCH(CHOOSE(MONTH(A3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"),M2:AJ2,0)+12 to extract the search column from the Month labels across the top. This too works fine. This formula is in K5, the +12 at the end is an offset.
The next part is to use the formula "=MATCH(DAY(A3), ADDRESS(3,K5-1) & ":" & ADDRESS(49,K5-1),0)" to extract the correct row that matches the selected date. The 3 for the ADDRESS row is the top row of the calendar under the labels and 49 is the bottom ADDRESS row. "Week" rows are blank.
Parts of this final MATCH function must be working as it evaluates to "MATCH(3,$AE$3:$AE$49,0)" before the next step gives a #VALUE! error. The formula appears correct to here so I assume it's something to do with data types but I can't quite figure it.
Can anyone please point me in the right direction? Or see anything obvious for me to consider?
Thanks for reading,