| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insertion of a cell address that constantly rises by a set number
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set number
On Aug 24, 8:50 pm, stew wrote:
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 Maybe... =IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L "&103*ROWS($1:1))) Ken Jhnson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set num
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 "Ken Johnson" wrote: On Aug 24, 8:50 pm, stew wrote: 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 Maybe... =IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L "&103*ROWS($1:1))) Ken Jhnson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set num
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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 "Ken Johnson" wrote: On Aug 24, 8:50 pm, stew wrote: 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 Maybe... =IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L "&103*ROWS($1:1))) Ken Jhnson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set num
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 And you're working on a SUNDAY????? <G -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 On a Island of the coast of Scotland Scotland "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 "Ken Johnson" wrote: On Aug 24, 8:50 pm, stew wrote: 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 Maybe... =IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L "&103*ROWS($1:1))) Ken Jhnson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set num
this is not work, this is to keep my brain working.
I'LL TRY THIS AND LET YOU KNOW THANKS!! "Sandy Mann" wrote: 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 And you're working on a SUNDAY????? <G -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 On a Island of the coast of Scotland Scotland "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 "Ken Johnson" wrote: On Aug 24, 8:50 pm, stew wrote: 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 Maybe... =IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L "&103*ROWS($1:1))) Ken Jhnson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set num
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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... this is not work, this is to keep my brain working. I'LL TRY THIS AND LET YOU KNOW THANKS!! "Sandy Mann" wrote: 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 And you're working on a SUNDAY????? <G -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 On a Island of the coast of Scotland Scotland "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 "Ken Johnson" wrote: On Aug 24, 8:50 pm, stew wrote: 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 Maybe... =IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L "&103*ROWS($1:1))) Ken Jhnson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set num
YOUR A STAR.
Is there a good site or book you would recomend on Formula Thanks again Stew "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... this is not work, this is to keep my brain working. I'LL TRY THIS AND LET YOU KNOW THANKS!! "Sandy Mann" wrote: 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 And you're working on a SUNDAY????? <G -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 On a Island of the coast of Scotland Scotland "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 "Ken Johnson" wrote: On Aug 24, 8:50 pm, stew wrote: 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 Maybe... =IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L "&103*ROWS($1:1))) Ken Jhnson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set num
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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... YOUR A STAR. Is there a good site or book you would recomend on Formula Thanks again Stew "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... this is not work, this is to keep my brain working. I'LL TRY THIS AND LET YOU KNOW THANKS!! "Sandy Mann" wrote: 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 And you're working on a SUNDAY????? <G -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 On a Island of the coast of Scotland Scotland "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 "Ken Johnson" wrote: On Aug 24, 8:50 pm, stew wrote: 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 Maybe... =IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L "&103*ROWS($1:1))) Ken Jhnson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set num
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 "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... YOUR A STAR. Is there a good site or book you would recomend on Formula Thanks again Stew "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... this is not work, this is to keep my brain working. I'LL TRY THIS AND LET YOU KNOW THANKS!! "Sandy Mann" wrote: 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 And you're working on a SUNDAY????? <G -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 On a Island of the coast of Scotland Scotland "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 "Ken Johnson" wrote: On Aug 24, 8:50 pm, stew wrote: 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 Maybe... =IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L "&103*ROWS($1:1))) Ken Jhnson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set num
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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... YOUR A STAR. Is there a good site or book you would recomend on Formula Thanks again Stew "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... this is not work, this is to keep my brain working. I'LL TRY THIS AND LET YOU KNOW THANKS!! "Sandy Mann" wrote: 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 And you're working on a SUNDAY????? <G -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 On a Island of the coast of Scotland Scotland "Sandy Mann" wrote: 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 Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 "Ken Johnson" wrote: On Aug 24, 8:50 pm, stew wrote: 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 Maybe... =IF(INDIRECT("L"&103*ROWS($1:1))="","",INDIRECT("L "&103*ROWS($1:1))) Ken Jhnson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set number
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 "stew" wrote: 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insertion of a cell address that constantly rises by a set number
You are very welcome and your thanks is payment enough.
-- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... 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 "stew" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do i remove a constantly appearing % in a cell in excel? | Excel Discussion (Misc queries) | |||
| Auto column insertion based on a number | Excel Worksheet Functions | |||
| Auto row insertion based on a number | Excel Worksheet Functions | |||
| automatic multiple insertion of 0 infront of a tel number? | Excel Discussion (Misc queries) | |||
| VB Random Number Generation/Insertion/NextWorksheet | Excel Discussion (Misc queries) | |||
Linear Mode
