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

AutoAdjust Formula for Incremental Row from referenced Sheet

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


Joined: 17 Nov 2010
Posts: 2

PostPosted: Wed Nov 17, 2010 8:05 am    Post subject: AutoAdjust Formula for Incremental Row from referenced Sheet Reply with quote

So I probably need to explain that better, wasn't even sure how to term it. I am trying to create a helper spreadsheet to migrate some data from a poorly programmed and old database with no export feature into csv that can be imported into the new database.

Currently I have the Export Worksheet which is pulling data from Import2 worksheet.

In Cell B2 i have this formula =Import2.C15 in Cell B3 I would like this =Import2.C33 and similar increases on down. How can I get a forumla to auto-adjust.

Similarly I need an increment in another field that has an IF Statement
H2 is =IF(Import2.J17="Online";"Active";"Canceled")
H3 would be =IF(Import2.J35="Online";"Active";"Canceled")
and so on.

I have been looking into the various functions and believe it would be using the OFFSET function. Am I overlooking a simple solution? Any help is greatly appreciated.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed Nov 17, 2010 2:29 pm    Post subject: Reply with quote

Try...
Code:
=INDEX(Import2.$C$1:$C$65536;18*(ROW($B1)-1)+15)
in B2 filled down as far as required and
Code:
=IF(INDEX(Import2.$J$1:$J$65536;18*(ROW($H1)-1)+17)="Online";"Active";"Cancelled")
in H2 filled down as far as required.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
XLPat
Newbie
Newbie


Joined: 17 Nov 2010
Posts: 2

PostPosted: Wed Nov 17, 2010 2:52 pm    Post subject: Reply with quote

Excellent. It looks like that's going to do the trick. Appreciate it greatly.
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