Insertion of a cell address that constantly rises by a set number

S

stew

I have a large spread sheet I am working on and I wish to increase a cell
address by 103 every time
ie
=L103,= L206,=L309 ETC

Is there a formula that will increase this address

thank you
 
K

Ken Johnson

I have a large spread sheet I am working on and I wish to increase a cell
address by 103 every time
ie
=L103,= L206,=L309 ETC

Is there a formula that will increase this address

thank you
Click to expand...

Maybe...
=IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L"&103*ROWS($1:1)))

Ken Jhnson
 
S

stew

Dear Ken,

I am not explaining my self enough

='Road Cash Accounting'!I318
in this formula, when I cut and paste, the I318 SHOULD BECOME I421.

What if anything can I add to this Formula to achieve that.

Thanks
Stew
 
S

Sandy Mann

Don't use Cut use Copy and the reference should change automatically.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

stew

iF i DO THAT AND PASTE TO ADJACENT CELL IT BECOMES

='Road Cash Accounting'!J318. i NEED IT TO INCREASE BY 103 EVERY TIME
I.E.='Road Cash Accounting'!I421
='Road Cash Accounting'!I524
='Road Cash Accounting'!I627

Stew
 
S

Sandy Mann

If by *Adjacent* you mean next cell down then with the *starting* formula

=Sheet1!I318

in A5 then in A6 use:

=OFFSET(Sheet1!$I$318,103*(ROW()-ROW($A$5)),0)

and copy down.

If by *adjacent* you mean next cell to the right then use COLUMN() in place
of ROW() and adjust as necessary.
Stew
On a Island of the coast of Scotland
Click to expand...

And you're working on a SUNDAY????? <G>

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

stew

this is not work, this is to keep my brain working.

I'LL TRY THIS AND LET YOU KNOW

THANKS!!
 
S

Sandy Mann

Remember that I used Sheet1 in place of your *'Road Cash Accounting'* sheet
name and it only works if placed in cell A6, you will have to adjust it to
compensate if you place it in any other cell.

I tried it and it returns the contents of cells I421,I524 & I627 in Sheet1
for me.


--
Best of luck

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

stew

YOUR A STAR.
Is there a good site or book you would recomend on Formula

Thanks again

Stew
 
S

Sandy Mann

You are looking at what I consider to be the best site for learning anything
about Excel.

I have seen in the Newsgroups that any book by John Walkenbach is worth
buying and many of the regular contributors here have their own Web sites.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

stew

Dear Sandy

That problem is well and truly solved. I have one other prOblem of the same
nature and if you can help it will also let me understand the Nature of the "
OFFSET" COMMAND

='Date Details'!T3 IS IN C1007

i WOULD LIKE
='Date Details'!U3 TO BE IN C1056

AND THIS IS REPEATED AGAIN AND AGAIN AS IT WAS THE lAST TIME.
I UNDERSTAND THAT i SHOULD BE USING ROW INSTEAD OF COLUMN BUT THIS TIME THE
CELLS ARE NOT ADJACENT
THANKS

STEW
 
S

Sandy Mann

As before with your formula:

='Date Details'!T3 in C1007

then:

=OFFSET('Date Details'!$T$3,0,MOD(ROW(),48)+1)

in C1056 and then C1105, C1154 etc.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

stew

Dear Sandy

Thank You. I have now Modified the Formula with a new understanding and it
is doing all that is required

I cannot thank you enough for this act of unpaid Generousity

Thank You

Stew
 
S

Sandy Mann

You are very welcome and your thanks is payment enough.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top