I assume Mmm in the formula refers to the 3 letter month designation,
such as Jan, Feb, and I assume the D$1 is what forces INDIRECT to
choose Jan rather than Feb.

Right. It takes the "month" component of the date in row 1, expresses it
as the three-character name and uses those the characters to identify the
tab.

If number of shares 0 and the stock isn't in the Jan tab, I'd expect
#N/A, not #REF.


So, what exactly do you have in Share Price D1 and how is row 1
formatted in the Share Price, Num Shares, and Value tabs?

I have formulas to compute the last day of each month, and format those
cells as dates:
Format Cells Number Date

My formulas are more elaborate than necessary, something like this:
A1: 2007 [current year as a constant number]
D1: =DATE($A$1,2,0) [last day of January; i.e., zeroth day of February]
E1: =DATE($A$1,MONTH(D1)+2,0) [last day of February]
Copy E1; select F1:O1; and paste.
I do this on one of the three sheets; on the others, row 1 refers to that
one for end-of-month dates.


If there's still a problem, try building up the D4 formula one part at a
time and see where things start to go wrong.

=TEXT(D$1, "Mmm") [should be Jan]
=TEXT(D$1, "Mmm")&"!A:C" [should be Jan!A:C]
='Num Shares'!D4 [should be the number of shares]
='Num Shares'!D40 [should be TRUE]
=N('Num Shares'!D4)0 [should be TRUE]
=VLOOKUP($A4,INDIRECT(TEXT(D$1, "Mmm")&"!A:C"),3,FALSE) [should be the
share price]

Combine these two lines for the complete formula:
=IF(N('Num Shares'!D4)0,
VLOOKUP($A4,INDIRECT(TEXT(D$1, "Mmm")&"!A:C"),3,FALSE), "")