Step Calculation

[Eric]

In a single cell in a master spreadsheet, I am getting the value in a cell in
each of thirteen different spreadsheets and Summing them. All 13
spreadsheets referenced have the same name except for the numbers 1 - 13. As
it is now I have a Sum statement adding the result of thirteen formulas, all
of which dynamically create the filename each of which goes to a different
cell to find the numbers 1 - 13 in turn.
Instead of this long string of formulas (using the Indirect function), which
are all the same except for the cell they go to to get the number 1 - 13, I
should be able to reform the formula 13 times or steps, each step, either
incrementing the number in the spreadsheet name by 1, or starting at the
first cell and moving one column to the right to the next cell (13 cells in a
row at the top of 13 columns containing the numbers 1 - 13).
I find no Step function. Is there some other function that would do this?
Thanks,
Eric

 

[Roger Govier]

Hi Eric

Take a look at the ROW() and COLUMN() functions.
=ROW(A1) will return 1
When copied down it will change to Row(A2) etc and return 2, 3 and so on
Similarly for Column where COLUMN(A1) will return 1 and will increment as
copied across

 

[Eric]

Thanks Roger,
I have looked at the column function at your suggestion, but I don't see how
it can help with this particular problem. My formula runs from a single
cell, and I need to add together the results of references to a cell in 13
spreadsheets, each with the same name, except a number from 1 - 13.
I want to cycle 13 times through the dynamic creation of each file name,
reference the cell in that file, add it to the accumulating sum with a "+"
sign, and create the next file name either incrementing the number by "1", or
picking up the value in a contiguous row of cells (1 - 13).
Eric

 

[Roger Govier]

Hi Eric

I had missed the point that you were trying to do the Sum all in one cell.
That being the case, I would use a common cell on all subsidiary sheets to
pick up the value required.
e.g. on Sheet1 X1=B1, on Sheet2 X1=C1 etc.
Insert two new sheets.
Rename them as First and Last
Drag them so that you have Summary Sheet, First, Sheet1, Sheet2 ......
Sheet12, Last i.e first and Last "sandwich" the sheets you want to sum.
Then your formula on the Summary sheet becomes simply
=SUM(First:Last!X1)

 

[Eric]

Thanks again Roger. This was a creative solution that I will keep in mind in
other circumstances, however, I am not working in a single workbook. Every
worksheet is in its own workbook.
I send out identical spreadsheet files to 13 team leaders for them to fill
out. The spreadsheets are designed as forms with blanks to fill in. They send
them back to me and when I open them all, the Totals spreadsheet compiles all
the data. The main body of the form is a list of over 100 items (these are
actually birds counted during the Christmas Bird Count, a nationwide count
performed every year in communities across the nation). In general my totals
sheet is laid out on a one for one basis, i.e., it includes the same bird
list, and there are 13 columns, one for each sector. So the typical formula
dynamically creates the spreadsheet filename from the number at the head of
that column, and the year in a single box on my totals sheet, and references
a corresponding cell in the spreadsheet for that sector, in the row for that
bird. If there is a number in that cell, a count by that team for that bird,
then it shows up in the column for that team/sector. The column after that
for sector 13 is a Total column which just adds across all thirteen previous
columns and provides the total count for that bird (row) for all sectors.
That part is simple.
However, in each individual sector spreadsheet, and the Totals spreadsheet,
I have an additional column which tracks a different category of count for
the same list of birds, that is not included in the accumulated total
described above. For this additional category, I have not created 13
additional columns, but instead poll all thirteen spreadsheets in turn from
the single cell in this column on the row for each bird.
Again, I am using the "INDIRECT" function to dynamically create each
spreadsheet name, and the tab name (which includes the year), and reference
the same cell in each spreadsheet. The way it is now, I just have a Sum
statement with a + sign between each spreadsheet name, tab, cell expression,
and poll all thirteen spreadsheets in turn. Since the only difference in
each spreadsheet name is the Sector number, 1 - 13, I wondered if there was a
way to create the same INDIRECT expression, 13 times, incrementing the
Sector number in the filename by 1 after each "step", and summing the
retrieved counts.
Looking through Excel Help, I have not been able to find any function which
cycles for a given number of steps, allowing an incremental change after each
step. The "Enable Iterative Option" in Excel Options serves a different
purpose and operates differently.
Eric

 

[Roger Govier]

Hi Eric

If you would like to send me a copy of your Totals workbook, and perhaps 2
of the 13 individual workbooks, I will be happy to take a look and see if I
can come up with a solution for you.
Would a VBA solution be acceptable, if I cannot figure out a formula
solution?

To send the files direct, email to
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address

 

[Eric]

No thanks Roger, that's not what I was looking for. I have a solution that
does the job albeit inelegantly: A single formula in each cell on the Totals
sheet with a string of all 13 expressions Summing each of the 13
File:Tab:Cell references. What I was looking for was a Function that would
cycle through the creation of each expression, allowing the incrementing of
the Sector number in the file name by 1 in each step, and adding the result
of each expression together. This would be a much more satisfying way of
solving this problem, but I have so far not found the means to do it.
Undoubtedly this could be done in a macro using VB, but that would lose the
transparency I want to maintain, in a spreadsheet that will be passed on to
others. Also, if the erector set is going to be built, I want the
satisfaction of building it myself.
Thanks,
Eric

 

[Roger Govier]

Hi Eric

I did say
So, you don't want a VBA solution - fine.
I am finding it difficult to visualize your workbook, hence the request to
see a copy to determine whether I can suggest a different formula approach.
The offer still stands - if you want to take it up.