Copy & paste / drag down formulas in Excel

wonski

Hi All,

Need a bit of advice in Excel, which I am familiar with, but not a pro obviously.

There probably is a simple solution that I just never had a chance to use.

See below - I tried to make it simple :



This data in Sheet1 contains no formulas nor references. This is imported from another application (copied to clipboard) and pasted into Sheet1. I do remove duplicates and other useless info, but all I need is there.




Sheet2 is a simple form that I need to print off weekly, all I need is up to date data imported into Sheet1 to show up in Sheet2. The problem starts when I try to copy/paste or drag down this simple form as I have 50 - 150 products/positions to cover:



Is there any way to copy&paste without typing it in so the reference doesn't go from 1 to 7, but 1 to 2, then 3 etc? I hope I am explaining it clearly enough and asking in the right place.

Many thanks, I've done this in OpenOffice to give you an idea of the problem, but the final one will be done in Excel btw.

I would ideally like to see this:



Any ideas? Thanks.

Bosh

If I'm following you correctly you need you us the fill tool. (I'm using Excel 2010)

Put 1 in the first cell, 2 in the next, then select each using ctrl & click and continue down in the same fashion on each cell that'll contain the next number for as far down as you require.

When each required cell is selected go to Fill button on the Home ribbon.

Click the wee arrow, series, Columns, Linear & OK.

This should fill each cell sequentially.

There's probably a more elegant way of doing it though!

wonski

I tried different fill options and none worked properly. I already asked few people at work and no luck so far. I think we all need an Excel training

Thanks anyway.

golfcaptain

Does the position cell have to reference the first sheet (cell D1=Sheets!A1) or can it just be numbered (cell D1=1)? If it can just be numbered you can put 1 in cell D1, 2 in cell D7, select cells D1:D12 or A1:D12 and drag down. Other than that the only other suggestions I'd have would be to set up a a template (easy) or create a macro (bit more learning involved).

REM76

It's important to be able to understand a formula in Excel, their shortcuts and tools are largely useless if you don't understand the construct of a formula.

If you don't, the tools won't work and you won't understand why.

They might seem complicated at first, but they really aren't.

I would spend some time understanding the "$" symbol and cell references. Once you understand this, the rest will be very straight-forward.

wonski

golfcaptain wrote: »

Does the position cell have to reference the first sheet (cell D1=Sheets!A1) or can it just be numbered (cell D1=1)? If it can just be numbered you can put 1 in cell D1, 2 in cell D7, select cells D1:D12 or A1:D12 and drag down. Other than that the only other suggestions I'd have would be to set up a a template (easy) or create a macro (bit more learning involved).

I could try to import all data onto the same sheet (Sheet2), no big deal.

What bothers me is that every time I try to repeat the action (by pasting or dragging) the reference/formula in the cell changes by the number of cells copied (7 in my case). Maybe I approached it wrong way and should start from moving all data I need to the same sheet and see what happens.

I will look into templates, macros seems a bit to complicated at this stage, though.

wonski

REM76 wrote: »

It's important to be able to understand a formula in Excel, their shortcuts and tools are largely useless if you don't understand the construct of a formula.

If you don't, the tools won't work and you won't understand why.

They might seem complicated at first, but they really aren't.

I would spend some time understanding the "$" symbol and cell references. Once you understand this, the rest will be very straight-forward.

Can't agree more. Tell it to my boss, after I missed two huge excel training sessions at work, he said I will get the next one.

I should have messed up a lot of reports in the past and would get one of those sessions sooner :rolleyes:

That particular form would save me 1-2 hours a week as it is normally printed from Word file which is updated manually and all updates are done based by emails from other teams. You basically need to type in new data into Word document while all old data is still there (unless you spot and delete it). It would also include location in the warehouse which normally is being typed in manually by checking the very same info that is in sheet1. Life saver if it works, just need to make it work.

GrumpyMe

Put 1 in DI.
Select D1:D7
Catch the fill handle and fill D8 - It will have 2 in it.
Not sure what you are trying to achieve but if you just want "2" in D2 the above will work!
If you want 2 in D8 and 3 in D15 and 4 in D22 and 5 in D29 etc just continue on at line 3 above.
Is that all you are trying to achieve?

GrumpyMe

Put
=INDEX(Sheet1!$B$1:$B$11,MATCH(Sheet2!D1,Sheet1!$A$1:$A$11,0))
in A4 of Sheet2 and it will get the product code for the ref number on D1

Put
=INDEX(Sheet1!$C$1:$C$11,MATCH(Sheet2!D1,Sheet1!$A$1:$A$11,0))
in C4 of Sheet2 and it will get the product name for the ref number on D1

THEN...

Select A1:D7
Grab the fill handle and fill down to A35:D35 and it will automatically get the product code and produce name for cells D1, D8, D15, D22 and D29

wonski

Thanks All, I will give it a go tomorrow and will let you know.

I appreciate you taking time to look at it.

REM76

The other tip I would give you is the classic "sanity check". Once your formula is done, have a look at the result and really think 'does it sound plausible'? Hypothetical example, pricing the cost of an onion. If it comes out as 5 euro, you know instantly your calculations are garbage.

Sometimes the result can be ridiculous, remember this is what people will see first. I often enlist a colleague for the sanity check, and do same for them. Works fantastically well.

I usually guess beforehand, do the workings and see if it looks realistic. If not, there's something mad in the formulas.


Don't worry about the formal excel training. I've done loads of them, and they are largely pointless. You might learn one or two tricks in the 3 hour course.

Get to know the mechanics of the formulas and you will be fine. The Excel examples are really good, you don't need formal training.


One last tip: BETTER LATE THAN WRONG

Don't distribute ANYTHING until you are sure it is correct. Look at it three times. Have a coffee, then look again. Then press "send". One nonsense calculation will destroy confidence in your calculations forever.

I used to send daily investment valuations, taking almost directly from a bank feed. One morning I reported a movement of -1,000,000. I had done everything right, but the bank had not updated or some such problem.

Obviously it was garbage. My defence was even worse, that I had done everything correctly. it just showed them that I had no idea what I was talking about and my reputation was shot until I left. If I had just asked someone else, "does this look right" before sending, it would have been fine.

Hey, I was young, but you need not learn these harsh lessons! I've been using Excel as an accountant for twenty years and am happy to work with you on all this kind of stuff, or on any accountancy problems. Just PM me any questions or concerns you have, I will be happy to help you. I had one manager help me for a few years and it was a massive help.

Bosh

wonski wrote: »

I tried different fill options and none worked properly. I already asked few people at work and no luck so far. I think we all need an Excel training

Thanks anyway.

Hmmm,

Looking at the pic you posted; Did you put 1 in D1, 2 in D8, then select D22 & select each relevant cell (ctrl click) from there on & do the series fill?

Tried it here & works fine..

wonski

Bosh wrote: »

Hmmm,

Looking at the pic you posted; Did you put 1 in D1, 2 in D8, then select D22 & select each relevant cell (ctrl click) from there on & do the series fill?

Tried it here & works fine..

1 in D1 and 2 in D8 are not constant