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

Author Message
dandefed
Newbie

Joined: 14 Aug 2005
Posts: 3

 Posted: Sun Aug 14, 2005 8:51 pm    Post subject: Function for revealing current cells address Here's the issue, I want this formula to work: =OFFSET(XXX;-1;0) * 50 Problem is that where I have XXX I want a function that will give the OFFSET function the cell address of the cell the formula is pasted into. I've looked through the help file and this forums without finding the answer. Perhaps I'm being stupid or I should just sleep more Thanks for any help!
8daysaweek.co.uk
Super User

Joined: 29 Nov 2003
Posts: 2130
Location: UK

 Posted: Mon Aug 15, 2005 1:23 am    Post subject: Hi dandefed, This doesn't feel right, but I think it does what you want : =RIGHT(LEFT(CELL("coord";A1);5);1)&RIGHT(CELL("coord";A1);1) I can't find a function that simply returns the current cell's address. I'm not sure one exists, but I'll be happy if someone points it out The above is a workaround HTH,_________________James www.8daysaweek.co.uk - A User-Focused OOo site
ms777
Super User

Joined: 07 Feb 2004
Posts: 1355

Posted: Mon Aug 15, 2005 3:04 am    Post subject:

How about something like (assuming C5 is the current cell)
 Code: =OFFSET(\$A\$1;COLUMN(C5)-1;ROW(C5);1;1)
dandefed
Newbie

Joined: 14 Aug 2005
Posts: 3

Posted: Mon Aug 15, 2005 3:10 am    Post subject: Thanks, but didn't seem to do much

 Quote: This doesn't feel right, but I think it does what you want Shocked: =RIGHT(LEFT(CELL("coord";A1);5);1)&RIGHT(CELL("coord";A1);1)

Thanks for the attempt but all you formula seemed to do was return the result 'A1'. It didn't matter what cell I put it in.

-- CORRECTION--

Just saw you last post, it seems to work. I didn't realise you needed to past it into A1 then copy it. I will keep experimenting. Thanks for the assistance.

You'd think there should be a function to return the cell you're in

Last edited by dandefed on Mon Aug 15, 2005 3:15 am; edited 2 times in total
8daysaweek.co.uk
Super User

Joined: 29 Nov 2003
Posts: 2130
Location: UK

 Posted: Mon Aug 15, 2005 3:11 am    Post subject: Hi dandefed, Try this: Enter it into cell A1 then copy that cell and paste it into the cell where you want it to be. BFN ,_________________James www.8daysaweek.co.uk - A User-Focused OOo site
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Mon Aug 15, 2005 3:20 am    Post subject: Hi, OFFSET does not want an address-string as it's first param. It wants a reference. You can't use =OFFSET("A2";1;2;3;4) The function which takes an address-string and returns a reference is INDIRECT. Try =OFFSET(Indirect("A2");1;2;3;4) or if A2 contains a cell address: =OFFSET(Indirect(A2);1;2;3;4) Unfortunately INDIRECT does not work with named ranges
8daysaweek.co.uk
Super User

Joined: 29 Nov 2003
Posts: 2130
Location: UK

Posted: Mon Aug 15, 2005 3:53 am    Post subject: Re: Thanks, but didn't seem to do much

 dandefed wrote: Just saw you last post, it seems to work. I didn't realise you needed to past it into A1 then copy it. I will keep experimenting. Thanks for the assistance. You'd think there should be a function to return the cell you're in

I realized after I'd posted that it won't work if the formula is pasted into a cell with double-character references i.e. A10 or AA1. There should be a workaround for this too, unfortunately I don't have time to do it at the moment, but I'll look at it when I do if no other solution has presented itself.

BFN ,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
dandefed
Newbie

Joined: 14 Aug 2005
Posts: 3

Posted: Mon Aug 15, 2005 4:25 am    Post subject: Still trying

I tried the solutions but they don't work for my purposes.

The annoying thing is that I remembered that I've done this before.

The purpose of the formula is that you have a column of units (in this case the number of notes in a particular cash denomination in a drop safe envelope). What I want is a column next to it that is the number of units times the denomination ie 11 notes of \$50 = \$550. What I was trying to get the spread sheet to do is use this:

=OFFSET(XXX;-1;0) * 50

What I want it to do is sit in the column to the right of the cash unit column with the total value of the notes (the 11x\$50 calucluation). Now instead of having to manually set every cell with a different formula I wanted to be able to paste the same formula into each one.

I hope I am being clear.

I was going to fix it by replacing XXX with a function that tells the offset function what cell it's in and therefore the value of the cell to its left.

Anyway...if anyone can help that's be great.

--UPDATE---

Ok, I found the answer! You define the unit column using Data -> Define Range menu and you then use:

 Code: =INDEX(HunderedDollarNoteUnits;0;1)
etc
ph1959de
General User

Joined: 16 Aug 2005
Posts: 7
Location: Germany

Posted: Tue Aug 16, 2005 12:40 am    Post subject:

 8daysaweek.co.uk wrote: Hi dandefed, This doesn't feel right, but I think it does what you want : =RIGHT(LEFT(CELL("coord";A1);5);1)&RIGHT(CELL("coord";A1);1) I can't find a function that simply returns the current cell's address. I'm not sure one exists, but I'll be happy if someone points it out The above is a workaround HTH,

Don't have an English version of Calc available, but it should be something like

Peter
8daysaweek.co.uk
Super User

Joined: 29 Nov 2003
Posts: 2130
Location: UK

Posted: Tue Aug 16, 2005 1:54 am    Post subject:

Hi Peter,

 ph1959de wrote: Don't have an English version of Calc available, but it should be something like =ADDRESS(ROW();COLUMN())

It is

The above returns an absolute address e.g. \$A\$1
=ADDRESS(ROW();COLUMN();4) returns a relative address e.g. A1 other options are available in help.

I had thought of ROW() and COLUMN() but had got stuck with 11 instead of A1, I even looked at ADDRESS() but couldn't see the wood for the trees

Thanks again

BFN,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
 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