Increment text field in excel formula

I have an Excel workbook that I use to track the achievements of my Cub Scout pack.  One of the sheets is outdated, and the original creator is deceased, so I took it upon myself to update it.

I am using the following formula:

=INDIRECT("Beltloops!" & $A$3 & "11")

On each individual scout worksheet to track entries made to the master worksheet.  The problem I have now is that there are over 200 cells that need to be changed from a direct cell reference to the formula above, on 15 worksheets.  Is there any way I can automate incrementing the text field (11 in my example) the same way the numbers increase when I drag and copy a cell with a number or a formula?

H
heisensteinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Yes. Can you give three example formulas as you want them to appear and the row numbers on which they are located?

Kevin
0
barry houdiniCommented:
What's in A3, just a letter like "D"? Not sure why you need INDIRECT at all, why not just use

=Beltloops!$D11

then drag down

regards, barry
0
heisensteinAuthor Commented:
A3 contains the letter for each scouts column on the summary pages; scout 1 is e, scout 2 is f...

The row numbers vary.  One group would be 11-13, then 20-27.
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

zorvek (Kevin Jones)ConsultantCommented:
We need a different approach then.

What is the purpose of this summary tab? Are you counting something? Or just replicating certain sections? Can you post the workbook?

Kevin
0
heisensteinAuthor Commented:
This is the file as I have so far modified it.  The 'Scout 1' tab is where I am making all the changes for the individual scouts; the pins, and belt loops tabs are where I actually will record the achievement.  This info needs to be echoed to each individual scout page, and the summary page, which is what I need to submit to get the boys their awards.
BeltloopPintrax1.9.xlsx
0
zorvek (Kevin Jones)ConsultantCommented:
Here is a start for the formula in H3 on down but it will still require work getting the formulas installed.

=INDEX(Beltloops!$E:$S,MATCH(F3,OFFSET(Beltloops!$B$1,MATCH(E$3,Beltloops!$B:$B,0)-1,0,20,1),0)+MATCH(E$3,Beltloops!$B:$B,0)-1,MATCH($A$1,Beltloops!$E$1:$S$1,0))

Caveats:

The value in E3 and on has to match the value in Beltloops:B7 and on.

You have to edit the E$3 value for each section.

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
heisensteinAuthor Commented:
I will assume that this solution would work, but given that I would still need to change all the formulas, I went ahead and made all the required entries static.  It was more important to tie each sheet to the columns on the summary pages.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.