Hope someone can assist with a problem I have never previously encountered.

I am trying to add a column to a spreadsheet with the formula '=0&<Cell details', which returns the contents of the chosen cell with a '0' as the first digit of the updated cell. E.g. in column G2 I input '=0&A2', which returns '0123' if A2 is '123'.

This works well however when I copy and paste the formula to each row then the formula returns the value from the first cell updated. E.g. Using the example above all columns updated would return '0123' regardless of the values in cells A3, A4 etc.

I have checked the formula's that are being copied and these are all correct?

NB: This problem is also occuring when I use VLOOKUP tables!

I'd love to know what I am doing wrong and would appreciate any guidance.

Thanks
Ed

Subsequent info:

I have just opned up the files I am working on using a different PC (a desktop as opposed to the laptop I normally use) and am not experiencing these problems.

I'm assuming that this will therefore be something to do with the set up of Excel on my laptop but do not know enough about excel to be able to change the settings!

Thanks in anmticipation as I don't fancy lugging the desktop around when I need to work from home!

"I have checked the formula's that are being copied and these are all correct?"

Do you mean to say that the formula in row 3 shows as '=0&A3' or as '=0&A2'? If it shows that later then it should show 0 and the contents of A2.

Copy and Paste is the hard way.

The easy way to copy down your formula is once entered in G2 you can simply wave over the bottom right hand corner of the cell so the cursor changes to a solid black + (crosshair), then just click and drag it down the column. The A2 will change incrementally to A3, A4...and so on.
Also, if all adjacent cells are filled (column F that is)then you can simply double click the black crosshair in G2 to "Fill down" the formula to the first adjacent empty cell (in column F). (There is more to that lesson but not for this exercise).

Alternately, if you just want the 3 digit values in column A to have a leading zero then you can simply select column A, go to Format, Cells, Custom and type in the box "0###" (without the quotation marks) or if some values only had one or two digits then enter "0000" so all values will have 4 digits with leading zeros if 4 digits or less.

HTH
Bryan

Bryan,

Thanks for the thorough response.

To clarify the cell's are showing:

G2 = 'O&A2'
G3 = '0&A3'
G4 = '0&A4' etc etc

But the value's in the all cells in column G return the same result as G2 even though the cells in column A have different values.

I have also tried using the 'crosshair version' though have to be careful here as the spreadsheet is generally filtered and doubleclicking the 'crosshair' updates the cell's that are 'filtered out'.

It does not matter whether I copy and paste or use the 'crosshair' method I still get the same results!

Any further input would be appreciated - thanks!

I think I would have to see it to understand how the problem exists. I am unable to duplicate the problem no matter how I try.

Can you make a copy of the spreadsheet or an example of it available for download or otherwise make it available?

Bryan

Bryan,

Thanks for the offer.

For some reason my laptop is now doing what I would expect and the problem doesn't exist any more!?!

I have no idea what the problem was but am just happy to see it gone so will try not too think too hard about it!

Thanks again for your kind offer

Ed

Ed,

I am glad the problem has resolved itself for you.

Bryan

You may find that in Tools /Options /Calculation you had the setting on Manual.
I can definitely replicate your problem if I set my Calculation to Manual.
As soon as you set it back to Automatic the correct answers are shown as soon as you Paste
Regards
Malcolm