Simple problem?

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!

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Foobi" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Foobi
> ------------------------------------------------------------------------
> Foobi's Profile:
http://www.excelforum.com/member.php...o&userid=19784
> View this thread: http://www.excelforum.com/showthread...hreadid=510507
>

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

Glad it worked for you !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Foobi" <[email protected]> wrote in
message news:[email protected]...
>
> 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