Add up cell from sheet 1 to show on sheet 2

  • Thread starter Thread starter 1404ottawa
  • Start date Start date
1

1404ottawa

I want to total the value on sheet 1 col j1 + v1 + z1 to show on sheet 2
Is there a better way then putting this code on sheet 2:
+sheet1!j1+sheet1!v1+sheet1!z1
Much appreciated and thanks
PCOR
 
I want to total the value on sheet 1 col j1 + v1 + z1 to show on sheet 2

Is there a better way then putting this code on sheet 2:

+sheet1!j1+sheet1!v1+sheet1!z1

Much appreciated and thanks

PCOR
Click to expand...

Try this:

Select J1, V1, & Z1 and name the three cells Data. On sheet two =SUM(Data).

HTH
Regards,
Howard
 
I want to total the value on sheet 1 col j1 + v1 + z1 to show on sheet 2

Is there a better way then putting this code on sheet 2:

+sheet1!j1+sheet1!v1+sheet1!z1

Much appreciated and thanks

PCOR
Click to expand...

Thanks for the help but I need more help
How do I go about "Naming" then cells and I wand to carry out the same for J2 + v2 + z2
all the way down to j200 + v200 + z 200
Thanks
again much appreciated
 
Thanks for the help but I need more help

How do I go about "Naming" then cells and I wand to carry out the same for J2 + v2 + z2

all the way down to j200 + v200 + z 200

Thanks

again much appreciated
Click to expand...

To name a range:

Select the first cell and then hold down the
"ctrl" key and select the other cells. Release the ctrl key and click in the name box, which is the white bar just above the A for column A. Type inwhat you want the name of cells to be. You will have to be somewhat cautious not to use RESERVED Excel words like Plus, Add, AA1. If you try to useAA1 as a name it will fail and instead take you to cell AA1 on the worksheet.

To name 200+ named ranges is pretty tedious. I have a query in on how do it using vba code and will pass it on should I get it, along with instructions on how to use it if you need help.

Regards,
Howard
 
Hello,

Rather than naming 200 ranges, you could use a relative reference in the name definition. To do so, choose any cell on Sheet1 row 1 and define a name(Formulas>Define Name) and enter: =Sheet1!$J1,Sheet1!$V1,Sheet1!$Z1. This will keep the column references locked, but the row reference will change depending on what row the name is used in. So, in Sheet2 you could put "=SUM(Data)" in any cell on row 1 and copy it down 200 rows. The downsideto this approach is that your formulas on Sheet2 must be entered on the same row as the data on Sheet1.

Alternatively, you could use the macro below to assign a unique name to each data row. Uncomment the line starting with "Sheet2..." to also add a sumreferencing each new named range.

Sub CreateNames()
Dim rNames As Range
Dim lNames As Long
Dim sName As String

Set rNames = Sheet1.Range("J1,V1,Z1")
sName = "Data"

For lNames = 1 To 200

ThisWorkbook.Names.Add Name:=sName & lNames, RefersTo:= _
"=" & rNames.Offset(lNames - 1, 0).Address
'Sheet2.Range("A1").Offset(lNames - 1, 0).Formula = "=SUM(" & sName& lNames & ")"

Next lNames

End Sub
 
I want to total the value on sheet 1 col j1 + v1 + z1 to show on sheet 2

Is there a better way then putting this code on sheet 2:

+sheet1!j1+sheet1!v1+sheet1!z1

Much appreciated and thanks

PCOR
Click to expand...

Iam learning much and many thanks to all
 
Back
Top