Simple problem?

F

Foobi

Hi!

My aim is to have a user to fill out a form on "sheet A", which I am to
convert into a specified number tickets. I want everything to be
constant, except the increasing ticket number.

Because of desired layout, all text on the tickets must be centered...
so I've had to use some combined cells.

Situation: Column A contains values: 1,2,3,4,5....
B1: <text>
B2: <text>
B3: =Sheet1!$A$1&": "&$A1

If I mark these 3 cells and use the handle to drag out more, I need to
get number "2" in B6 and not "5".

* Can anyone think of a better way of solving this?

* Can I do something like this: =Sheet1!$A$1&": "&$A(1+1)

Grateful for any suggestions!
 
M

Max

B3: =Sheet1!$A$1&": "&$A1
.. do something like this: =Sheet1!$A$1&": "&$A(1+1)
Click to expand...

Try replacing the formula in B3 with:
=Sheet1!$A$1&": "&INDIRECT("A"&INT((ROWS($A$1:A1)-1)/3)+1)

Then select B1:B3 and copy down as far as required.
That should return the desired incrementation
 
F

Foobi

Thanx a lot Max!

It really worked (after a translation of certain function names).
I've never used the INDIRECT or ROWS functions, but I'll do some
reading and see what else they can be used for.

/Foobi :)
 
Top