Thread: Automatic Sequential renumbering within formulas

I have 2 columns which will more or less repeat again and again but the cells
are linked to columns in another worksheet. Basically, I'm having to copy
and paste my first 2 columns, go into each cells formulas and change the
Letter and the Number of the cell it is linked to (in the formula). Excel
automatically goes one up on the Letter but in the wrong direction and it
doesn't go one up in the Number.

It's hard to explain but is there an easy way to dictate what intervals go
up within the formula's reference to the linked cells - in what increments?

Can you provide more info:
1)What cell you're entering the formula in and What formula you're entering
2) What cell you're copying the formula to, and how exactly you desire the
copied formula (with automatically updated references after you copied it)
to be different from how it is.





"Task Lead Nicole" <TaskLeadNicole@discussions.microsoft.com> wrote in
message news:F8CA785D-0E96-4EB5-B2F8-C0271649623F@microsoft.com...
> I have 2 columns which will more or less repeat again and again but the
cells
> are linked to columns in another worksheet. Basically, I'm having to copy
> and paste my first 2 columns, go into each cells formulas and change the
> Letter and the Number of the cell it is linked to (in the formula). Excel
> automatically goes one up on the Letter but in the wrong direction and it
> doesn't go one up in the Number.
>
> It's hard to explain but is there an easy way to dictate what intervals go
> up within the formula's reference to the linked cells - in what
increments?

My columns look like this:
=Summary!F113 =IF(Summary!G113<>"","Complete","")
=Summary!H113 =IF(Summary!I113<>"","Complete","")
=Summary!J113 =IF(Summary!K113<>"","Complete","")
=Summary!L113 =IF(Summary!M113<>"","Complete","")
=Summary!N113 =IF(Summary!O113<>"","Complete","")
=Summary!P113 =IF(Summary!Q113<>"","Complete","")
=Summary!R113 =IF(Summary!S113<>"","Complete","")
=Summary!T113 =IF(Summary!U113<>"","Complete","")
=Summary!V113 =IF(Summary!W113<>"","Complete","")

....because they're being populated from another worksheet. The subsequent
columns (going on and on) in pairs like that need to be the same but with
simply the number (like 113 above) changing, going up sequentially because
these reference Rows in the other worksheet.

What I ended up doing was copying the above into a Word doc (as a table),
doing a Find and Replace for the number every time, and copying and pasting
the newly numbered 2 columns in where I wanted them in the worksheet. It
worked pretty well as a work-around.

But now I'm just curious if you can override Excel's attempt to be smart --
i.e., it was automatically up'ing the Letter in the above formulas Each Time
I copied and pasted within that worksheet. I couldn't figure out how to turn
off that function because my life would've been easier if it had even just
let me copy and paste the exact code/formulas in the columns and all I
would've had to do was change the Numbering myself manually.

Let me know what you can find. Thanks!

"Dave R." wrote:

> Can you provide more info:
> 1)What cell you're entering the formula in and What formula you're entering
> 2) What cell you're copying the formula to, and how exactly you desire the
> copied formula (with automatically updated references after you copied it)
> to be different from how it is.
>
>
>
>
>
> "Task Lead Nicole" <TaskLeadNicole@discussions.microsoft.com> wrote in
> message news:F8CA785D-0E96-4EB5-B2F8-C0271649623F@microsoft.com...
> > I have 2 columns which will more or less repeat again and again but the
> cells
> > are linked to columns in another worksheet. Basically, I'm having to copy
> > and paste my first 2 columns, go into each cells formulas and change the
> > Letter and the Number of the cell it is linked to (in the formula). Excel
> > automatically goes one up on the Letter but in the wrong direction and it
> > doesn't go one up in the Number.
> >
> > It's hard to explain but is there an easy way to dictate what intervals go
> > up within the formula's reference to the linked cells - in what
> increments?
>
>
>

Still not sure about your data layout. But if you want to prevent Excel from
helping when you copy a formula, put $ money signs in front of the part you
don't want to change. e.g. $A$1 will remain $A$1 no matter where you copy
it. $A1 will turn to $A2 if you copy it down one row and 1 through 254
columns to the right.




