trouble converting a 123 funtion into excel

  • Thread starter Thread starter Dlindy
  • Start date Start date
D

Dlindy

Hi I'm in a bit of a jam here. I received a lotus 123
sheet that contians a function @coord. Basically, it is
reading a formula result from one cell and point to
another cell. How do I do it in excel. Because there is
no way that 123 is better at this?
 
I don't know the @coord function, but I can narrow your search, it sounds
like it could be ADDRESS, INDIRECT, or one of the LOOKUP functions.
Investigate help for these.
 
Dlindy said:
Hi I'm in a bit of a jam here. I received a lotus 123
sheet that contians a function @coord. Basically, it is
reading a formula result from one cell and point to
another cell. How do I do it in excel. Because there is
no way that 123 is better at this?
Click to expand...

The 123 syntax is

@COORD(WORKSHEET,COLUMN,ROW,ABSOLUTE)

and it functions similarly to Excel's ADDRESS. The bad news is that there's
nothing built into Excel that will give you anything useful for the
WORKSHEET argument. If the WORKSHEET argument corresponded to the worksheet
containing the formula calling @COORD, then you could translate @COORD calls
as

ADDRESS(ROW,COLUMN,MOD(ABSOLUTE,4),,
MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1)+1,32))

The MID bit gives the worksheet name for the calling worksheet. Getting
names of other worksheets requires VBA or hardcoded ordered lists of
worksheet names.
 
WORKSHEET argument. If the WORKSHEET argument corresponded to the worksheet
containing the formula calling @COORD, then you could translate @COORD calls
as

ADDRESS(ROW,COLUMN,MOD(ABSOLUTE,4),,
MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1)+1,32))

The MID bit gives the worksheet name for the calling worksheet. Getting
names of other worksheets requires VBA or hardcoded ordered lists of
worksheet names.
Click to expand...

Not sure I follow. It is possible to enter, in a worksheet of Book3,
the formula =CELL("filename",[Book4.xls]Sheet1!$C$2) to get the name of
that workbook and the sheet in it. Of course, Book4 must be open to
correctly refresh the content of the cell in Book3.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Dlindy said:
Hi I'm in a bit of a jam here. I received a lotus 123
sheet that contians a function @coord. Basically, it is
reading a formula result from one cell and point to
another cell. How do I do it in excel. Because there is
no way that 123 is better at this?
Click to expand...

The 123 syntax is

@COORD(WORKSHEET,COLUMN,ROW,ABSOLUTE)

and it functions similarly to Excel's ADDRESS. The bad news is that there's
nothing built into Excel that will give you anything useful for the
WORKSHEET argument. If the WORKSHEET argument corresponded to the worksheet
containing the formula calling @COORD, then you could translate @COORD calls
as

ADDRESS(ROW,COLUMN,MOD(ABSOLUTE,4),,
MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1)+1,32))

The MID bit gives the worksheet name for the calling worksheet. Getting
names of other worksheets requires VBA or hardcoded ordered lists of
worksheet names.
Click to expand...
 
Tushar Mehta said:
Not sure I follow. It is possible to enter, in a worksheet of Book3,
the formula =CELL("filename",[Book4.xls]Sheet1!$C$2) to get the name of
that workbook and the sheet in it. Of course, Book4 must be open to
correctly refresh the content of the cell in Book3.
Click to expand...
....

123's @COORD function takes a worksheet index number as it's first argument.
It only returns worksheets in the *CURRENT* workbook, and you don't have to
know the worksheet's name - just it's position in the workbook. As the OP is
trying to translate from 123, Excel's ability to include workbook name as
well is superfluous to the task.

However, Excel's ADDRESS doesn't take a worksheet index argument. In Excel
it's possible, as I mentioned, to pull the *current* worksheet (the one
containing the cell formula that would have called @COORD in 123), but
there's no general means for addressing other worksheets in Excel without
either resorting to VBA or using an ordered list of worksheet names.

For example, a 123 workbook may have 3 worksheets named A, B and C. The
function call @COORD(2,5,7,8) returns B:E7. If that were the entire formula
in cell C:X99, how would you write a formula in Excel that returns B!E7? The
obvious answer would be ADDRESS(7,5,4,,"B"). HOWEVER, in 123 it's only
necessary to know that the worksheet being references is the 2nd one. In
Excel it's necessary to know its name.

If you mean you could write

ADDRESS(7,5,4,,MID(CELL("Filename",B!A1),
FIND("]",CELL("Filename",B!A1))+1,32))

then you're correct, but it may defeat the purpose for using @COORD in the
first place in the 123 workbook. Also, if cell B!A1 (or whatever) were *cut*
and pasted elsewhere, the Excel formula would be fubar. If you try to
neutralize that by using INDIRECT("B!A1") in place of B!A1, then if you
rename worksheet B, the Excel formula would be fubar.

At a conceptual level, the 123 @COORD function means 'generate a 3D cell
address NO MATTER WHAT the given worksheet is named'. There's nothing
comparable in Excel without using VBA, specifically

Application.Caller.Parent.Parent.Worksheets(n).Name

where n is the worksheet index in question.

Duplicating any aspect of 123's 3D functionality is difficult and/or tricky
in Excel, generally requiring VBA to achieve the same level of robustness as
123 would provide.
 
Back
Top