Multiple Tab Counts

[Guest]

I have a spreadsheet with multiple tabs. The first tab tallys numbers from
other tabs, and compares them with data on the first tab. ex: tab1- tab6. In
tab1, I have a cell, lets say A1 where I need to add tab2-a1, a2, a3 + tab3-
a1,a2,a3 (and so on up to tab6). Then once those cells are added up, I need
it to compare the added number to cell a5 on tab1, if the numbers match,
leave that cell blank. If the two number differ, more or less, I need that
cell to turn red. If I cant make it change color, then mark that cell with an
"X". I keep getting a too many commands rule when I try this.

Thanks!

 

[Guest]

I am not sure exactly what you want but
your cell a1 equation could be
=Sum('tab2:tab6'!A1:A3)
I do not know if you want A1 or A5 to turn red
I will assume A1
select A1
<format><conditional format> Value is not equal to A5
set format pattern to red

if it is A5 you want t6o change to red
Select A5
<format><conditional format> Value is not equal to A1
set format pattern to red

 

[Don Guillett]

=(sum(tab2name:tab6name!a1)+sum(etc

=if(a5=sum(tab2name:tab6name!a1)+sum(etc,"","C")
then use format>conditional format to color if cell<>"x")

 

[Guest]

It gave me an error. I'll write it out actual.
I have a tab called "Weekly" which is my main sheet.
My other tabs are "Work Order Labor1" thru "Work Order Labor6"
In Weekly I have cell E7 where I need to enter this formula.
"Weekly" E7 needs to add up tabs "Work Order Labor1" through "Work Order
Labor6", cells G7, J7, M7, P7, S7, V7, Y7, and AB7. Its these same cells in
all the work order tabs.
Weekly E7 needs to take that total, and compare it to "Weekly" tab, cell AC7.
If the numbers match, I want the cell to be blank, if the numbers dont
match, higher or lower, either mark cell E7 red, or put an "X" in it.

I hope this explains it better, sorry for the weak original description.

 

[Guest]

It gave me an error. I'll write it out actual.
I have a tab called "Weekly" which is my main sheet.
My other tabs are "Work Order Labor1" thru "Work Order Labor6"
In Weekly I have cell E7 where I need to enter this formula.
"Weekly" E7 needs to add up tabs "Work Order1" through "Work Order6", cells
G7, J7, M7, P7, S7, V7, Y7, and AB7. Its these same cells in all the work
order tabs.
Weekly E7 needs to take that total, and compare it to "Weekly" tab, cell AC7.
If the numbers match, I want the cell to be blank, if the numbers dont
match, higher or lower, either mark cell E7 red, or put an "X" in it.

I hope this explains it better, sorry for the weak original description.

 

[Don Guillett]

COPY and modify this sub to suit

Sub sumemup()
On Error Resume Next
For i = 1 To 6
With Worksheets("w of " & i)
mysum = mysum + .Range("b2") _
+ .Range("g7") _
+ .Range("j7") _
'etc

End With
Next i
MsgBox mysum
With Sheets("w")
.Range("a1") = ""
If mysum <> .Range("b1") Then .Range("a1") = "X"

'Sheets("w").Range("a1") = mysum
End With
End Sub

Or even better
Sub sumemup1()
On Error Resume Next
For i = 1 To 6
With Worksheets("w o " & i)
mysum = mysum + Application.Sum(.Range("b3,d4,f5"))'etc
End With
Next i
MsgBox mysum
With Sheets("w")
.Range("a1") = ""
If mysum <> .Range("b1") Then .Range("a1") = "X"

'Sheets("w").Range("a1") = mysum
End With
End Sub

 

[Guest]

In what step do you get an error and what error is listed
is the order of the sheets Weekly, ... labor1, ... , ...Labor 6
or a different order?

If this is the order then in E7
=sum('Work Order Labor1:Work Order Labor6'!G7,'Work Order Labor1:Work Order
Labor6'!J7,'Work Order Labor1:Work Order Labor6'!M7,'Work Order Labor1:Work
Order Labor6'!,P7,'Work Order Labor1:Work Order Labor6'!S7,'Work Order
Labor1:Work Order Labor6'!V7,'Work Order Labor1:Work Order Labor6'!Y7,'Work
Order Labor1:Work Order Labor6'!AB7)

It would be easier if you could have a cell in each sheet with
=G7+J7+M7+P7+S7+V7+Y7+AB7
if it were in BA7
then you could use
=sum('Work Order Labor1:Work Order Labor6'!BA7)

select E7
<format><Conditional formatting>
value is - not equal to - AC7
Format - patern - red
if you really want the cell to appear red only and not see the text
add to format
format - font - color - Red
If you really want the cell to appear blank when it does equal AC7
select Add>>
Cell Value is - equal to - AC7
Format - font - color - White

select OK

 

[Guest]

When I entered it, it says the formula contains an error. So I did what you
said to do. I went into all of the Work Order Labor tabs and put individual
"totals" entry for tab. Instead of listing all those cells for each tab, the
cell with the totals is AE7. Does this help? So its adding Work Order Laber1
thru 6, cell AE7 on each, and compare it to "weekly" AC7, and if they dont
match, make red or put an X in Weekly E7. I appreciate the time you're taking
to help me with this.

 

[Guest]

In Weekly E7 enter =sum(
using your mouse click on the tab for Work Order Labor1
holding the Shift click on the tab for Work Order Labor6
select cell AE7
type
)
you should have the sum now

if it in the conditional formatting which gives you problems go back to the
shhet and make sure it did not put quote marks around the formula. remove
them if it did Mine does sometimes, and doesn't others and I have never
figured out why.

 

[Guest]

YES! Thanks!

In Weekly E7 enter =sum(
using your mouse click on the tab for Work Order Labor1
holding the Shift click on the tab for Work Order Labor6
select cell AE7
type
)
you should have the sum now

if it in the conditional formatting which gives you problems go back to the
shhet and make sure it did not put quote marks around the formula. remove
them if it did Mine does sometimes, and doesn't others and I have never
figured out why.

 

[Don Guillett]

Did you try my macro?