Import non-concurrent cell values from SheetA into concurrent cells on SheetB

[skinnea]

Hi - I have a spreadsheet with two tabs:

1. 'all months'
2. 'summary'

On the 'all months' tab, every 23rd cell in row R has a £££ amount in it. This number is calculated from various parts of the other 22 cells, each chunk of which represents a month.
I'd like to pull those specific amounts into the 'summary' tab and position them in a table of concurrent months.

I could hard-code each cell references (A1='all months'!$R$6 , B1='All months'!$AN$6 , etc) but I'm sure there must be a smarter way that allows me to drag the formula across my 'summary' table?

And I'm sure it's to do with OFFSET or INDEX ... but I'm not smart enough to work it out. Could someone help me please...?

 

[bebo021999]

May be in A1:

=INDIRECT("'All months'!"&ADDRESS(ROW(1:1)+5,COLUMN($R:$R)+(COLUMN(A:A)-1)*22))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

A couple of other options that are not volatile

=INDEX('all months'!$R$6:$ZZ$6,COLUMNS($A1:A1)*22-21)

or depending on version

=INDEX($R$6:$ZZ$6,SEQUENCE(,10,,22))

 

[skinnea]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Updated it (it's 365 btw) ?

And your formula sorted it, thank you!!

 

[Fluff]

Glad we could help & thanks for the feedback.

Thanks also for updating your profile.