| =OFFSET(B1,1,0,COUNT(B:B),1). Then I clicked on OK. Finally, I entered the formula =SUM(Sales) in cell E1. Note that COUNT(B:B) refers to the count of all numeric cells in column B. So this OFFSET function refers to a range that starts 1 cell down from cell B1 and has as many rows as there are numeric values in column B. To see how it adjusts, enter a sales value for June in cell B7 and watch how the total changes automatically.
Note that COUNT(B:B) counts all of the numeric values in column B, so if there were some other numbers down below sales, they could mess up the logic in the OFFSET function. For example, suppose there were a numeric value in cell B100. Then, given the data above, COUNT(B:B) would return 6, so at that point, the Sales range name would refer to the range B2:B7, that is, 6 cells starting with B2. The moral is that if you want to use dynamic range names, it’s best not to include “junk” in the affected columns or rows.
Try it! The following spreadsheet has sales by month and by salesperson. We want to sum sales over all months and salespeople in cell B2. Create a dynamic range name that updates when new months are added
Dostları ilə paylaş: |
