Greeting to all

First time user / member. Have used excel from MS Office's first inception. Am a former Lotus123 and Quatro Pro user from waaaaay back. In other words - I hack from a book most of the time. Need a little help and am looking forward to your responses.

spreadsheet.png

I have attached an image of the spreadsheet to which I will be referring as the workbook is HUGE and copying the worksheet has so many links that it will not function by itself.

The columns to which I will refer are:

(B) Date - (C) % Guide - (L) Daily Total

"(B) Date" is derived from a formula which calculates and then displays the day of the month based upon whatever month it is such that it only displays the actual days for that month: 28/29, 30 or 31 days. The formula is: =DATE(YEAR(NOW()),$O$6,1) in the cell for the first day of the month and a relative reference of: =IF(DAY(IF(B6="",0,B6)+1)>DAY(B$6),B6+1,"") in the rest of the cells below for an obvious maximum of 30 additional cells.

"(C) % Guide" is derived by taking the COUNT of entries in column "B" from the first day of the month down to the particular row being calculated then dividing that by the maximum number of days in the displayed month. =COUNT(B6)/O42 in the first cell and =COUNT(B6:B7)/$O$42 on down to the last cell of the month. This generates a percentage of the month used up for each day of the month. The "O" column referred to in each of the about expressions is a figure representing the maximum number of days in the particular month being displayed.

The row to which I will refer are:

(42) % Achieved

This row/column is the far right bottom cell which, in the image, reads "20.1%" This figure currently represents the percentage of the production achieved based upon the figures entered above it.

Here's what I need:

I want to place the value of the percentage of the month which has passed in the cell to the right of the lower right cell noted above - "20.1%" - for comparison purposes then I would establish a conditional format to bring attention to it in some way. I have tried to use vlookup and other functions associated to its use to return the value associated with the entry in the % Guide column based upon it being the row corresponding with the last entry in the Daily Total column

In this case, the last entry in the Daily Total column is 73.0 which is the row corresponding with the Date & % Guide columns entry of "13" and "43%" respectively.

I get #N/A or #VALUE error in all attempts.

A COUNT of the Daily Total column always returns "31", I assume because there are formulas in each cell that produce the daily totals; a COUNTIF >1 produces a correct count of the cells, however, you will notice that the Daily Total of the first day of the month is empty so guess what the count is - yep -"12".

And of course trying to use these routines with vlookup just fails.

Letting the boss just physically refer to the % Guide column and comparing that figure to the % Achieve figure is just WRONG. Excel can do better than that - it is just that "I" can't do it.

HELP ! (If you need the workbook I could send it if you can explain how to get it to you individually)

Thanks in advance for any help you all can provide.