0

I am using offset to get a range of the last 7 entries in a column which updates automatically when I add data to it. However, to be able to automate this as much as possible I have to use a formula to determine if there is a value to display in that cell. This causes the cell to be not-empty and thus the offset isn't working. Any workaround for this?

I copy the data from Sheet1 into another sheet because I need to hide the columns in Sheet1, and therefor can't create a sparkline for those values anymore. Copying the data into column B on Sheet 2: 

=IF(Sheet1!O18<>"";Sheet1!O18;"")

Using the offset:

=OFFSET(Sheet2!$B$3;COUNTA(Sheet2!$B:$B)-7;0;7)
Chrisvdberge
  • 1,824
  • 6
  • 24
  • 46
  • I got it working with a workaround in the OFFSET; I changed the formula on sheet2 to assign the value 0 in case of no-entry, and changed the OFFSET to COUNTA(Sheet2!$B:$B)-COUNTIF(Sheet2!$B:$B;0)-7 this works :) – Chrisvdberge Jul 03 '11 at 13:05
  • Please post your answer as an answer, and then accept it to mark the question as solved. – Jean-François Corbett Jul 04 '11 at 07:21

2 Answers2

1

I got it working with a workaround in the OFFSET; I changed the formula on sheet2 to assign the value 0 in case of no-entry, and changed the OFFSET to COUNTA(Sheet2!$B:$B)-COUNTIF(Sheet2!$B:$B;0)-7 this works :)

Chrisvdberge
  • 1,824
  • 6
  • 24
  • 46
0

You could use the SUMPRODUCT formula as described in this thread: Excel - Create chart from range of cells while excluding null values? =OFFSET(Sheet1!$A$2,1,0,SUMPRODUCT(IF(Sheet1!A2:A60<>"",1,0)),1))

Community
  • 1
  • 1
JMax
  • 26,109
  • 12
  • 69
  • 88