Advice on copying cells into second tab

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Is this it? In B9, copied down:

=IFERROR(INDEX(Sheet1!$B$2:$B$55,MATCH(0,INDEX(COUNTIF($B$8:$B8,Sheet1!$B$2:$B$55),0),0)),"")

and in c9, copied across and down:
=SUMIF(Sheet1!$B:$B,$B9,Sheet1!N:N)

What constitutes the current period? How is it identifiable in the source data?

So the current period is the current month and the previous two? Why not just answer the question???

I did not think you were being rude!

My point is that if you want a chart to draw from the 'current period', then you need to define what that is. Excel will need to know, so yes, it IS important.

I presume that it's this bit you need to understand:

=IFERROR(INDEX(Sheet1!$B$2:$B$55,MATCH(0,INDEX(COUNTIF($B$8:$B8,Sheet1!$B$2:$B$55),0),0)),"")

the INDEX in the middle of it is just an Excel "trick" to transform an array formula into a non-array (ordinary) formula, which many users prefer. The unadapted formula is:

=IFERROR(INDEX(Sheet1!$B$2:$B$55,MATCH(0,COUNTIF($B$8:$B8,Sheet1!$B$2:$B$55),0)),"")

Being an array formula, it would need to be set using CTRL-SHIFT-Enter.

At its heart is this bit:



COUNTIF($B$8:$B8,Sheet1!$B$2:$B$55)

which returns 1 or 0 depending on whether the value in $B$8:$B8 has previously been found in the range $B$2:$B$55. In B9, the value in B8 (a blank cell - but can be a header) will never previously been found in that range, so it returns a series of 0s. The bit in red returns the row corresponding to the first zero. In B9, it will be the first row. The INDEX bit in cyan returns the corresponding value from column B - in this case BD. When the formula is copied into B9, any cells inthe column containing BD will return a 1 in the countif, so the first row that returns a zero in the countif will be passed to the INDEX function, and KR will be returned.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.