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

Why is this formula giving the wrong answer?

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


Joined: 22 Mar 2010
Posts: 26
Location: Mississauga, Ontario, Canada

PostPosted: Wed Mar 21, 2012 9:12 am    Post subject: Why is this formula giving the wrong answer? Reply with quote

Hi,

I am using XP, OpenOffice Portable version 3.2

I would like to dynamically create a column reference based on information in cells. I need the answer in the form of a column name, ie BX. This will be plugged into an indirect forumla.

This works, but I typed in the column, I want to create that information dynamically.

=LEFT(RIGHT(ADDRESS(1; COLUMN(Ai$1)+$B$1 -1 ; 1;;$D2 );4);2)

This gives a wrong answer. I cannot identify the cause.

D1=LEFT(RIGHT(ADDRESS(1; COLUMN(OFFSET($C$1; 0; $B$1; 1))+ $B$1; 1;; $D2); 4); 2)

where:
A1 is row# = 6
B1 is number = 37
C1 is starting Column# = Ai
D2 is sheet name. = Sheet2

I want to dynamically determine column by add offset# in B1 to start column in C1.

If I plug the number 31 in B1, both formulas give the correct answer. BM
if 30, D1 returns BK, should be BL, off by -1
if 39, D1 returns CC, should be BU, off by +9
The larger the number in B1, the more the answer in D1 is off.

I've tried many versions of this formula, this at least gives an aswer instead of an error.

Please help. Thanks,
lacole
_________________
lacole
Mississauga, ON Canada
Oo 3.2.0 portable Using Windows Xp, Vista Home Premium
Oo 3.3 Using Vista Home Premium 64Bit
Back to top
View user's profile Send private message
range
General User
General User


Joined: 04 Jan 2012
Posts: 21

PostPosted: Wed Mar 21, 2012 11:08 am    Post subject: Reply with quote

Hello,

OFFSET() does not take the contents of C1 as the parameter, but the address of C1, so that you are offsetting from column C, not column AI.
Back to top
View user's profile Send private message
gerard24
OOo Enthusiast
OOo Enthusiast


Joined: 08 Jul 2011
Posts: 100
Location: France

PostPosted: Wed Mar 21, 2012 1:13 pm    Post subject: Reply with quote

Try this :

Code:
=LEFT(RIGHT(ADDRESS(1;COLUMN(INDIRECT($C$1&1))+$B$1-1;1;;$D2);4);2)

or

Code:
=LEFT(ADDRESS(1;COLUMN(INDIRECT($C$1&1))+$B$1-1;4);2)

Adress of column is not dependant of the sheet.
_________________
LibreOffice 3.5.0 on Windows Vista
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Wed Mar 21, 2012 11:03 pm    Post subject: Reply with quote

Hi
Quote:
I would like to dynamically create a column reference based on information in cells. I need the answer in the form of a column name, ie BX. This will be plugged into an indirect forumla.


You are doing the job a little bit "overcomplcating" do you ?

Try one of these Variants:

Code:
=INDEX( great_address_range ; rownumber ; columnnumber )


Code:
=INDEX(  (address_range1;addressrange2; ect ) ;  rownumber ; columnnumber; range_address_index)


or in Case of targets out of numerous different sheets:

Code:
=OFFSET( INDIRECT( D2 &".A1" ;  rownumber-1 ; columnumber-1 )



Karo
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Wed Mar 21, 2012 11:08 pm    Post subject: Reply with quote

karolus wrote:
Hi
Quote:
I would like to dynamically create a column reference based on information in cells. I need the answer in the form of a column name, ie BX. This will be plugged into an indirect forumla.


You are doing the job a little bit "overcomplicating" do you ?

Try one of these Variants:

Code:
=INDEX( great_address_range ; rownumber ; columnnumber )


Code:
=INDEX(  (address_range1;addressrange2; etc... ) ;  rownumber ; columnnumber; range_address_index)


or in Case of targets out of numerous different sheets:

Code:
=OFFSET( INDIRECT( D2 &".A1" ;  rownumber-1 ; columnumber-1 )



Karo
Back to top
View user's profile Send private message
lacole
General User
General User


Joined: 22 Mar 2010
Posts: 26
Location: Mississauga, Ontario, Canada

PostPosted: Fri Mar 23, 2012 9:11 am    Post subject: Solved - why is this formula giving the wrong answer? Reply with quote

My first attempts at doing this did not work, so I made changes, which made the formula more complicated .

The answer that gerard24 gave me work, and I am thrilled.

Thanks everyone, for your assistance.

lacole
_________________
lacole
Mississauga, ON Canada
Oo 3.2.0 portable Using Windows Xp, Vista Home Premium
Oo 3.3 Using Vista Home Premium 64Bit
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