98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Summary sheet formula on multi sheet workbook | Excel forum
Summary sheet formula on multi sheet workbook | Excel forum
Resolved · High Priority · Version 2010
Mon 15 Apr 2013, 14:15Delegate Fiona said...
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
Mon 15 Apr 2013, 15:05Trainer Gary said...
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
Mon 15 Apr 2013, 15:35Delegate Fiona said...
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?
Tue 16 Apr 2013, 10:25Trainer Gary said...
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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Creating a range of monthly payments as textYou could use a formula to create a range of payment ie. payment amount for x% to y% rate with fixed terms and principle. |
