Summing Moving Values

[ExcelDabbler]

Hello,

I need to sum values that will appear in different rows (but the same columns) of any sheets that appear between a sheet named "start" and a sheet named "stop."

The moving row number is a new development, so right now, I've got this, and it works:

=SUM(start:stop!G423)

If I place a sheet between "start" and "stop," the value of G423 on that sheet is added to my sum. What I need is to replace the "423" with any row that contains the text "FinalValue" in column D.

As often happens, I feel like I almost get to an answer with my limited knowledge of formulas, but I can't quite get the pieces together. Any help would be much appreciated.

 

[gsbelbin]

I've not been able to come up with a clever solution to this so I've resorted to a workaround. You could have a cell on each of the sheets which sums the relevant cells on that sheet. So for example A1 would have the formula =SUMIF(D2:D10, "FinalValue", G2:G10). Then to get the overall total you could use the method you used for one cell on each sheet ie =SUM(start:stop!A1)

 

[ExcelDabbler]

Thanks for the suggestion. I'd thought about just moving the FinalValue row to the top of the spreadsheet, but I didn't want to have to go through all of the sheets, and I also wanted it out of the way.

I did manage a pretty elaborate solution, though. On a scratch sheet, I used the hidden, defined-name Replace function to generate a list of all sheets in the workbook. Then a Match function embedded in an Address function got me the right cell address on a particular sheet, but to avoid having to type in every sheet name, I had to create the Address function as text and then run a couple of hidden Evaluate functions.

It's working, but with the drawback that it has to be a macro-enabled file, and the hidden formulas don't work on Mobile versions of Excel. If anybody has a more elegant solution, I'd love to hear it. I feel like I found a way to replace the Evaluate function with a non-macro array formula that used the Row function in an ingenious way, but I lost track of that formula and have never been able to find it again.

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Is there only one row in each worksheet that has "FinalValue" in column D?

2. If so,
a) will it be on the same row in each worksheet?
b) will it be the last row with data in column D?

 

[ExcelDabbler]

Thank you for the reply.

There is only one row with "FinalValue" in it. The challenge is that isn't on the same row every time. The application is a daily schedule that starts with a template but changes, with tasks added or deleted. By way of description: Column D contains descriptions of each task and the columns to the right allocate the number of minutes spent on that task to one or more categories ("Knowledge," "Work," "Health," etc. The last data row (which should... or can... always be the last one) is the difference of the templated planned times for each category and the actual times.

 

[Peter_SSs]

There is only one row with "FinalValue" in it. The challenge is that isn't on the same row every time.

I'd thought about just moving the FinalValue row to the top of the spreadsheet, but I didn't want to have to go through all of the sheets, and I also wanted it out of the way.

I think we can deal with all of that pretty easily because you can work on all those sheets at once. It is basically what has already been suggested, but I think deals with the "issues" you raised.
1. Select all the sheets from 'start' to 'stop'
2. Insert a new row 1
3. Select cell G1 on the active sheet
4. Enter the formula =VLOOKUP("FinalValue",D2:G1000,4,0) You may need/want to change the 1000. It needs to be big enough to be sure of always including all your data on even the biggest of those sheets.
5. Hide row 1
6. Activate the sheet where you want the final sum and enter the formula: =SUM(start:stop!G1)

 

[ExcelDabbler]

Ah! I didn't know about the working-on-multiple-sheets thing. (Or if I did, I'd forgotten it.) That helps, thank you!

 

[Peter_SSs]

That helps, thank you!

Cheers.