SOLVED

Do a search/sort/sum in an excel workbook and put results in another workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-2756431%22%20slang%3D%22en-US%22%3EDo%20a%20search%2Fsort%2Fsum%20in%20an%20excel%20workbook%20and%20put%20results%20in%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2756431%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20not%20an%20in%20depth%20excel%20user.%26nbsp%3B%20I%20am%20working%20with%20one%20excel%20file%20with%20multiple%20workbooks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EIn%20WORKBOOK1%2C%20i%20need%20to%20search%20COLUMN%20D%20for%20all%20values%20%3D%20to%20VALUE1.%26nbsp%3B%3C%2FLI%3E%3CLI%3EIn%20each%20%3CSTRONG%3Erow%3C%2FSTRONG%3E%20with%20COLUMND%20%3D%20VALUE1%2C%20i%20need%20to%20grab%20the%20data%2F%24%24%20in%20COLUMNC%26nbsp%3B%20and%20get%20the%20sum%20of%20all%26nbsp%3B%20COLUMNC%20values%20where%20COLUMND%20%3D%20VALUE1.%3C%2FLI%3E%3CLI%3EI%20want%20the%20SUM%20of%20all%20data%20in%20COLUMNC%20where%20COLUMND%20%3D%20VALUE1%20to%20appear%20in%20a%20cell%20in%20WORKBOOK2.%26nbsp%3B%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlowing%20through%20this%2C%20but%20working%20against%20a%20deadline%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2756431%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2756662%22%20slang%3D%22en-US%22%3ERe%3A%20Do%20a%20search%2Fsort%2Fsum%20in%20an%20excel%20workbook%20and%20put%20results%20in%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2756662%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1157849%22%20target%3D%22_blank%22%3E%40sboland61%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20at%20all%20possible%20for%20you%20to%20post%20a%20copy%20of%20your%20actual%20workbook%3F%20It%20is%20very%20(VERY)%20difficult%20to%20respond%20to%20a%20verbal%20description%20such%20as%20yours%20in%20any%20way%20that%20one%20could%20feel%20confident%20about%2C%20especially%20given%20your%20own%20admitted%20lack%20of%20in%20depth%20experience%20with%20whatever%20one%20might%20offer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20let%20me%20ask%20again%2C%20%3CU%3Ecould%20you%20post%20a%20copy%20of%20the%20actual%20workbook%3C%2FU%3E%20(not%20just%20an%20image%3B%20the%20actual%20workbook)%3F%3C%2FP%3E%3CP%3EIF%20the%20actual%20workbook%20contains%20proprietary%20or%20confidential%20information%20(or%20private%20personal%20names%20and%20contact%20info)%2C%20%3CU%3Ethen%20a%20facsimile%20with%20false%20names%3C%2FU%3E%2C%20just%20to%20give%20us%20here%20some%20better%20sense%20first%20hand%20of%20the%20relationships%20your%20words%20have%20described%2C%20how%20the%20data%20on%20the%20various%20sheets%20are%20arrayed%2C%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2756802%22%20slang%3D%22en-US%22%3ERe%3A%20Do%20a%20search%2Fsort%2Fsum%20in%20an%20excel%20workbook%20and%20put%20results%20in%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2756802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Bjust%20did...%3C%2FP%3E%3CP%3EI%20was%20trying%20to%20simplify%20in%20my%20description%2C%20but...%3C%2FP%3E%3CP%3EFor%20example%3A%26nbsp%3B%20in%20tab%20CINDY%20i%20will%20have%20a%20code%20in%20Column%20C.%26nbsp%3B%20If%20that%20Code%20%3D%201%20(or%20whatever)%2C%20i%20want%20to%20add%20up%20the%20data%20included%20in%20Column%20B%20-%20Hours.%26nbsp%3B%20This%20should%20happen%20for%20each%20line%20where%20Code%20%3D%201.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20that%20data%2Fformula%20should%20be%20referenced%2Fdisplayed%20in%20workbook%20SUMMARY.%26nbsp%3B%20IDC%20where%2C%20i%20havent%20set%20that%20up%20yet.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank-you%20for%20bothering%20to%20decrypt%20my%20message!%26nbsp%3B%20Greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2757004%22%20slang%3D%22en-US%22%3ERe%3A%20Do%20a%20search%2Fsort%2Fsum%20in%20an%20excel%20workbook%20and%20put%20results%20in%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2757004%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1157849%22%20target%3D%22_blank%22%3E%40sboland61%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOK%2C%20here's%20a%20start.%20I've%20modified%20the%20sheets%20%22CIndy%22%20and%20%22Allison%22%20to%20make%20them%20Excel%20Tables%2C%20with%20the%20names%20%22Cindy%22%20and%20%22Allison%22.%20Doing%20this%20makes%20it%20possible%20for%20you%20to%20continue%20to%20add%20rows%20to%20either%20of%20them%20and%20have%20the%20formulas%20take%20into%20account%20added%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20also%20assumed%20that%20some%20hours%20will%20be%20coded%20%221%22%20as%20you%20suggest%2C%20but%20that%20others%20might%20be%20coded%202%20or%203%2C%20etc.%2C%20so%20I've%20covered%20that%20possible%20scenario%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20in%20the%20Summary%20sheet%2C%20the%20set%20up%20is%20as%20seen%20below%2C%20and%20the%20formula%20for%20cell%20D5%20is%20visible%20in%20the%20formula%20bar%2C%20that%20being%3A%26nbsp%3B%3DSUMIF(Cindy%5BCODE%5D%2CD%243%2CCindy%5BHOURS%5D)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1631809820830.png%22%20style%3D%22width%3A%20643px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F310830i4E4E8A0E410E46C4%2Fimage-dimensions%2F643x222%3Fv%3Dv2%22%20width%3D%22643%22%20height%3D%22222%22%20role%3D%22button%22%20title%3D%22mathetes_0-1631809820830.png%22%20alt%3D%22mathetes_0-1631809820830.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

9 Replies

@sboland61 

 

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.

@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!

@sboland61 

 

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])

mathetes_0-1631809820830.png

 

 

best response confirmed by sboland61 (Occasional Contributor)
Solution
Just pouring through this now...it is perfect, thank-you!! still playing with how i can expand etc. YOU ROCK
got the tables in and cleaned some other things up. I cannot get the other tab totals (Sharon, Suzanne, Capitol One, Mountain West and Monthly Operations to add up and map over to summary tab. I thought i would be able to replace the tab names in the formula from Cindy - > Sharon (for each tab). the formula: =SUMIF(Sharon[CODE],D$4,Cindy[HOURS]) results in an error. What is D$4?

@sboland61 

 

D$4. is a reference to a cell toward the top of the summary sheet. The dollar sign keeps the reference to row 4 even if the column changes; it's called an "absolute reference".  As I said in my earlier post, I was assuming that you'd have codes OTHER THAN 1, so created a way for those to be handled; hence the codes 2----

 

That said, the formulas' references to Cindy and so forth are references to a table name; some of the new ones that you created are called Table5, Table8, and so forth. 

 

If I were doing this myself, unless there were compelling reason to have separate sheets for each source, I'd make them all a single database and have the summary still able to separate them different entities out by selection criteria. Having separate sheets--although cleaner for human beings to look at--actually gets in the way of a clean and simple Excel formula, as you're discovering. You need to write a different formula for each entity, rather than using one formula and referring to the names.....

 

If you're able to put all those billing hours into one table, regardless of source--just adding a column to identify whether it's Cindy or Allison or.....--let me know and I can show you that.

Deadline? you best share your workbook or a sample of it which reflects a realistic scenario of your data set.