I need to sum the last column - is there a formula for this

[Guest]

I am working with two worksheets. Sheet 1 contains data ie A2, B2 etc.
Sheet2 needs to reference the last column with data on row 2 and multiply by
2%. When I add another column of data to Sheet 1 I want the formula on Sheet
2 to automatical know to look at the last column of data without me having to
change it manually.

 

[Guest]

try:

=INDIRECT("Sheet1!"&ADDRESS(ROW(),COUNTA(Sheet1!$1:$1),4))*0.02

HTH

 

[Guest]

Toppers - can you explain how this works and what I need to change to make it
work in my spreadsheet

 

[Guest]

The COUNTA counts the number of non-blank columns in row 1 so if A to D are
filled, you will get 4. (it assumes there are no intermediate blanks cells
i.e they re contiguous).

The ROW() value is the row in which your formula resides so you may need to
adjust for your sheet; for example, if your sheet2 formula is starts in row 4
but data in Sheet1 starts in row 2 then the formula would be:

=INDIRECT("Sheet1!"&ADDRESS(ROW()-2,COUNTA(Sheet1!$1:$1),4))*0.02


If your "headings" are in row 2, change $1:$1 to $2:$2

=INDIRECT("Sheet1!"&ADDRESS(ROW()-2,COUNTA(Sheet1!$2:$2),4))*0.02

ADDRESS returns C2 or D2 etc

HTH

 

[Dave Peterson]

You could even drop the =address() portion and use R1C1 reference style in the
=indirect() function.

=INDIRECT("Sheet1!R"&ROW()&"C"&COUNTA(Sheet1!$1:$1),FALSE) * 0.02

 

[Guest]

Thanks Dave. Always willing myself to learn the options.

 

[Dave Peterson]

You and me, both.

(I bet that's why we're both here!)

 

[Guest]

Dave & Toppers - thank you both so much.

Can I have the COUNTA function count the specific row since not all rows
will end on the same column

 

[Dave Peterson]

If you used:
Sheet1!1:1
instead of
Sheet1!$1:$1

Then you can drag the formula down and it'll increment nicely.