Copying and adjusting formulas

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

Hi
Firstly thanks for many tips picked up in this ng - I've learnt a lot.

Copying and adjusting formulas

26 sheet workbook (Excel 2000) with a sheet for each weeks figures broken
down departmentally and totalled for the week. From sheet 2 onwards the
sheet has year to date being current weeks figures + previous year to
date.
This was originally in .123 and as you copied from one sheet to other
sheets the formula adjusted the sheet name - in Excel it keeps the formula
without adjustment.
I find this weird (and very long winded as each sheets formula will need
changing). Am I missing something or is there a way to do this better or
what (the .123 file is corrupt and that is why I'm moving it and I
_thought_ it wouldn't take long <G>)

TIA - any help appreciated and will happily clarify anything. Spreadsheet
competent but not excel!
 
Hi Nigel,

If you highlight all the sheets that you want to put the formula in (by holding down 'shift' or 'Ctrl' and selecting them). Now when you input the formula in one sheet this will be replicated in each highlighted sheet.

Hope I understood your question correctly!

DN
 
Hi Nigel,

If you highlight all the sheets that you want to put the formula in
(by holding down 'shift' or 'Ctrl' and selecting them). Now when you
input the formula in one sheet this will be replicated in each highlighted sheet.

Hope I understood your question correctly!

DN
Click to expand...
Agreed Dave and thanks but it doesn't adjust the sheet name part of the
formula for each sheet
Sheet 2 needs to reference the value on sheet 1, sheet 3 the value on
sheet 2 etc etc
Cheers
 
Out of curiosity does 1-2-3 allow you to rearrange sheetnames,
and to rename them. How are you coding the next/previous
sheet in 1-2-3.
 
Out of curiosity does 1-2-3 allow you to rearrange sheetnames,
and to rename them. How are you coding the next/previous
sheet in 1-2-3.
Click to expand...
Hi David
Don't think move is an option in .123 tho they can be
renamed (I guess I'd try to plan things so that moving
wasn't necessary).

Coding .123 eg
Sheet 2 formula - +1:L7+D7
Sheet 3 formula - +2:L7+D7
and the formula adjustment takes place with a standard copy

Coding Excel eg
Sheet 2 formula - =+D7+'1'!L7
Sheet 3 formula - =+D7+'1'!L7
so no adjustment with copy (which as a 123 user seems very
weird!)

Happy to learn and any suggestions welcome
 
Hi David
Don't think move is an option in .123 tho they can be
renamed (I guess I'd try to plan things so that moving
wasn't necessary).

Coding .123 eg
Sheet 2 formula - +1:L7+D7
Sheet 3 formula - +2:L7+D7
and the formula adjustment takes place with a standard copy

Coding Excel eg
Sheet 2 formula - =+D7+'1'!L7
Sheet 3 formula - =+D7+'1'!L7
so no adjustment with copy (which as a 123 user seems very
weird!)

Happy to learn and any suggestions welcome
Click to expand...

For the benefit of anyone watching this in the hope of
finding the answer (as I've done many times) ...
John Walkenbach's excellent book Excel 2000 formulas
p618/19/20
If you want more info let me know (crazy way that excel
works tho)
 
Back
Top