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
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
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
=Beltloops!$D11
then drag down
regards, barry
ASKER
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.
The row numbers vary. One group would be 11-13, then 20-27.
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
What is the purpose of this summary tab? Are you counting something? Or just replicating certain sections? Can you post the workbook?
Kevin
ASKER
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
BeltloopPintrax1.9.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Kevin