LinkBack URL
About LinkBacks
if i had in sheet1: a1=1
a6=2
a11=3
and so on...
how can i make in sheet2: a1=1
a2=2
a3=3
and when i drag it in sheet2 it will be continuous.
plz it would make my life sooo mch easier.
if i had in sheet1: a1=1
a6=2
a11=3
and so on...
how can i make in sheet2: a1=1
a2=2
a3=3
and when i drag it in sheet2 it will be continuous.
plz it would make my life sooo mch easier.
Hi there... and welcome to the Excel Forum.
Try this:
=OFFSET($A$1,(ROWS($1:1)-1)*5,,,)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
thank you it worked like a charm, but i still have a small issue about this. is it possible if i add a new row to the reference and the equation wont change? i tried adding one and the numbers changed. can it be fixed?
Can you attach an Excel sheet showing exactly what you mean?
The formula is set to copy every 5th row. if you are changing your request to sometimes copy every 5th row and sometimes to copy every 6th row, then I'd need to see what else is on the sheet.
sorry for the delay i was trying to make the sheet a bit clear since its a mess, here you go
as you can see sometimes the rows could be 6 in between or 4 or 5 even more so i would be always adding and deleting. also this is only a sample later on itll be more than 200 items. thank u for your time
Last edited by karim.zheng; 08-01-2015 at 05:26 AM.
A3=IFERROR(INDEX('Accessories '!B:B,SMALL(INDEX(('Accessories '!$A$6:$A$31<>1)*10^10+ROW('Accessories '!$A$6:$A$31),0),ROWS(A$3:A3))),"")Try this and copy towards down
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
thank you very much but i need it for the whole column not only till A35 because in the future ill be adding alot. i was dragging it down it only completed 6 cells. thank u for ur time.
then just adjust the range to go down as far as you need:
A3=IFERROR(INDEX('Accessories '!B:B,SMALL(INDEX(('Accessories '!$A$6:$A$31<>1)*10^10+ROW('Accessories '!$A$6:$A$31),0),ROWS(A$3:A3))),"")
ive changed the range to 1000, but it stops at 426 and it doesnt continue. any solutions?(im rly rly rly sry its just that this is kinda important)
=IFERROR(INDEX(Accessories!B:B,SMALL(INDEX((Accessories!$A$6:$A$600<>1)*10^10+ROW(Accessories!$A$6:$A$600),0),ROWS(A$3:A3))),"")
hey ive made the range till 600 but it just makes 85 cells, help plz?
will you pls attach sample file after removing confidential data if any
sorry for disturbing u ive attached book5 it stops at number 85 and under it doesnt continue. i need it to be, if can unlimited. best regards.
In Cell A427 the S/N was started with 5 aind In Cell A432 S/N Started with 9 so on thats why it is not giving the result
By assuming that every block starts with S/N 1 based on that formula was created so change the S/N number then it will give correct result
THANK YOU SOO MUCH. it really means alot! thank u for ur time and sry for giving any troubles. life saver.
You are welcome and thanks for your feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks