OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Using colons in cells causing me grief.

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
jgreenlee
Newbie
Newbie


Joined: 15 Jun 2006
Posts: 3

PostPosted: Thu Jun 15, 2006 10:28 am    Post subject: Using colons in cells causing me grief. Reply with quote

I'm using VLOOKUP in a spreadsheet to take a MAC Address and lookup the associated host which lives in another worksheet. Unfortunately the results of my queries are pretty sporadic. For example...

10:00:00:00:C9:32:AA:BB

Is my reference MAC. I have the following formula that performs the lookup...

=IF($N23="";"";VLOOKUP($N23;systems_macs.$E$2:systems_macs.$F$100;2))

Instead of returning the proper value for that MAC address it returns the value for a completely different MAC address.

For giggles I removed all the colons from the reference MAC address and the target MAC address in my data worksheet which returned the proper system.

An interesting observation is that adding the first colon back (10:00...) returns a different MAC but still not the right one. (why it returns ANY result is beyond me because now the reference doesn't match ANY field in the lookup table.)

I formatted the cells in both columns of MACs as "text".

Why isn't the lookup working properly?

Thanks,

James


Last edited by jgreenlee on Thu Jun 15, 2006 10:54 am; edited 1 time in total
Back to top
View user's profile Send private message
jgreenlee
Newbie
Newbie


Joined: 15 Jun 2006
Posts: 3

PostPosted: Thu Jun 15, 2006 10:41 am    Post subject: Reply with quote

Okay...It appears that colons are a special opperand that causes Calc to do some "figgerin."

Using dots in place of the colons seems to fix my issue...But I would like to see my MAC's with colons instead. How can I get around this?

James
Back to top
View user's profile Send private message
ms777
Super User
Super User


Joined: 07 Feb 2004
Posts: 1355

PostPosted: Thu Jun 15, 2006 11:28 am    Post subject: Reply with quote

Hi,

if I remember correctly, VLOOKUP expects 4 parameter, not 3, and the last one must be 'false' for exact matches ... check the OO Basic help

Good luck,

ms777
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Jun 15, 2006 11:30 am    Post subject: Reply with quote

The colons are not the problem, but the missing 4th argument of VLOOKUP.
Have a look at this: http://www.oooforum.org/forum/viewtopic.phtml?t=38012
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
jgreenlee
Newbie
Newbie


Joined: 15 Jun 2006
Posts: 3

PostPosted: Thu Jun 15, 2006 11:53 am    Post subject: Reply with quote

Much thanks guys...I never saw mention of the "fourth" parameter of VLOOKUP in the OOo tutorial.

Things usually work when you use them right. Smile

Thanks!

James
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group