Fiona has attended:
Excel Intermediate course
Summary sheet formula on multi sheet workbook
Hi there
I am creating a workbook which has multiple sheets and have created a summary sheet from which info feeds through from the following sheets.
The cells that the summary sheet needs to pick up are not consecutive but are alternate cells on the same line in each individual sheet.
For eg cell AD5 in the summary sheet needs to pick up cell 'Sheet 1'!BD$332, Then cell AE5 needs to pick up cell 'Sheet 1'!BF$332, Then cell AF5 needs to pick up cell 'Sheet 1'!BH$332 etc
Is there a formula that can be input into the summary sheet to pick up alternate cells in this way?
Thank you,
Fiona
RE: Summary sheet formula on multi sheet workbook
Hi Fiona
Thanks for getting in touch. I can think of a couple of ways but I think OFFSET will be the most flexible.
OFFSET means "start at this cell, then tell me what's located X number of cells away".
You'd do something like this, in AD5 enter:
=OFFSET('Sheet 1!'BB$332,0,2)
Then copy across. This formula says "Start at cell BB$322, stay in the same row, move across 2 columns".
Give it a try, and let me know how you get on.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Summary sheet formula on multi sheet workbook
Thanks Gary this almost works but not quite because then cell AE5 looks at 'Sheet 1'! BC$332 and moves on 2 cells from there where I need it to show the data in cell BD332?
RE: Summary sheet formula on multi sheet workbook
Hi Fiona
Thanks for trying it out. I've had another look and it's a little more tricky than I first suspected. You need the following formula in AD5:
=INDEX(Sheet1!BD$332:$BZ$332,2*(COLUMNS(Sheet1!$BD$332:BD332)-1)+1)
And change BD$332:$BZ$332 to the full width of columns that you need.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
