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

Variable Row Numbers When Dragging HLOOKUP

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


Joined: 18 Apr 2006
Posts: 13

PostPosted: Thu May 11, 2006 2:27 am    Post subject: Variable Row Numbers When Dragging HLOOKUP Reply with quote

=IF(ISNA(HLOOKUP($C$2;$PRICE.$C$1:$O$36;2;0));"NOT STANDARD";HLOOKUP($C$2;$PRICE.$C$1:$O$36;2;0))

How do I get the row number, '2' in this case, to change to 3, 4, 5...... etc when I copy the formula into multiple cells of a column?

I know I can go through and manually change them but that is just asking for human error plus I'm lazy.
Back to top
View user's profile Send private message
french
OOo Enthusiast
OOo Enthusiast


Joined: 29 Nov 2004
Posts: 129
Location: Iowa, USA

PostPosted: Thu May 11, 2006 4:57 am    Post subject: Reply with quote

Delete the dollar sign ($) from in front of the row number in the formula.
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Thu May 11, 2006 7:22 am    Post subject: Reply with quote

I have usually had to put a column of 2, 3, 4, etc next to where the LOOKUP function is going, then in the LOOKUP refer (as a relative address) to the cell with the 2 in it instead of putting 2 in. Then fill down.

I don't know of a way to fully automate it.
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 May 11, 2006 8:46 am    Post subject: Reply with quote

=IF(ISNA(HLOOKUP($C$2;$PRICE.$C$1:$O$36;ROW($IV2);0));"NOT STANDARD";HLOOKUP($C$2;$PRICE.$C$1:$O$36;ROW($IV2);0))
I use column IV, hoping that it is not involved (avoiding circular reference). You may use $OtherSheet.$A2 as well.
Back to top
View user's profile Send private message
fargone
General User
General User


Joined: 18 Apr 2006
Posts: 13

PostPosted: Thu May 11, 2006 8:16 pm    Post subject: Reply with quote

Thanks everyone. Once again the support for Ooo is super.

Substituting ROW($IV2) worked like a charm but I'm kind of fuzzy on why it works?

What does the $IV mean? When I am helping my daughter with her school work and she asks me why I did this I don't want to say "Do not question the Mighty Villeroy. Just do what he says."
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 12, 2006 1:09 am    Post subject: Reply with quote

I replaced the bold 2 in your initial posting with something that returnes 2 in the first row and consecutive row-numbers in case it's copied down wards.
See help on row. The missing $ does exactly the same as french suggested for your $C$2 -> $C2. When you copy up or down a formula, having relative row-references (missing $ before row-numbers) the row-numbers adjust to their new position. Same with columns references, missing $ and copy direction left or right.
Simply use some free cell-range and copy around some formulas ROW(), COLUMN(), ROW(X123), COLUMN($X123),ROW($X$123),...
Back to top
View user's profile Send private message
fargone
General User
General User


Joined: 18 Apr 2006
Posts: 13

PostPosted: Fri May 12, 2006 2:18 am    Post subject: ROW FUNCTION Reply with quote

I did play around with =ROW($IV2) and now see that the $IV can be $ANY_COLUMN_LETTER as long as it does not refer to a column that is actually in use in the spreadsheet.

Now I have to go back and rework all my previous spreadsheet templates and make them function more efficiently. Made myself more work. A little knowledge IS dangerous.

Thanks again everyone.
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