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 names
E12:E53 are corresponding values

I 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? error

I 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

Reply ↓  Report •

✔ Best Answer
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.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  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, .

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  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


Reply ↓  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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  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


Reply ↓  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 in
giving any specific suggestions,
but a simpler way would be to Sum The Sums

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

OR

Say cell AA1 has the name Australian Dollar in it
and cell AB1 is the sum total

Then on your summary sheet do a VLOOKUP() to each
month sheet and put the sum in a separate cell and
sum 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.

MIKE

http://www.skeptic.com/


Reply ↓  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


Reply ↓  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?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  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.


Reply ↓  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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  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.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#12
September 14, 2018 at 05:27:39
Mike, Thank you. I didn't know that "smart quotes" was even a thing!! I do now!!

DerbyDad03,
Here is what I am trying to do (I will try to be as concise as possible).

I trade commodity futures and I track all of the markets that I have traded, both monthly and yearly.
I have a workbook with 1 summary sheet, 12 monthly sheets (January - December) and 1 sheet for lists for drop down menus...14 sheets in total.
All monthly sheet are identical, just different months in the header and tab names.

On the monthly sheets,

Column A is a drop down menu of Sectors (Currency, Energy...etc)
Column B is a dependent drop down menu of Market names (Australian Dollar, Crude Oil...etc)...B12:B60
Column E are totals corresponding to each Market name for that month....E12:E60

I have 46 available different Market names to trade. I don't trade all of them every month, so their position in Column B is random. There is no way for a Market name to have a fixed position in every month. If a market trades that month, then the Market name is listed in Column B and a total in Column E.

On the summary sheet,

I have a chart with each Market name listed and I want to add together all 12 monthly Totals (E12:E60) for each individual Market name (B12:B60) (example: January - December for Crude OIl..., etc).
In some months markets will trade and some they will not, so if the Market name shows up in Column B, then it should be added to its appropriate cell on the Summary Page.

I hope this explains what I am trying to accomplish. If not, let me know and I can further clarify.
I never had any idea that something so simple in my head could be so complicated on paper!!!

Again, any help that I can get would be greatly appreciated,

Jerry


Reply ↓  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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#14
September 14, 2018 at 16:26:11
✔ Best Answer
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


Reply ↓  Report •

#15
September 15, 2018 at 05:46:34
That is essentially what I was offering to help with. A formula that referenced the data instead of hard coding it into the formula.

Good job figuring it out on your own!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question