How to set formula for different gap in source and destination

[paramveer]

https://skydrive.live.com/redir?resid=FF9C73CAE52F48A2!118


Check out this link. there is excel file. Please solve this problem

 

[bobhc]

parameveer


It is always best to give as much information as possible such as uploading a file but there are those who do not like having to register on a site to be able to download a file or they are unable to because of restrictions in place. can you not post the sample question and can you not use something like Dropbox which does not require a login to download a file.


If you explain your problem in the post others may be able to help.

 

[SirJB7]

Hi, paramveer!

Your uploaded file yet does what you're asking for help. In a direct way indeed...

Regards!

PS: Now if you want to complicate it type this in A7:

=DESREF($A$7;(FILA()-FILA($A$7)-1)*1;COLUMNA($F$1)-COLUMNA($A$7)) -----> in english: =OFFSET($A$7,(ROW()-ROW($A$7)-1)*1,COLUMN($F$1)-COLUMN($A$7))

just to keep the formula with the same structure as the other.

 

[SirJB7]

@b(ut)ob(ut)hc

Hi, old friend!

Good afternoon.

Regards!

 

[bobhc]

@ SirJB7


Good day old friend,I hope you are well, did your protest do any good??

 

[paramveer]

Sir I want a Formula which i Want to place at Cell A1 When I copy formula from A1 to A7 the Value Come : f6 i.e 56 (Here I don't want to adopted dragged procedure)


Your formula work on A7 but not work on A1..


So please give me proper Formula

 

[SirJB7]

Hi, paramveer!

=DESREF($A$1;(FILA()-FILA($A$1)+SI(FILA()=1;1;-1))*SI(FILA()=1;2;1);COLUMNA($F$1)-COLUMNA($A$1)) -----> in english: =OFFSET($A$1,(ROW()-ROW($A$1)+IF(ROW()=1,1,-1))*IF(ROW()=1,2,1),COLUMN($F$1)-COLUMN($A$1))

Regards!

 

[paramveer]

Thanks Sir, It work


Please sir explain it functionality.

 

[paramveer]

Because i apply it on huge number of sheet

 

[Debraj]

Its functionality is ..


If you write this formula is A1.. it will show data from F3.

and if you write this formula in A7.. it will show data from F6.


This formula dont have any other power..

The main power is in the technical mind to create the formuala.


if your data dont have even any similarity.. It really hard to adapt.. So requesting to upload the your exact file.. (Not sample) as only two field never help to search similarity..


BTW.. above formula is a combination of

OFFSET:

http://chandoo.org/wp/2012/09/17/offset-formula-explained/


ROW:

http://chandoo.org/excel-formulas/row.shtml


and COLUMN:

http://chandoo.org/excel-formulas/column.shtml

 

[SirJB7]

@Debraj Roy

Hi!

Thanks for taking the baton.

Regards!