D1: '=A101', D2: '=A201', D3: '=A301' how to do this...

[R-P]

If I try this and drag the box down to copy this format, I end up with
'pointers' to A101, A201, A301, A102, A202, A302, etc.

I want A1, A101, A201, A301, A401, etc.

Reason: 45000 serialnumbers. They are not all sequential, that is, many
numbers are missing, but they are in ascending order.

What I need is to get an overview of quality, where all serialnumbers with
quality issues are marked in a seperate column as 1 (faulty) or 0 (good).

My choice was to take 100 sequential serialnumbers and take an average of
the faults, and an average of the serialnumbers/100 and plot the results. So
I would have 450 datapoints with % quality-issues-per-100 on the Y axis and
average serialnumber devided by 100. The latter to see at one glance if a
certain 100 products are worse than the 100 before or after.

The problem is that I wound up with two columns: "faults per 100" and
"serialnumberrange", but they only have data at e.g. D101 and E101, the next
dataset is at D201 and E201, the next at D301, etc.
When selecting these two columns, the graph-wizard informs me that I have
more than 32000 datapoints, and it cannot handle more than 32000.

Obviously I only have 450 datapoints, but I tried several ways to copy them
to a new sheet with e.g. A1: '=firstsheet!D101' and A2: '=firstsheet!D201',
but I don't feel like typing this 450times, and dragging the box down doesn't
work because it returns the numbers 101, 201, 102, 202, etc. into the formula.

Is there a way to increment the 'pointer' with a certain value? Can you
'work' with A101 + '100' = A201? or A101 + (F1) = A201 where in F1 you can
fill in a constant?

Ideas welcomed!

[Pete]

A simple way is to <copy> the column containing your data -
firstsheet!D - then click into Sheet2 and Edit | Paste Special | Values
| OK. Then sort this column, so all the blank entries will be bunched
together (which you can delete if you wish).

Pete

[R-P]

I hate you.....
That solution is way too simple, but also very effective!!!

I still would like to know if there are other ways to solve this.

Thanks Pete! for making me feel STUPID and for solving my problem...

"Pete" wrote:

> A simple way is to <copy> the column containing your data -
> firstsheet!D - then click into Sheet2 and Edit | Paste Special | Values
> | OK. Then sort this column, so all the blank entries will be bunched
> together (which you can delete if you wish).
>
> Pete
>
>

[Pete]

Well you could do it using OFFSET( ), but why make things complicated?

Glad my first suggestion worked for you!

Pete