| View previous topic :: View next topic |
| Author |
Message |
lacole
General User
Joined: 22 Mar 2010
Posts: 26
Location: Mississauga, Ontario, Canada
|
 Posted: Wed Mar 21, 2012 9:12 am Post subject: Why is this formula giving the wrong answer? |
 |
|
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 |
|
 |
range
General User
Joined: 04 Jan 2012
Posts: 21
|
| Posted: Wed Mar 21, 2012 11:08 am Post subject: |
|
|
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 |
|
|
gerard24
OOo Enthusiast
Joined: 08 Jul 2011
Posts: 100
Location: France
|
| Posted: Wed Mar 21, 2012 1:13 pm Post subject: |
|
|
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 |
|
|
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| Posted: Wed Mar 21, 2012 11:03 pm Post subject: |
|
|
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 |
|
|
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| Posted: Wed Mar 21, 2012 11:08 pm Post subject: |
|
|
| 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 |
|
|
lacole
General User
Joined: 22 Mar 2010
Posts: 26
Location: Mississauga, Ontario, Canada
|
| Posted: Fri Mar 23, 2012 9:11 am Post subject: Solved - why is this formula giving the wrong answer? |
|
|
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 |
|
|
|
