Solved

Increment text field in excel formula

Posted on 2012-03-30
7
304 Views
Last Modified: 2012-04-26
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
0
Comment
Question by:heisenstein
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 81

Expert Comment

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

Kevin
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 37789514
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
 

Author Comment

by:heisenstein
ID: 37789546
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 37789552
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
 

Author Comment

by:heisenstein
ID: 37789635
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 37789671
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
 

Author Closing Comment

by:heisenstein
ID: 37897202
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question