[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
fargone
General User

Joined: 18 Apr 2006
Posts: 13

 Posted: Thu May 11, 2006 2:27 am    Post subject: Variable Row Numbers When Dragging HLOOKUP =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.
french
OOo Enthusiast

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

 Posted: Thu May 11, 2006 4:57 am    Post subject: Delete the dollar sign (\$) from in front of the row number in the formula.
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Thu May 11, 2006 7:22 am    Post subject: 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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Thu May 11, 2006 8:46 am    Post subject: =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.
fargone
General User

Joined: 18 Apr 2006
Posts: 13

 Posted: Thu May 11, 2006 8:16 pm    Post subject: 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."
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Fri May 12, 2006 1:09 am    Post subject: 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),...
fargone
General User

Joined: 18 Apr 2006
Posts: 13

 Posted: Fri May 12, 2006 2:18 am    Post subject: ROW FUNCTION 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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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