Forum Discussion
Do a search/sort/sum in an excel workbook and put results in another workbook
I am not an in depth excel user. I am working with one excel file with multiple workbooks.
- In WORKBOOK1, i need to search COLUMN D for all values = to VALUE1.
- In each row with COLUMND = VALUE1, i need to grab the data/$$ in COLUMNC and get the sum of all COLUMNC values where COLUMND = VALUE1.
- I want the SUM of all data in COLUMNC where COLUMND = VALUE1 to appear in a cell in WORKBOOK2.
Plowing through this, but working against a deadline 😞
- Just pouring through this now...it is perfect, thank-you!! still playing with how i can expand etc. YOU ROCK
13 Replies
- Deadline? you best share your workbook or a sample of it which reflects a realistic scenario of your data set.
Me again. thank-you for your help in sept. I've been pretty lame on this by just using it, but recently had to expand it....so i read your references ....i get it. I cant figure out now, why i'm getting errors. Getting the right number, but an error flag.
If you can, see attachment. Specifically, on the SUMMARY tab : Cells J12 and and K12. When i added new tables, created names...i can only think in setting up the table i had to do something to designate the columns for formulas......idk....input appreciated.
By "error code" do you mean the yellow triangle and its associated message?
All that's doing is warning you that the formula may unintentionally be inconsistent with an adjacent formula. If in fact it's giving you the desired result, if in fact it's not in error (as you seem to be saying), that's a judgment call you can make.... and you can select "Ignore error"--essentially saying "It's not an error"--and go on about your business.
This workbook is both an impressive one, and a worrisome one--I say as an outside observer. If you are the only one to use it, the only one to maintain it, and if it's delivering the results you need, then that's fine. But I'd strongly recommend writing, even for yourself, a document that explains what each page is doing, what each formula on each page is doing, and why.... Without that kind of documentation, an Excel spreadsheet can easily go awry and create plausible but deeply erroneous results.
Spreadsheets/workbooks can become overly complicated very easily. The more you can create a fundamental design that is simple and clear, the more trustworthy it can be.
That's partly why, in an earlier posting, I said that if I were doing it, I'd not have separate tabs for each person or site (or whatever it is that those separate tabs represent); instead, I'd create a single database and do the differentiation that the tabs are doing by means of added columns. Then have a summary tab (as you do) or "dashboard" section, that does the calculations needed in tested and consistent fashion. That's a far more reliable way to have consistency in formulas and reliability in results.
You might find it helpful to read the attached article on spreadsheet design.
Is it at all possible for you to post a copy of your actual workbook? It is very (VERY) difficult to respond to a verbal description such as yours in any way that one could feel confident about, especially given your own admitted lack of in depth experience with whatever one might offer.
So let me ask again, could you post a copy of the actual workbook (not just an image; the actual workbook)?
IF the actual workbook contains proprietary or confidential information (or private personal names and contact info), then a facsimile with false names, just to give us here some better sense first hand of the relationships your words have described, how the data on the various sheets are arrayed, etc.
Hi, Mathetes,
I am having what seems like a similar issue to the initial one described above. I can make some changes to the file and upload it, if you would be willing to help.
I am also working on a deadline - once that has long since passed - but I am trying to improve efficiencies from manual typing and =SUM functions to at least some automation in Excel.
I actually have a few projects that this one could really help me make some progress on. I would have called myself an advanced user - until I had 100,000 lines thrown at me and told to "reconcile these 40 accounts". It can't be done in one day... unless you know a lot that I don't (and I have no doubt you do) ☺
PLEASE let me know if you can help with this.
mathetes just did...
I was trying to simplify in my description, but...
For example: in tab CINDY i will have a code in Column C. If that Code = 1 (or whatever), i want to add up the data included in Column B - Hours. This should happen for each line where Code = 1.
Then that data/formula should be referenced/displayed in workbook SUMMARY. IDC where, i havent set that up yet.
Thank-you for bothering to decrypt my message! Greatly appreciated!
OK, here's a start. I've modified the sheets "CIndy" and "Allison" to make them Excel Tables, with the names "Cindy" and "Allison". Doing this makes it possible for you to continue to add rows to either of them and have the formulas take into account added rows.
I've also assumed that some hours will be coded "1" as you suggest, but that others might be coded 2 or 3, etc., so I've covered that possible scenario as well.
Then in the Summary sheet, the set up is as seen below, and the formula for cell D5 is visible in the formula bar, that being: =SUMIF(Cindy[CODE],D$3,Cindy[HOURS])
