Difficult problem. Could try to do in VBA but a worksheet solution might be
easier. Can you create a calculated column for your worksheets? If so, I
would make a column I would call "Last workday" or something like that. You
need to track what is the last workday's value for what is in column A so you
could use a formula like this one (written arbitrarily for the worksheet
"APRIL" cell D20):
=IF(ROW()=1,MARCH!$D$31,IF(OFFSET(A20,-1,0)="",OFFSET(D20,-1,0),OFFSET(A20,-1,0)))
(explanation: if on row 1, will pick up the last workday value from the
prior month's sheet (row 31); if the value in A for "yesterday" is blank,
then it will look at the prior D value and copy this as the "last workday"
value, carrying it over on the weekend/holidays; if yesterday's A is not
blank then just use that. The ROW and OFFSET formulas are in there so the
same formula works for every cell in column D; this also means you can use it
for February and the months with 30 days to fill in the remaining rows of the
sheet carrying over the value so D31 always contains the last workday's value
from the month.

Hope that all makes sense, but I have tested it and it seems to handle the
situation. Use your D column value wherever you need the "last workday"
value from A. So, for example, the formula you gave becomes:
C2 = A2 - B2 + D2
--
- K Dales


"jimtmcdaniels" wrote:

Help, I need a formula for work that does the following,

In a 12 sheet excel workbook, each month of the year is represented in a
generic 31 row sheet. No data is entered in the rows representing weekends
or holidays our office is closed.

It's simple math concerning 3 cells, but the tricky part is one of the 3
cells the formula must use is in the previous working day's row. The problem
is I don't know how to tell the formula to look at the previous working day's
cell since on Monday's that would be 3 rows above and on the beginning of the
month, that would be the last cell-row with data in it on the previous
month's sheet, and then there's holidays too where rows are skipped-blank.

I.E. (formula is cell C2 =A2 -B2 +A1) In this example, Row 2 would
represent the 2nd of May. This formula works only if May 1st was a workday -
has data in it (cell A1). However if the 1st does not have data, because it
was a holiday, or weekend, then I need a smarter formula that knows to go to
the last business day's row, which in this case would be on a different sheet
in the workbook.

Thank-you for any help!