Cross-Sheet Formula, formula

[Immortalis15]

Hi all.


In a cell, I have this link.

=[HPSI.xlsx]Sheet1!$J$31

In another cell, I wish to call data from [HPSI.xlsx]Sheet1!$J$31 +12 cells down (essentially, 43) but I want it in a formula to add 12 cells so that every subsequent time it will keep pulling data from down 12 more cells. How would I do that? If it's even possible.

Thank you

[bentleybob]

You didn't say where the cell with the link is or where the second cell would be, so it's hard to give you a specific formula. What you want, I think, is to use INDIRECT and craft a formula that addresses the right rows.

For example, let's say your first cell is in D10 and you want to simply copy that formula down to D11, D12, etc. and get your links to work. In that case, you would use:

=INDIRECT("[HPSI.xlsx]Sheet1!$J$"&(ROW(D10)-9)*12+19)

Be more specific and folks here can help you with the exact formula you need. Hope this helps.

[Immortalis15]

Sorry, I thought that was enough information of what I was trying to do. I guess not.

I have a template set up and a set of data in cells and cell A2 has the link =[HPSI.xlsx]Sheet1!$J$31

In the next set of data, I want cell A20 to call the =[HPSI.xlsx]Sheet1!$J$43 however I don't want to have to write a new link for every set of data, I'd like to just copy/paste the template whereas all the references would be updated in relation to the new cells it'll be using.

For example... I tried variations of =[HPSI.xlsx]Sheet1!$J$31 +12 but instead of giving me the value of Sheet 1, cell 43, it gave me the value of Sheet 1, cell 31 and added 12 to the value.

I hope that's enough information... in re-reading it it sounds VERY confusing to me.

Thank you

[martindwilson]

try
=INDEX([HPSI.xlsx]Sheet1!$J$:$J$,19+ROW(A1)*12)
gives rows
31
43
55
67
79
91
103
.
.
.
.

[Immortalis15]

The formula had an error at the first $J$

[martindwilson]

oops should be
$J:$J
=INDEX([HPSI.xlsx]Sheet1!$J:$J,19+ROW(A1)*12)

[Immortalis15]

Thanks... that helps very much!. I really appreciate it.