"Task Lead Nicole" <TaskLeadNicole@discussions.microsoft.com> wrote in
message news:AB73A24F-8A13-4D7E-A13C-D204F6052D51@microsoft.com...
> My columns look like this:
> =Summary!F113 =IF(Summary!G113<>"","Complete","")
> =Summary!H113 =IF(Summary!I113<>"","Complete","")
> =Summary!J113 =IF(Summary!K113<>"","Complete","")
> =Summary!L113 =IF(Summary!M113<>"","Complete","")
> =Summary!N113 =IF(Summary!O113<>"","Complete","")
> =Summary!P113 =IF(Summary!Q113<>"","Complete","")
> =Summary!R113 =IF(Summary!S113<>"","Complete","")
> =Summary!T113 =IF(Summary!U113<>"","Complete","")
> =Summary!V113 =IF(Summary!W113<>"","Complete","")
>
> ...because they're being populated from another worksheet. The subsequent
> columns (going on and on) in pairs like that need to be the same but with
> simply the number (like 113 above) changing, going up sequentially because
> these reference Rows in the other worksheet.
>
> What I ended up doing was copying the above into a Word doc (as a table),
> doing a Find and Replace for the number every time, and copying and
pasting
> the newly numbered 2 columns in where I wanted them in the worksheet. It
> worked pretty well as a work-around.
>
> But now I'm just curious if you can override Excel's attempt to be
smart --
> i.e., it was automatically up'ing the Letter in the above formulas Each
Time
> I copied and pasted within that worksheet. I couldn't figure out how to
turn
> off that function because my life would've been easier if it had even just
> let me copy and paste the exact code/formulas in the columns and all I
> would've had to do was change the Numbering myself manually.
>
> Let me know what you can find. Thanks!
>
> "Dave R." wrote:
>
> > Can you provide more info:
> > 1)What cell you're entering the formula in and What formula you're
entering
> > 2) What cell you're copying the formula to, and how exactly you desire
the
> > copied formula (with automatically updated references after you copied
it)
> > to be different from how it is.
> >
> >
> >
> >
> >
> > "Task Lead Nicole" <TaskLeadNicole@discussions.microsoft.com> wrote in
> > message news:F8CA785D-0E96-4EB5-B2F8-C0271649623F@microsoft.com...
> > > I have 2 columns which will more or less repeat again and again but
the
> > cells
> > > are linked to columns in another worksheet. Basically, I'm having to
copy
> > > and paste my first 2 columns, go into each cells formulas and change
the
> > > Letter and the Number of the cell it is linked to (in the formula).
Excel
> > > automatically goes one up on the Letter but in the wrong direction and
it
> > > doesn't go one up in the Number.
> > >
> > > It's hard to explain but is there an easy way to dictate what
intervals go
> > > up within the formula's reference to the linked cells - in what
> > increments?
> >
> >
> >

Can you provide more info:
1)What cell you're entering the formula in and What formula you're entering
2) What cell you're copying the formula to, and how exactly you desire the
copied formula (with automatically updated references after you copied it)
to be different from how it is.





"Task Lead Nicole" <TaskLeadNicole@discussions.microsoft.com> wrote in
message news:F8CA785D-0E96-4EB5-B2F8-C0271649623F@microsoft.com...
> I have 2 columns which will more or less repeat again and again but the
cells
> are linked to columns in another worksheet. Basically, I'm having to copy
> and paste my first 2 columns, go into each cells formulas and change the
> Letter and the Number of the cell it is linked to (in the formula). Excel
> automatically goes one up on the Letter but in the wrong direction and it
> doesn't go one up in the Number.
>
> It's hard to explain but is there an easy way to dictate what intervals go
> up within the formula's reference to the linked cells - in what
increments?

My columns look like this:
=Summary!F113 =IF(Summary!G113<>"","Complete","")
=Summary!H113 =IF(Summary!I113<>"","Complete","")
=Summary!J113 =IF(Summary!K113<>"","Complete","")
=Summary!L113 =IF(Summary!M113<>"","Complete","")
=Summary!N113 =IF(Summary!O113<>"","Complete","")
=Summary!P113 =IF(Summary!Q113<>"","Complete","")
=Summary!R113 =IF(Summary!S113<>"","Complete","")
=Summary!T113 =IF(Summary!U113<>"","Complete","")
=Summary!V113 =IF(Summary!W113<>"","Complete","")

....because they're being populated from another worksheet. The subsequent
columns (going on and on) in pairs like that need to be the same but with
simply the number (like 113 above) changing, going up sequentially because
these reference Rows in the other worksheet.

What I ended up doing was copying the above into a Word doc (as a table),
doing a Find and Replace for the number every time, and copying and pasting
the newly numbered 2 columns in where I wanted them in the worksheet. It
worked pretty well as a work-around.

