openoffice-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brian Barker <b.m.bar...@btinternet.com>
Subject Re: Indirect and Address problem (from VLOOKUP and Macros)
Date Thu, 03 Oct 2013 16:36:38 GMT
At 08:47 03/10/2013 -0600, John Meyer wrote:
>http://i174.photobucket.com/albums/w108/pueblonative/FormulaError2_zps27abcf42.png
>http://i174.photobucket.com/albums/w108/pueblonative/Formulaerror1_zpsda33a4c4.png
>Here are the worksheet names and the formula I am using.

I haven't been following this thread, so take this with a pinch of 
salt, but I think I can see the problems here.

Your source value in cell C1 of sheet Bonuses may look like 
"09-27-2013" but it is actually a date value formatted to look like 
that.  I can see this from its right alignment (unless you have set 
this cell formatting manually).  Your INDIRECT(ADDRESS... will 
retrieve this value, but not with the date formatting applied.  I'm 
guessing, but I think the most obvious result would be the underlying 
date value (possibly 41544), the numbers of days from the date 
origin.  Now your sheet is actually named "09-27-2013" - as text - 
and there is no sheet named "41544".  Hence the error.

You could enter the date in C1 as text.  Type an apostrophe before 
the value and it will be interpreted as text (and left aligned by 
default).  The result of your INDIRECT(ADDRESS... will now be the 
same text string and this will match the sheet name.

But that's not the whole story.  The result of the INDIRECT function 
is a text string representing the sheet name, but you cannot just 
append ".$E$1 ..." to this.  Instead you need to concatenate these 
text strings as
INDIRECT(ADDRESS(1;3;1;;"Bonuses"))&".$E$1 ..."
but then you have another text string and you need to use INDIRECT() 
again to convert it to a reference.  Try:
=COUNTIF(INDIRECT(INDIRECT(ADDRESS(1;3;1;;"Bonuses"))&".$E$1:$E$2000");A2)

If you wanted to retain the values in C1 and so on as genuine dates, 
you may be able to convert the date value to the appropriate text 
explicitly using TEXT(...;"MM-DD-YYYY") within your formula.  But I 
can't get this to work; I think the problem is that sheet names that 
are numerical or perhaps start with a number need in this context to 
be surrounded by quotes - and it's difficult to see how you could add these.

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org


Mime
View raw message