You could have used this, too:
row()-4
Keith W. wrote:
>
> Roger, you're right, that worked!
>
> Thank you Dave and Roger.
>
> "Roger Govier" wrote:
>
> > Hi Keith
> >
> > Instead of ROW(), use ROW(A1) in Dave's formula, then copy down.
> >
> > --
> > Regards
> >
> > Roger Govier
> >
> >
> > "Keith W." <(E-Mail Removed)> wrote in message
> > news:A09E39F9-5713-4688-880A-(E-Mail Removed)...
> > >I did exactly what you said and it worked great. The only problem is,
> > >I have
> > > a bunch of titles and headers for the columns that I need to keep, so
> > > I have
> > > to start in row 5 (it is cell Y5 for example). Doing what you
> > > explained
> > > started with 'Site 5'!, when it needs to be 'Site 1'!. How do I make
> > > row 5
> > > start with 'Site 1'!??? Do I put in a minus 4 (- 4) in that formula
> > > you gave
> > > me somewhere to get it to start with 1 instead of 5?
> > >
> > > Thanks for all the help to this point.
> > >
> > > "Dave Peterson" wrote:
> > >
> > >> I'd do this.
> > >>
> > >> I'd put this in A1 (or whatever column).
> > >> ="$$$='Site "&ROW()&"'!$BL$116"
> > >>
> > >> Then drag down as far as you need.
> > >>
> > >> Then select that range
> > >> edit|copy
> > >> edit|paste special|values
> > >>
> > >> and finally
> > >> with that range still selected:
> > >> edit|replace
> > >> what: $$$=
> > >> with: =
> > >> replace all
> > >>
> > >>
> > >>
> > >> Keith W. wrote:
> > >> >
> > >> > I really hope someone can please help me...
> > >> >
> > >> > I have a spreadsheet with 119 separate worksheets (Site 1 - Site
> > >> > 119) and
> > >> > one "master" worksheet. The "master" worksheet is basically the
> > >> > combination
> > >> > of specific data from each of the 119 sheets, so it has 119 rows
> > >> > (and about
> > >> > 45 columns). In the master, I have a formula that refers to the
> > >> > separate
> > >> > external worksheets (ie, each row in the "Master" (rows 1-119) will
> > >> > refer to
> > >> > sheet 1 - sheet 119. To save myself hours of copying the same
> > >> > forumla and
> > >> > changing the sheet number, I am trying to drag down the formula to
> > >> > keep the
> > >> > same cell number, but change the referenced sheet number...
> > >> >
> > >> > SO, the formula in the master in row 1 is:
> > >> >
> > >> > ='Site 1'!$BL$116
> > >> >
> > >> > I want to drag it down so the external worksheet number changes,
> > >> > but the
> > >> > cell doesn't, so row 2 will be:
> > >> >
> > >> > ='Site 2'!$BL$116
> > >> >
> > >> > and row 3 will be:
> > >> >
> > >> > ='Site 3'!$BL$116
> > >> >
> > >> > all the way to row 119 which will be:
> > >> >
> > >> > ='Site 119'!$BL$116
> > >> >
> > >> > But when I drag, it just drags 'Site 1'! all the way down.
> > >> >
> > >> > Can anyone help me? Is this even possible or do I have to type in
> > >> > the
> > >> > number 119 times (multiplied by about 45 columns I have to do this
> > >> > to).
> > >> >
> > >> > Thanks!
> > >>
> > >> --
> > >>
> > >> Dave Peterson
> > >>
> >
> >
> >
--
Dave Peterson
|
Similar Threads
