MyVeryOwnSelf:


Thanks to you, I have this thing working fairly well now.

I put in some mock entries in the Jan tab to get the formula working.

However, my download source for live data insists on putting the ticker
symbol in Column B of the Jan tab, rather than Column A, which of course
generates an error back on the Share Price tab.

The Excel help file is a bit cryptic. Is it correct that the formula cannot
be modified to force a search in Column B of the Jan tab and therefore find
the ticker symbol? Hiding Column A is no help either.

Even if that is true, I am guessing I can find another download source that
will drop the tickers in column A. Mebbe I should look at winstock as you
mentioned.

Lastly, I see that the =TEXT(D$1, "Mmm")&"!A:C" portion of the formula does
in fact generate Jan!A:C, but I don't understand why you need it. The
overall formula seems to generate errors if that portion is omitted.





"MyVeryOwnSelf" wrote in message
...
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), "")