Is there a way to edit forumla eg. =Input!A1 into =Input!"A"&"1" ?(input is the tab name) because I need to drag right and copy across for a formula increase of +2
eg. I am trying to transfer data from 1st tab to second tab. But data only exist every 2nd cell
Instead of typing =Input!A1, =Input!A3, =Input!A5 on separate cells in a second tab., or manually clicking and matching to previous tab. is there a way to quickly +2 for the numbers?
Thanks!
Assuming you are starting in cell A1 on your new sheet and referencing Input!A1 as the first cell, you could try:
Formula:
Please Login or Register to view this content.
=INDEX(Input!1:1, 2*COLUMN()-1)
Entia non sunt multiplicanda sine necessitate
Hi, Thanks for both replies, but I can't seem to get the forumla to work. I have attached an example.
So basically for the first tab (input) I haved data in Cell C5 , C7, C9 etc etc etc
for second tab (data), I have to link to first tab eg. for A6 (on Data), I would like to link to =Input!C5, and B6 link to =Input!C7, C6 link to =Input!C9 etc etc..
Thank you!
See the attached file.
=If(INDIRECT("Input!$C"&3+column()*2)="","",INDIRECT("Input!$C"&3+column()*2))
and drag to the right.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Ah that works great Thanks a lot!!
Is there a way to edit forumla eg. =Input!A1 into =Input!"A"&"1" ?(input is the tab name) because I need to drag right and copy across for a formula increase of +2You should ask the question you want answered ...for A6 (on Data), I would like to link to =Input!C5, and B6 link to =Input!C7, C6 link to =Input!C9 etc etc..
In A6 and copy across, =INDEX(Input!$C:$C, 3 + 2*COLUMN())
@SHG
Do you know if your index formula will be much faster than my indirect formula?
The old ways are sometimes useful-for a one time exercise type
||input!C5
in A6 then
||Input!C7
in B6. Select both and fill across as far as required then ctrl+h and replace || with =
Josie
if at first you don't succeed try doing it the way your wife told you to
I know it is not volatileOriginally Posted by oeldere
@JP: +1
@SHG
Thank you very much for your answer
Ah Thanks shg That works beautifullyYou should ask the question you want answered ...
In A6 and copy across, =INDEX(Input!$C:$C, 3 + 2*COLUMN())
@JP: Very neat way as well!
Last edited by tonylyx; 05-29-2013 at 09:59 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks