[AccessD] A couple of Excel questions

McGillivray, Don [IT] Donald.A.McGillivray at sprint.com
Fri Aug 24 09:57:07 CDT 2007


Oops,  looks like the default for RangeLookup is True.  Check the help in Excel for a clearer description of this flag's function. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of McGillivray, Don [IT]
Sent: Friday, August 24, 2007 7:39 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A couple of Excel questions

Arthur,

I've seen behavior 1 only when I've multi-selected sheets in a book.  IOW, if both sheets are selected.  Is that the case for you?  (Ctrl+Shift+PgDn(Up) will select the current sheet and its adjacent one, or hold shift while selecting tabs.)  If this is not the case, I'm afraid I'm not much help.

As for 2, is your lookup table sorted according to values in the index column?  If you set the "RangeLookup" flag to False (the default), the values in that column need to be sorted in ascending order.  If the "RangeLookup" flag is set to True, Vlookup will return an approximate match, and the values needn't be sorted.  Beware that multiple occurrences of the same value in your lookup index may cause unexpected results.

I hope this helps . . .

Don




-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Friday, August 24, 2007 7:10 AM
To: Access Developers discussion and problem solving; Discussion of Hardware and Software issues
Subject: [AccessD] A couple of Excel questions

Forgive the double-post. I wasn't sure whether this belongs on dba-Tech or
if it's ok here.


1. When I change column widths on one sheet, it seems to change them on all
sheets. Can't I affect only one sheet?
2. On one of my sheets there is a column that does a VLOOKUP() into another
sheet. Except for the relative row number of the lookup value, the formulae
are identical down the column. But two lookups fail. There doesn't seem to
be anything different about these two. In one case the lookup value is 4431
and the other 11503. I tried switching them to strings and ditto in lookup
table, to no avail. Any idea why these two, and only these two out of about
80 lookups, fail?

TIA,
Arthur
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com






More information about the AccessD mailing list