Excel formula needed

  Willow12 14:35 12 Aug 2003
Locked

I am creating a fairly straightforward spreadsheet on Excel for a new treasury role that I have undertaken. I have sorted out my columns the way that I need them including some simple formulas i.e. =E2-D3+C3 for the balance sheet. The problem I have is that I am going to have a new Sheet for each month from August 2003 - July 2004. Is there a formula available which allows me to:-

1. Carry the balance from a previous sheet to the new one.

2. Import the Labels and Formulas from previous sheets to new ones as I don't want to create a brand new sheet from scratch (notwithstanding copy and paste) for each month if I don't need to.

I have already renamed 12 worksheets for the months of the year but as yet only have anything on August 2003.

I am very new to using Excel in this way having previously only used it as a simple chart with no formulas used at all!

Any help would be greatly appreciated

Willow12

  Taff36 14:49 12 Aug 2003

A couple of tips:

Set up your first sheet with all your formulas and formatting. When you`re happy with that one simply copy it. (Right click the shhet tab and select MOVE OR COPY.) A duplicate sheet will appear - simply double click it to edit the name to MASTER & press ENTER KEY. Now you should have a blank sheet that you can copy again and rename to the months you want.

Carrying the balances is even easier. In the cell you are bringing forward your figures to type

= then using your mouse click the previous sheet tab, select the appropriate cell and press the ENTER KEY again. The formula will write itself and the figure from the previous sheet will appear.

  Peverelli 14:58 12 Aug 2003

One way: Start with a single sheet. Right click on the sheet's tab (at the botttom of the page) and select 'Move or Copy'. In the box that appears select 'Move To End' & tick 'Create a Copy'. The new sheet should have the same formatting & formulii. Repeat as necessary, then rename the new sheets 'Sept', 'Oct' etc.

Second way: Wait for the expert, VoG ;O)

  Willow12 15:18 12 Aug 2003

Thanks both. I thought that there would be a formulaic way (I may have just made that word up, I am not sure!) but as your way works fine I shall go with that.

I'll keep this open for a short while in case anyone (perhaps called VoG!) has a purer way of reaching the same point.

Thanks again

Willow12

  VoG II 15:28 12 Aug 2003

Say your subtotal is in A1 on sheet Jul 03

You can "import" this onto another sheet (say Aug03) using the formula

='Jul 03'!A1

  Mike D 15:30 12 Aug 2003

There is a way!!!

In the same workbook you would have a sheet for each month (using the tabs at the bottom of the screen. If, for example your closing balance for month 1 was in cell A24 on the first sheet, then your opening balance for Month 2 (sheet 2) would be =SUM('Month 1'!A24) and so on, increasing by one month for each new sheet.

Mike D

  Willow12 18:51 12 Aug 2003

Cheers everybody. I nearly had this one right then. I was putting in ="August 2003"!E11 thus using " where I should have used '.

Like I said this is all new to me.

Thanks again to everybody who took the time to help out.

Willow12

  daba 23:27 13 Aug 2003

This has cropped up before, and many people seem to do it.

You have used the formula "=SUM('Month 1'!A24)" where a simple "='Month 1'!A24 would suffice.

You only need to use SUM when you want to SUMmate (i.e. Add Together) a range of cells, as in "=SUM(A1:A4)", which adds all the values in all the cell range A1 to A4, that is A1+A2+A3+A4.

When you only want to 'carry forward' a value, as in Willow12's question, you only need to refer back to the single cell, no summation is required.

A simpler case would be "=A1" in a cell would result in the cell displaying the same value as in cell A1.

"Why would you want to do that ?", i hear you ask. Well you may want to show the result ina different format, for example a conveniently rounded-down result (setting the cell to display numbers to xx decimal places automatically rounds the answer to your chosen number of places).

Another example:- say cell A1 is a date, then you could set cell B1 format to be 'custom' as type "ddd". Then if you entered in B1, "=A1", cell B1 would display the day of the week of the date in A1 as the predefined days, like "Wed". All very usefull stuff

If you have used =SUM in all your formulas, undoubtedly they will have become more complex than they need to be, and will have slowed down the evaluation of them a bit.

  Willow12 23:36 13 Aug 2003

That information is excellent and certainly explains when and when not to SUM for me.

I am currently making Excel my new topic to really gem up on but have only just started. It is one thing that I can practice at work without the boss finding out! ;)

At least I will know that there is another expert to call up on when the mighty VoG takes his hols!

Cheers

Willow12

  daba 23:46 13 Aug 2003

thanks for the vote Willow, but i'm by no means an expert, although i do actually LIKE excel, much to the disgust of our IT dept.

VoG's name just keeps popping up all the time with excel answers, and he's so quick, too.

i have made excel do some pretty complex stuff in the past, and usually i've had to 'play and learn' to get it to do what i want, which i believe is a better way of learning than any text-book, although sometimes the results aren't usually the best way of doing things.

daba

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

50 best online Adobe XD tutorials

iPad Pro 10.5in (2017) review

Comment connecter un MacBook à une TV ?