openoffice-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Meyer <johnme...@pueblocomputing.com>
Subject Re: Indirect and Address problem (from VLOOKUP and Macros)
Date Thu, 03 Oct 2013 16:59:43 GMT
Thanks for the help.

After looking over this problem a bit, I decided to scrap the sheet name 
idea and just have all sales on one sheet.

sales are in this format:

DummyField, Date, DummyField, DummyField, SalesRep


For this table, I want to get the number of sales generated for a 
particular agent by date.

Sales Rep                            10/1/13       10/2/13 10/3/13
12345                                      ## ##                ##
45678                                      ## ##                 ##


Would this lend itself to a DCOUNT solution (and to answer the obvious 
question, I can't use a database at work).





On 10/3/2013 10:36 AM, Brian Barker wrote:
> 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
>


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


Mime
View raw message