How can I change info in one tab when I change info in a seperate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any possible way when I am updating information in one tab to make
sure it transfers the info to a second tab to update the information there?

For instance I have in Tab A information on several trucks such as year make
model VIN etc. In Tab B I have the years of the trucks and how many of each I
have. If I enter in a new year in to Tab A will it add an extra truck to Tab
B. Sorry if this is confusing... if anyone understands this and can let me
know if this is possible I would appreciate it. Thank you!
 
I believe what you would need/want is a SUMPRODUCT() formula for the count of
trucks of a specific make/model/year on Tab B. One similar to this should be
able to do the job:
=SUMPRODUCT(--('Tab A'!A:A="Ford"),--('Tab A'!B:B="F100"),--('Tab
A'!C:C=2000))
would return # of 2000 Ford F100's in the lists from tab A. Of course
change 'Tab A' to the actual name of that sheet, and same for the column
references. If you can limit the area to be searched it will be faster.
Let's say you now have trucks listed in rows 2 through 104 and want to be
able to add some more without having to adjust things for a while (say for 50
more acquisitions), you could modify that to be like this:
=SUMPRODUCT(--('Tab A'!A2:A154="Ford"),--('Tab A'!B2:B154="F100"),--('Tab
A'!C2:C154=2000))

If you'll look into naming the ranges (A2:A154, B2:B154, C2:C154) it could
make maintenance of those formulas on Tab B a hands off deal. Might end up
with a formula that looks like this that you'd never need to touch again:
=SUMPRODUCT(--(Make="Ford"),--(Model="F100"),--(ModelYear=2000))
 
Back
Top