Summing across multiple sheets using a changeable reference cell
I've spent a couple of hours searching this group but I can't seem to
find a solution that works! I have a workbook with sheets named with dates, but let's call them "A", "B", "C", "D" etc I have a 'from' date in cell A1 and a 'to' date in cell A2, so let's say these are 'B' and 'D' So what I want is a formula that will sum cell F8 on each of the sheets in the range from 'B' to 'D' I know using INDIRECT and SUM doesn't work, but from reading this group I think there's probably some way of using SUMPRODUCT but I just can't get anything to work. Any help greatly appreciated! |
Summing across multiple sheets using a changeable reference cell
ajnmx wrote...
.... I have a workbook with sheets named with dates, but let's call them "A", "B", "C", "D" etc I have a 'from' date in cell A1 and a 'to' date in cell A2, so let's say these are 'B' and 'D' So what I want is a formula that will sum cell F8 on each of the sheets in the range from 'B' to 'D' I know using INDIRECT and SUM doesn't work, but from reading this group I think there's probably some way of using SUMPRODUCT but I just can't get anything to work. Any help greatly appreciated! This isn't consistent with your subject. Start by creating an *ORDERED* list of the worksheet names in a range. I'll use WSLST to refer to this list. It should be in order. The expression N(INDIRECT("'"&WSLST&"'!F8")) will return an array of the F8 values in each of these worksheets (it'll return 0 for any cell evaluating to text, blank or FALSE, and 1 for any cell evaluating to TRUE). Use this in a formula like the following to sum over a specific range of worksheets. =SUMPRODUCT((WSLST=From)*(WSLST<=To),N(INDIRECT(" '"&WSLST&"'!F8"))) If you want a method for generating a list of worksheet names automatically, see http://groups.google.com/group/micro...96ac93fb63b01e or http://tinyurl.com/5h5o4t |
Summing across multiple sheets using a changeable reference cell
That is genius! Works perfectly. Thank you sooooo much (and sorry
about the subject!). |
All times are GMT +1. The time now is 08:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com