Dragging a Formula & Basic Setting Changes

Hi, I have two issues that I've run into with Excel that have me stumped.

1) I am trying to mirror a row of values (via formulas) from one sheet to
another. I do not want to simply cut and paste b/c I am udating the sheets.
Typically I copy one cell and then drag it across the row to fill in the
remaining cells. Right now, however, the worksheet is simply repeating the
same value instead of inputting 'value + 1 (column)'.

Ex. I want to have a mirror of cells B2-B6 so I input on the new sheet, C5
= 'Orig. Sheet'B2. I then drag the corner of C5 across the row to try to
fill in with B3, B4, B5, B6 from the original sheet....Instead what I'm
getting is B2, B2, B2, B2.....

Any idea what setting might have changed or how to get it back to the way I
would prefer?

That leads me to my second question which is more of a general Excel
question...

2) Excel seems to sometimes change settings when I open and close it. For
instance, I always prefer to "edit directly in cell", however, many times I
will have to click this check box each time I open a file (even the same one
that's been saved the other way). I am confused as to why it keeps
defaulting back to a setting and forces me to manually change it. Is it
possible that there is a bug in my version of the program?

Thanks for any ideas.

I dont really understand what is happening in your first issue.

is the results of the equations which are wrong?
are equations themselves wrong?

in the second issue it is probably that the first progeam you open each day
has different defaults.
excel uses as its defaults the settings of the first file opened.
You can tell Excel to open an initial file with the defaults you want if you
just open Excel. see help on "customise how excel starts"

"chande00" wrote:

> Hi, I have two issues that I've run into with Excel that have me stumped.
>
> 1) I am trying to mirror a row of values (via formulas) from one sheet to
> another. I do not want to simply cut and paste b/c I am udating the sheets.
> Typically I copy one cell and then drag it across the row to fill in the
> remaining cells. Right now, however, the worksheet is simply repeating the
> same value instead of inputting 'value + 1 (column)'.
>
> Ex. I want to have a mirror of cells B2-B6 so I input on the new sheet, C5
> = 'Orig. Sheet'B2. I then drag the corner of C5 across the row to try to
> fill in with B3, B4, B5, B6 from the original sheet....Instead what I'm
> getting is B2, B2, B2, B2.....
>
> Any idea what setting might have changed or how to get it back to the way I
> would prefer?
>
> That leads me to my second question which is more of a general Excel
> question...
>
> 2) Excel seems to sometimes change settings when I open and close it. For
> instance, I always prefer to "edit directly in cell", however, many times I
> will have to click this check box each time I open a file (even the same one
> that's been saved the other way). I am confused as to why it keeps
> defaulting back to a setting and forces me to manually change it. Is it
> possible that there is a bug in my version of the program?
>
> Thanks for any ideas.

In the first scenario, dragging the lower right hand corner of the box just
takes the exact info that I have in that cell and copies it to all of the
cells that I drag it to. What I am trying to do is have the cells correspond
with the info I am trying to replicate. I have listed a more detailed
description below.

Say I have cells A1 A2 A3 A4 A5 A6 on what we'll call sheet 1

I want the values of A1-A6 on a new sheet in row 'C'. So in C1 I input
='sheet1'!A1
.. Then typically I would drag the bottom corner of the cell and it would
fill in the values as listed: C2=A2 C3=A3 C4=A4 C5=A5 C6=A6 . Now, however,
it is just filling like this: C2=A1 C3=A1 C4=A1 C5=A1 C6=A1 . I hope that
this helps explin the issue a little better. Thanks
"bj" wrote:

> I dont really understand what is happening in your first issue.
>
> is the results of the equations which are wrong?
> are equations themselves wrong?
>
> in the second issue it is probably that the first progeam you open each day
> has different defaults.
> excel uses as its defaults the settings of the first file opened.
> You can tell Excel to open an initial file with the defaults you want if you
> just open Excel. see help on "customise how excel starts"
>
> "chande00" wrote:
>
> > Hi, I have two issues that I've run into with Excel that have me stumped.
> >
> > 1) I am trying to mirror a row of values (via formulas) from one sheet to
> > another. I do not want to simply cut and paste b/c I am udating the sheets.
> > Typically I copy one cell and then drag it across the row to fill in the
> > remaining cells. Right now, however, the worksheet is simply repeating the
> > same value instead of inputting 'value + 1 (column)'.
> >
> > Ex. I want to have a mirror of cells B2-B6 so I input on the new sheet, C5
> > = 'Orig. Sheet'B2. I then drag the corner of C5 across the row to try to
> > fill in with B3, B4, B5, B6 from the original sheet....Instead what I'm
> > getting is B2, B2, B2, B2.....
> >
> > Any idea what setting might have changed or how to get it back to the way I
> > would prefer?
> >
> > That leads me to my second question which is more of a general Excel
> > question...
> >
> > 2) Excel seems to sometimes change settings when I open and close it. For
> > instance, I always prefer to "edit directly in cell", however, many times I
> > will have to click this check box each time I open a file (even the same one
> > that's been saved the other way). I am confused as to why it keeps
> > defaulting back to a setting and forces me to manually change it. Is it
> > possible that there is a bug in my version of the program?
> >
> > Thanks for any ideas.

II sent a response earlier but don't see it now.

what version of Excel do you have?
in 2002 when you drag copy there is a little clipboard looking icon which
lets you select some of the character for what you are copying.

Also are you getting values or just the equations. the second sheet might
be formated for text only and converting the equation to text

"chande00" wrote:

> In the first scenario, dragging the lower right hand corner of the box just
> takes the exact info that I have in that cell and copies it to all of the
> cells that I drag it to. What I am trying to do is have the cells correspond
> with the info I am trying to replicate. I have listed a more detailed
> description below.
>
> Say I have cells A1 A2 A3 A4 A5 A6 on what we'll call sheet 1
>
> I want the values of A1-A6 on a new sheet in row 'C'. So in C1 I input
> ='sheet1'!A1
> . Then typically I would drag the bottom corner of the cell and it would
> fill in the values as listed: C2=A2 C3=A3 C4=A4 C5=A5 C6=A6 . Now, however,
> it is just filling like this: C2=A1 C3=A1 C4=A1 C5=A1 C6=A1 . I hope that
> this helps explin the issue a little better. Thanks
> "bj" wrote:
>
> > I dont really understand what is happening in your first issue.
> >
> > is the results of the equations which are wrong?
> > are equations themselves wrong?
> >
> > in the second issue it is probably that the first progeam you open each day
> > has different defaults.
> > excel uses as its defaults the settings of the first file opened.
> > You can tell Excel to open an initial file with the defaults you want if you
> > just open Excel. see help on "customise how excel starts"
> >
> > "chande00" wrote:
> >
> > > Hi, I have two issues that I've run into with Excel that have me stumped.
> > >
> > > 1) I am trying to mirror a row of values (via formulas) from one sheet to
> > > another. I do not want to simply cut and paste b/c I am udating the sheets.
> > > Typically I copy one cell and then drag it across the row to fill in the
> > > remaining cells. Right now, however, the worksheet is simply repeating the
> > > same value instead of inputting 'value + 1 (column)'.
> > >
> > > Ex. I want to have a mirror of cells B2-B6 so I input on the new sheet, C5
> > > = 'Orig. Sheet'B2. I then drag the corner of C5 across the row to try to
> > > fill in with B3, B4, B5, B6 from the original sheet....Instead what I'm
> > > getting is B2, B2, B2, B2.....
> > >
> > > Any idea what setting might have changed or how to get it back to the way I
> > > would prefer?
> > >
> > > That leads me to my second question which is more of a general Excel
> > > question...
> > >
> > > 2) Excel seems to sometimes change settings when I open and close it. For
> > > instance, I always prefer to "edit directly in cell", however, many times I
> > > will have to click this check box each time I open a file (even the same one
> > > that's been saved the other way). I am confused as to why it keeps
> > > defaulting back to a setting and forces me to manually change it. Is it
> > > possible that there is a bug in my version of the program?
> > >
> > > Thanks for any ideas.

Easy one first...

I think that this is one of those settings that's stored in the windows
registry. If your changes aren't being saved, it may be worthwhile to talk to
your IT folks to find out why you can't save to the registry.

Maybe you're not an administrator for your pc (or you're logged on as a user who
doesn't have those rights).

But until you get it fixed, you could put a workbook in your XLStart folder.
That means the workbook will open each time you start excel.

The code could turn the setting off and close the workbook.

Option Explicit
Sub auto_open()
Application.EditDirectlyInCell = True
ThisWorkbook.Close savechanges:=False
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

The more difficult question...

#1. Are you sure the formulas all look the same? Maybe the formulas are
different, but the formulas have recalculated (yet). Tools|Options|Calculation
tab. (Make sure Automatic is checked.)



chande00 wrote:
>
> Hi, I have two issues that I've run into with Excel that have me stumped.
>
> 1) I am trying to mirror a row of values (via formulas) from one sheet to
> another. I do not want to simply cut and paste b/c I am udating the sheets.
> Typically I copy one cell and then drag it across the row to fill in the
> remaining cells. Right now, however, the worksheet is simply repeating the
> same value instead of inputting 'value + 1 (column)'.
>
> Ex. I want to have a mirror of cells B2-B6 so I input on the new sheet, C5
> = 'Orig. Sheet'B2. I then drag the corner of C5 across the row to try to
> fill in with B3, B4, B5, B6 from the original sheet....Instead what I'm
> getting is B2, B2, B2, B2.....
>
> Any idea what setting might have changed or how to get it back to the way I
> would prefer?
>
> That leads me to my second question which is more of a general Excel
> question...
>
> 2) Excel seems to sometimes change settings when I open and close it. For
> instance, I always prefer to "edit directly in cell", however, many times I
> will have to click this check box each time I open a file (even the same one
> that's been saved the other way). I am confused as to why it keeps
> defaulting back to a setting and forces me to manually change it. Is it
> possible that there is a bug in my version of the program?
>
> Thanks for any ideas.

--

Dave Peterson