Function for revealing current cells address

[dandefed]

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]

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]

How about something like (assuming C5 is the current cell)

[dandefed]

[8daysaweek.co.uk]

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]

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]

[dandefed]

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:

[ph1959de]

[8daysaweek.co.uk]

Hi Peter,