Autofill upwards!!!!!

Try this:

Select the column with Store Names

From the Excel main menu:
<edit><goto>...Click the [special cells] button
Check: Blanks...Click the [OK] button
(Now all of the blank cells are selected)

Press the [=] key
Press the down arrow ONE time
Hold down the [ctrl] key and press [enter]

Now the formerly blank cells should display the correct Store Name
Select that column of cells
<edit><copy>
<edit><paste special><values>

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Paul Sheppard" wrote:

>
> I have a spreadsheet with 3 columns of data as follows
>
> Col 1 Col 2 Col 3
> Store Stock Item Sales
> Item 1 22
> Item 2 322
> Item 3 16
> Store 1 360
> Item 1 11
> Item 2 122
> Item 3 46
> Item 4 31
> Store 2 210
> etc
>
> How can I easily get the store name to autofill upwards so it appears
> alongside each stock item, without copy/paste or dragging each one
> individually, so that I can then run a pivot table on the data
>
> Note that each store does not necessarily carry the same number of
> stock items
>
> Thanks in advance
>
> Paul
>
>
> --
> Paul Sheppard
>
>
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> View this thread: http://www.excelforum.com/showthread...hreadid=547424
>
>

You can use a formula, but you will have to start from the bottom of the sheet.

Steps:

Insert a column before Col 1
Go to the end of the list
Assume that row 100 is the last row in your sheet enter in Cell A100 the
formula :

=If(C100="",B100,B101)
Copy this formula upwards and you will have the Store ID before each item.

HTH

Hans

"Paul Sheppard" wrote:

>
> I have a spreadsheet with 3 columns of data as follows
>
> Col 1 Col 2 Col 3
> Store Stock Item Sales
> Item 1 22
> Item 2 322
> Item 3 16
> Store 1 360
> Item 1 11
> Item 2 122
> Item 3 46
> Item 4 31
> Store 2 210
> etc
>
> How can I easily get the store name to autofill upwards so it appears
> alongside each stock item, without copy/paste or dragging each one
> individually, so that I can then run a pivot table on the data
>
> Note that each store does not necessarily carry the same number of
> stock items
>
> Thanks in advance
>
> Paul
>
>
> --
> Paul Sheppard
>
>
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> View this thread: http://www.excelforum.com/showthread...hreadid=547424
>
>

Thanks for the feedback....I'm glad that worked for you.

Yes...that method also works for filling from the top.
In that case, though, you'd use the UP arrow in the formula, instead of the
DOWN arrow.

***********
Regards,
Ron

XL2002, WinXP


"Paul Sheppard" wrote:

>
> Thanks Ron, that worked perfectly it will save me a bundle of time every
> month when I need to manipulate the data for my reports
>
> Would that also work if the store name was at the top and i wanted to
> fill down?
>
> Ron Coderre Wrote:
> > Try this:
> >
> > Select the column with Store Names
> >
> > From the Excel main menu:
> > <edit><goto>...Click the [special cells] button
> > Check: Blanks...Click the [OK] button
> > (Now all of the blank cells are selected)
> >
> > Press the [=] key
> > Press the down arrow ONE time
> > Hold down the [ctrl] key and press [enter]
> >
> > Now the formerly blank cells should display the correct Store Name
> > Select that column of cells
> > <edit><copy>
> > <edit><paste special><values>
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "Paul Sheppard" wrote:
> >
> > >
> > > I have a spreadsheet with 3 columns of data as follows
> > >
> > > Col 1 Col 2 Col 3
> > > Store Stock Item Sales
> > > Item 1 22
> > > Item 2 322
> > > Item 3 16
> > > Store 1 360
> > > Item 1 11
> > > Item 2 122
> > > Item 3 46
> > > Item 4 31
> > > Store 2 210
> > > etc
> > >
> > > How can I easily get the store name to autofill upwards so it
> > appears
> > > alongside each stock item, without copy/paste or dragging each one
> > > individually, so that I can then run a pivot table on the data
> > >
> > > Note that each store does not necessarily carry the same number of
> > > stock items
> > >
> > > Thanks in advance
> > >
> > > Paul
> > >
> > >
> > > --
> > > Paul Sheppard
> > >
> > >
> > >
> > ------------------------------------------------------------------------
> > > Paul Sheppard's Profile:
> > http://www.excelforum.com/member.php...o&userid=24783
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=547424
> > >
> > >
>
>
> --
> Paul Sheppard
>
>
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> View this thread: http://www.excelforum.com/showthread...hreadid=547424
>
>