Automatically updating multiple Excel worksheets from one "master" worksheet?

July 9, 2009 11:54 AM

How to automatically breakout Excel worksheets based off one "master' worksheet?

I inherited an Excel spreadsheet that is being used to to track individual orders. It is built so each order has its own row and the columns reflect various information (i.e. state)

My boss wants data entry done on the "master" worksheet. Then he wants the other sheets to automatically update given certain criteria. (For example, all orders placed in California.)

Let's say the state column is Column A and the values are simple two letter postal codes.

How would I write a function that would say if A2 is CA, then paste 2 here and if A2 is not CA, go to row 3 and check for CA?

posted by entropicamericana to computers & internet (9 comments total)

Places to start:
SUMIFS(sum_range; critera_range1, critera1; [etc])
- with the critera of "CA" (probably specified somewhere on the second sheet)
if the sumrange is Column B, and the criteria range is Column A of the same length, and the criteria is "CA" or a cell with the text "CA" then you'll have a sum of all values with "CA"


VLOOKUPS
(Careful with these, data needs to be sorted or things go whacky, tables with 70K records tend to be brute forced meaning things get painful) - keep in mind that you can't be looking up something that occurs twice unless you do some funky stuff with key creation...

PivotTables and GETPIVOTDATA also are great friends
posted by Nanukthedog at 12:25 PM on July 9

You're looking at more Visual Basic than I have time to flesh out for you. To be honest, factoring in development and debugging you would probably save time by copying the row and manually pasting it into the subsequent sheets in the long run. If you are entering enough data that this is not the case then you should look to technologies more robust than Excel.

Solemente mis dos centavos. I know that this post is not helping you towards your specific goal and for that I apologize.
posted by Gainesvillain at 12:25 PM on July 9

Take this opportunity to learn Access or some other database product. You'll thank me later.
posted by odinsdream at 12:27 PM on July 9

Nanukthedog: Correct me if I'm wrong, but it looks like SUMIF would only be useful if I am looking to summarize numerical values. The other columns beside State include things such as customer name, initials, dates, etc.

odinsdream: I would, but moving the tracking to Access is "Not An Option." *shrug*
posted by entropicamericana at 12:37 PM on July 9

I think Pivot tables are exactly what you need to create. This looks like a pretty good tutorial to start with.
posted by Oddly at 12:38 PM on July 9

Ugh.... Pivot tables seem like what you need... but they are Satan... I mean, hi-fives if you can wrap yr brain around 'em... god knows I've never been able to.

FWIW, I have learned a *ton* of amazing tricks via MS's Office Forums (I know weird right?)

Basically I use them like any Q&A site... first I search (usually the formula name gets you close - ex: "sumif multiple criteria").

If I don't find what I need, I post a question with some example data.

I've gotten some ridiculously powerful formulas / solutions this way. The locals are super-smart and actually NICE!
posted by MeatLightning at 3:21 PM on July 9

pivot tables are the reason to use excel - that and getting yout CDO's to be AAA ... but mainly the pivot tables.
posted by fistynuts at 4:18 PM on July 9

You could probably do this with some annoying combination of Match(), Index(), and Offset(), but it'd give you the Cthulhu-esque crazies. Not knowing the full story, I'd urge the boss to live with a bunch of autofilters on the master sheet, (or, better, on a copy of the master sheet).

To actually split out a sheet for reporting, the folling vba inserted into a module on your main sheet and run should do the trick.... maybe attach it to a button on the master sheet.

Sub splitout()

Dim strPath, strOutDetails, strMsg As String

On Error GoTo handle

strOutDetails = Workbooks("Master").Path & "\DisplayCopy.xls"

Workbooks("Master").Sheets("Master").UsedRange.Copy
Workbooks.Add
ActiveCell.PasteSpecial
ActiveSheet.UsedRange.AutoFilter

If Dir(strOutDetails) <> "" Then Kill (strOutDetails)
ActiveWorkbook.SaveAs (strOutDetails)

stopping:
Exit Sub

handle:
strMsg = "An unexpected situation arose in your program." & vbCrLf & _
"Unable to continue due to Error " & Err.Number & vbCrLf & Err.Description
MsgBox strMsg, vbCritical, "Oh bother."
Resume stopping

End Sub

posted by pompomtom at 4:35 PM on July 9

2nding odinsdream. What you're talking about is 'querying' a superset of data ('All orders') to return a meaningful subset of data ('All orders from California'). As long as your dataset contains meaningful elements to support the querying, this is much better suited to a package such as Microsoft Access or FileMaker etc. This is not to say that Excel can't be used to achieve your goal, but that you are now treading heavily within the demilitarised zone between solutions you would provide via a spreadsheet versus solutions you would provide via even a desktop database package (such as Access / FileMaker).
posted by planetthoughtful at 7:53 PM on July 9