Microsoft Excel

  Tulips 21:39 24 Jun 07
Locked

How do I get from =SUM('W1'!AC$24:AD$24) to =SUM('W38'!AC$24:AD$24) without deleteing W1 and replacing it with W2 and W3 and so on and so on until I get to 38. I have another 60 columns to perform and would like an easier way.

  VoG II 21:45 24 Jun 07

Try Edit > Replace.

  Tulips 21:56 24 Jun 07

isn't that just like deleting W1 and replacing it with W2 W3 W4 etc manually, is there a way like holding Ctrl and dragging or copying and pasting or some such magic ?

  daba 00:04 25 Jun 07

I'm sure there's a better way, and I'll try to figure it, but I think its going to need to use INDIRECT function.

I've got it as follows:-

Insert 2 new columns, I've used A and B in this example : you can hide them later if you wish.

In column A, Row 1, put ="'W" & ROW() & "'!AC$24" make sure all them single and double quote marks are there.

In column B, Row 1, put ="'W" & ROW() & "'!AD$24"

You will see that Excel evaluates these as 'W1'|AC$24 and 'W1'|AD$24

What we have done is use the current row number to create your sheet number references, using ROW()

Now your formula for the sum is just =INDIRECT(A1)+INDIRECT(B1)

Drag the 3 columns down to create your 38 totals.

If you inadvertantly reference a non-existant sheet, then you get a #REF error.

A bit cludgy, but it works.


HTH

  Tulips 19:21 26 Jun 07

Cheers daba, as you said, a bit lumpy but does the job, sorted.

  daba 00:45 27 Jun 07

You're very welcome - lumpy it may be, but so often is the case with simple spreadsheet operations.

I hold my hands up to avoiding Macros or VBA script, when a cludgy "cell calculation" solution is self-maintaining.

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

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac