Solved How to add from different sheets that have drop down menus

September 13, 2018 at 09:54:22
Specs: Macintosh
 Hi!!I have just learned about Drop Down Menus using Data Validation and have run into a problem.I have a workbook with a summary page and a page for each month. I have a drop down menu on each month with market names ( Australian Dollar...etc ).B12:B53 are Drop down with market namesE12:E53 are corresponding valuesI have tried SUMPRODUCT(--(January!B12:B53="Australian Dollar"), January!E12:E53 to see if I could get it to work with 1 month.I get a #NAME? errorI want to be able to add all "Australian Dollar" totals from each month into cell D4 on the Summary page.Any help would be greatly appreciated!!Thank you!!

See More: How to add from different sheets that have drop down menus

September 14, 2018 at 16:26:11
 Mike and DerbyDad03, Here was the formula that I was looking for...=SUMPRODUCT(SUMIF(INDIRECT("'"&Months&"'!B10:B61"),\$B4, INDIRECT("'"&Months&"'!E10:E61")))This works!!!I had to make a list for the Months ( the tabs) and then I was able to find this formula. \$B4 is one the Market Names on my destination chart on my Summary page. I just had to change that cell number for each Market Name.I didn't really know exactly what I was looking for. I kept searching the internet and found a question that someone was asking that was close enough to mine, so I was able to use their formula.I don't completely understand what it is saying, but I figured it out enough to be able to apply it to my situation.Thank you again for the help that you have given me. It is greatly appreciated!!!!Jerry

#1
September 13, 2018 at 10:21:07
 Your formula is not complete, SUMPRODUCT(--(January!B12:B53="Australian Dollar"), January!E12:E53 should be:SUMPRODUCT(--(January!B12:B53="Australian Dollar"), January!E12:E53 )You left off the closing parentheses.I tested it out and it works for me.A NAME error usually point to a typo of some kind, check your spelling. MIKEmessage edited by mmcconaghy

Report •

#2
September 13, 2018 at 10:28:27
 Another good practice is to always type Excel Functions in lower case. That way, when you enter a formula, if it is recognized, Excel will automatically convert it to upper case, . MIKEmessage edited by mmcconaghy

Report •

#3
September 13, 2018 at 16:09:46
 Mike,Thank you...The lowercase tip was a really good tip that I did not know. My error in the original post was just a typo on this post. I was working off of another computer. Here is the formula, copied and pasted directly from the spreadsheet. =SUMPRODUCT(--(January!B12:B53=”AUSTRALIAN DOLLAR”), January!E12:E53)It is still giving me the same #NAME? error. I wrote the functions in lowercase as you said and Excel converted them. If I misspelled something, I am not seeing it.Would getting the market name coming from the drop down menus be causing a problem? And how would I add more than one month together? Would I use =Sum((Sumproduct(--(January!B12:B53="Australian Dollar"), January!E12:E53)+(Sumproduct(--February!B12:B53="Australian Dollar"), February!E12:E53)) and then add the other 10 months...I can't thank you enough... you are making a very daunting task for me into a less daunting task!!!Jerry

Report •

Related Solutions

#4
September 13, 2018 at 16:57:56
 Look at the quotes around AUSTRALIAN DOLLAR. They look like this...” ”...when they should look like this:" "I get a #NAME error with the "smart quotes" also. Change them to straight quotes and everything is fine.

Report •

#5
September 13, 2018 at 17:06:25
 DerbyDad03,Thank you!!! I never would have caught that. I never would have thought to look for that.I deleted the quotes, added a space after the = and then put the quotes back and now it works. The formula works now, too.Thank you again!!!Jerry

Report •

#6
September 13, 2018 at 17:28:25
 Along with what DerbyDad pointed out abut the quote marks, this probably means you wrote the formula in Word.Word uses the Smart Quotes, while Excel does not like them.Without knowing how your sheets are set up I would be hesitant ingiving any specific suggestions, but a simpler way would be to Sum The SumsFirst sum them in each monthly sheet.Say cell AA1 is the total for Australian Dollar on each Month sheet,then in the Summary sheet you could do something like: =SUM(Jan:Dec!AA1)ORSay cell AA1 has the name Australian Dollar in it and cell AB1 is the sum totalThen on your summary sheet do a VLOOKUP() to eachmonth sheet and put the sum in a separate cell andsum the months on the summary sheet.That would give you a running total.Lots of way to do it, but without specific info as to what your doing and how your sheets are set up it's difficult.MIKEhttp://www.skeptic.com/

Report •

#7
September 13, 2018 at 17:35:01
 Mike,You are correct. I did write it in Word and then copy it to Excel. I won't do that again. Easier to write it down and then type it out.I understand about lots of different ways to do things. I will work on it some more tomorrow.Thank you for all the help,Jerry

Report •

#8
September 13, 2018 at 18:29:11
 I too "understand about lots of different way to do things" but what I don't understand is "I did write it in Word and then copy it to Excel."Why would you write formulas in Word and copy them to Excel? Why not just write them in Excel in the first place?

Report •

#9
September 13, 2018 at 18:42:17
 I am using information from 12 different sheets and was copying and pasting into 33 different cells on a summary sheet. Each cell has the same formula with only a different market name. I thought this might be easier, since I didn't have to deal with the =. I thought I would copy and paste the formula, change the name and then add the =.Not sure if this is the way to do it, but obviously, it didn' t work!!!! : )For a relative newbie to this level of Excel, this is a bit daunting. Tried to make it manageable for me.

Report •

#10
September 13, 2018 at 18:54:37
 If I had a clearer picture of what you were trying to do, I might be able to come up with a way to build those formulas within Excel. The Concatenation operator (&) comes to mind.

Report •

#11
September 13, 2018 at 22:07:02
 jdueitt If you must, don't use Word, use a text editor like Notepad, which comes with Windows,or JEdit or Notepad++ which are both good text editors.That way the "smart quotes" won't give you problems.MIKEhttp://www.skeptic.com/

Report •

#12
September 14, 2018 at 05:27:39

Report •

#13
September 14, 2018 at 07:13:31
 Thank for sharing your goals for this workbook.Perhaps I should clarify what I was asking for. I was wondering if I could offer a quicker way to build your formulas. You mentioned copying and pasting formulas into 33 different cells and then manually editing them.I was thinking that using cell references or an embedded VLOOKUP or something like that might be easier than manually hard coding the name of each market into the formula. Not sure if that is possible since I don't have a clear picture of your layout or where the "market names" come from.

Report •

#14
September 14, 2018 at 16:26:11