PDA

View Full Version : Update a cell with another cell



onlines
01-19-2009, 01:15 PM
Hello again.

Hopefully the last question :)

Imagine this.
1 Excel File
32 Tabs
The 32nd Tab is labeled "MTD"
Within that MTD tab, i want to get the total for B2 across all tabs.

So,
tab 1, B2 = 1
tab 2, B2 = 1
... all have B2 = 1 until
tab 31, B2 = 1

And then, in MTD, i want B2 to have 32 (because 31 is the value added up across all the tabs)

Is this possible in VBScript?

I am banging my head against the wall (:banghead:) and completely confused (:dunno)

Thank you!

mdmackillop
01-19-2009, 01:22 PM
=SUM(Tab1:Tab32!B2)

onlines
01-19-2009, 01:31 PM
haha.. .that simple.

Then, how about if each tab is a different date in the format of day (19) month (1) year (2009) and i want to get the added values of B2, C2, etc

Etc again using just 2 tabs for sanity's sake
Tab = 18-1-2009, B2 = 10
Tab = 19-1-2009, B2 = 11
Tab = MTD, B2 = 21

How would you do it so all sheets are added up, regardless of name (is that even possible?) or then how would you update MTD B2 with todays date B2 cell.

Thank you again!

PS : Sorry, this is for my daily reports... i'm trying to greatly simplify them and all the help i can get i will appreciate:)

EDIT : Attached is an example of the file, and MTD should ahve the updated values...

Thank you again!

mdmackillop
01-19-2009, 01:56 PM
If you are adding consecutive sheets, you can use this method.
=SUM('18-1-2009:MTD'!B2)

onlines
01-19-2009, 05:13 PM
If you are adding consecutive sheets, you can use this method.
=SUM('18-1-2009:MTD'!B2)

And that will add from shset 18-1-2009, 19-1-2009 all the way until MTD?

If not, how would one go about adding ALL B2 fields up, ALL B3 fields up, ALL... until I9, and then repeat B3, C3, etc until I36?

onlines
01-20-2009, 06:07 AM
Sorry, bump!?

onlines
01-20-2009, 07:34 AM
Dim inner_WorksheetCount : inner_WorksheetCount = worksheetCount
Dim inner_TabCounter
Dim inner_currentWorkSheet
Dim inner_activeSheetName
Dim inner_nextCellIterator
For inner_TabCounter = 1 To inner_WorksheetCount
Set inner_currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(inner_TabCounter)
objExcel.Sheets(inner_currentWorkSheet.Name).Activate
inner_activeSheetName = inner_currentWorkSheet.Name
If Left(inner_activeSheetName, 5) <> defaultTabName Then
WScript.Echo inner_activeSheetName
objExcel.Sheets("MTD").Select
inner_nextCellIterator = "B2"
objExcel.Range(inner_nextCellIterator).Select
objExcel.ActiveCell.Formula = "=SUM('" & inner_activeSheetName & ":End'!" & inner_nextCellIterator & ")"
Else
' empty cells mean we're simply wasting time adding 0s with values, 1 + 0 = 0; or so i'm told
Exit For
End If
Next


Where inner_nextCellIterator will always shift up 1 + 1 (C2, C3, D3, etc)

Thanks for starting me off with at least the formula. You know who that one person is :)

onlines
01-20-2009, 07:59 AM
Well, actually, this raises another question. I need to keep adding to the formula, each tab that i hit until i eventually get 102, or 204, or 306 after each tab has been added up