But now I'm just curious if you can override Excel's attempt to be smart --
i.e., it was automatically up'ing the Letter in the above formulas Each Time
I copied and pasted within that worksheet. I couldn't figure out how to turn
off that function because my life would've been easier if it had even just
let me copy and paste the exact code/formulas in the columns and all I
would've had to do was change the Numbering myself manually.

Let me know what you can find. Thanks!

"Dave R." wrote:

> Can you provide more info:
> 1)What cell you're entering the formula in and What formula you're entering
> 2) What cell you're copying the formula to, and how exactly you desire the
> copied formula (with automatically updated references after you copied it)
> to be different from how it is.
>
>
>
>
>
> "Task Lead Nicole" <TaskLeadNicole@discussions.microsoft.com> wrote in
> message news:F8CA785D-0E96-4EB5-B2F8-C0271649623F@microsoft.com...
> > I have 2 columns which will more or less repeat again and again but the
> cells
> > are linked to columns in another worksheet. Basically, I'm having to copy
> > and paste my first 2 columns, go into each cells formulas and change the
> > Letter and the Number of the cell it is linked to (in the formula). Excel
> > automatically goes one up on the Letter but in the wrong direction and it
> > doesn't go one up in the Number.
> >
> > It's hard to explain but is there an easy way to dictate what intervals go
> > up within the formula's reference to the linked cells - in what
> increments?
>
>
>

Still not sure about your data layout. But if you want to prevent Excel from
helping when you copy a formula, put $ money signs in front of the part you
don't want to change. e.g. $A$1 will remain $A$1 no matter where you copy
it. $A1 will turn to $A2 if you copy it down one row and 1 through 254
columns to the right.




"Task Lead Nicole" <TaskLeadNicole@discussions.microsoft.com> wrote in
message news:AB73A24F-8A13-4D7E-A13C-D204F6052D51@microsoft.com...
> My columns look like this:
> =Summary!F113 =IF(Summary!G113<>"","Complete","")
> =Summary!H113 =IF(Summary!I113<>"","Complete","")
> =Summary!J113 =IF(Summary!K113<>"","Complete","")
> =Summary!L113 =IF(Summary!M113<>"","Complete","")
> =Summary!N113 =IF(Summary!O113<>"","Complete","")
> =Summary!P113 =IF(Summary!Q113<>"","Complete","")
> =Summary!R113 =IF(Summary!S113<>"","Complete","")
> =Summary!T113 =IF(Summary!U113<>"","Complete","")
> =Summary!V113 =IF(Summary!W113<>"","Complete","")
>
> ...because they're being populated from another worksheet. The subsequent
> columns (going on and on) in pairs like that need to be the same but with
> simply the number (like 113 above) changing, going up sequentially because
> these reference Rows in the other worksheet.
>
> What I ended up doing was copying the above into a Word doc (as a table),
> doing a Find and Replace for the number every time, and copying and
pasting
> the newly numbered 2 columns in where I wanted them in the worksheet. It
> worked pretty well as a work-around.
>
> But now I'm just curious if you can override Excel's attempt to be
smart --
> i.e., it was automatically up'ing the Letter in the above formulas Each
Time
> I copied and pasted within that worksheet. I couldn't figure out how to
turn
> off that function because my life would've been easier if it had even just
> let me copy and paste the exact code/formulas in the columns and all I
> would've had to do was change the Numbering myself manually.
>
> Let me know what you can find. Thanks!
>
> "Dave R." wrote:
>
> > Can you provide more info:
> > 1)What cell you're entering the formula in and What formula you're
entering
> > 2) What cell you're copying the formula to, and how exactly you desire
the
> > copied formula (with automatically updated references after you copied
it)
> > to be different from how it is.
> >
> >
> >
> >
> >
> > "Task Lead Nicole" <TaskLeadNicole@discussions.microsoft.com> wrote in
> > message news:F8CA785D-0E96-4EB5-B2F8-C0271649623F@microsoft.com...
> > > I have 2 columns which will more or less repeat again and again but
the
> > cells
> > > are linked to columns in another worksheet. Basically, I'm having to
copy
> > > and paste my first 2 columns, go into each cells formulas and change
the
> > > Letter and the Number of the cell it is linked to (in the formula).
Excel
> > > automatically goes one up on the Letter but in the wrong direction and
it
> > > doesn't go one up in the Number.
> > >
> > > It's hard to explain but is there an easy way to dictate what
intervals go
> > > up within the formula's reference to the linked cells - in what
> > increments?
> >
> >
> >