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

Function for revealing current cells address

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


Joined: 14 Aug 2005
Posts: 3

PostPosted: Sun Aug 14, 2005 8:51 pm    Post subject: Function for revealing current cells address Reply with quote

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 Smile

Thanks for any help!
Back to top
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Mon Aug 15, 2005 1:23 am    Post subject: Reply with quote

Hi dandefed,

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)

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 Smile The above is a workaround Confused

HTH,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
ms777
Super User
Super User


Joined: 07 Feb 2004
Posts: 1355

PostPosted: Mon Aug 15, 2005 3:04 am    Post subject: Reply with quote

How about something like (assuming C5 is the current cell)
Code:
=OFFSET($A$1;COLUMN(C5)-1;ROW(C5);1;1)
Back to top
View user's profile Send private message
dandefed
Newbie
Newbie


Joined: 14 Aug 2005
Posts: 3

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

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
Back to top
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Mon Aug 15, 2005 3:11 am    Post subject: Reply with quote

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 Smile,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Aug 15, 2005 3:20 am    Post subject: Reply with quote

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 Sad
Back to top
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

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

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 Smile,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
dandefed
Newbie
Newbie


Joined: 14 Aug 2005
Posts: 3

PostPosted: Mon Aug 15, 2005 4:25 am    Post subject: Still trying Reply with quote

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
Back to top
View user's profile Send private message
ph1959de
General User
General User


Joined: 16 Aug 2005
Posts: 7
Location: Germany

PostPosted: Tue Aug 16, 2005 12:40 am    Post subject: Reply with quote

8daysaweek.co.uk wrote:
Hi dandefed,

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)

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 Smile The above is a workaround Confused

HTH,

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

Peter
Back to top
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Tue Aug 16, 2005 1:54 am    Post subject: Reply with quote

Hi Peter,

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

It is Very Happy

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 Shocked

Thanks again Smile

BFN,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
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