Search results

I was wondering if anyone had some opinions on this software. I'm looking to use it for organizing campaigns throughout the year in different areas, but using the trial I'm finding the software is actually pretty complicated even for someone comfortable with Excel. Any reviews? Should I...

I played around with Pivot tables, but I can't do a scatter pivot-chart which is what I need.

Hello, I am intermediate-skilled with lookup formulas, VBA, SumProduct etc. but the project I'm on now is a different beast. I need to build transaction maps, so of a large table of data, I'd like to pull a smaller table for a quarter. Basically this is the step I can't figure out a great...

I have a similar question to this. I'm displaying Actual v. Forecast on a dashboard, and by default the cell will show the variance in thousands. I added in a button that changes the underlying formulas to generate a percent, but when I do this the percent is in the thousands or more. The...

Fixed...there was a blank in one of the cells for that month (as opposed to a 0). Sorry I didn't catch that before posting. Sumproduct continues to baffle me...my formula works perfectly. Also works perfectly if I remove the -- but still multiply. Time to read the formula forensic and...

Hello, I'm using sumproduct to summarize some data from a larget table. The formula works great for everything EXCEPT my May data?! =SUMPRODUCT(--(PlanRegion=$B17)*--(PlanMetric=$C17)*--(PlanMktProd={"A","B","C","D"})*Plan!H$2:H$141) The column H is returning a #VALUE where everything...

It looks like my issue was that there was too much text in those cells...after deleting portions of the cells I found where 1 additional character, in any cell of that row, will cause the formula to return #VALUE instead of true/false. Thanks for the help!

It doesn't seem to work, still give me a value error. I only get a true/false if I delete the cell containing X;Y;Z etc...

genius, thanks!

I figured out that the formula does not like semi-colons (;) in any of the fields. I like the 2nd formula, thanks. Now to figure out what to do with all these records with semicolons. If I remove duplicates using excel functionality, and select all columns as possibly containing dupes...

Good suggestion. Some of the countif formulas evaluate to "#VALUE" citing a value used is the wrong data type. Any ideas? Most of the formulas have 1, or 2 if the line is duplicated. There are a couple date columns but I don't know why sometimes the data type is off... I selected the...

I was writing all sorts of VBA code to do some sorting/analysis on data...then Narayan pops in with a simple SUMPRODUCT formula that worked even better. Who knew these simple formulas had so much mileage?

Hello I have been trying to make a SUMPRODUCT formula to check if one line of data is exactly the same as another in my list. I have tried many variations, read thru the forensics post, but this formula is still giving me some difficulty to fully wrap my head around :( Here is one of my...

I managed... -Formatted data labels to hide 0 values -removed legend -made 5 legends in cells, saved them as ranges, and used the linked picture tool to cycle through legends according to the controls on the sheet. Why is excel such a pain with legends...I can't reorder the items, hardly...

I know you can use named ranges in a series, but I thought Deb was trying to get the whole data range as a dynamic range. I was trying to get this working because sometimes there are 2 series, sometimes 4 or 5. When I'm showing 2 series, all the data labels and legend show a lot of blanks due...

Was a solution ever found? I have the exact same problem. I've tried a lot of work arounds with a dynamic chart with changing # of series, but if I can get this to work, data labels will be usable. My other attempts use a larger range, but populate a lot of 0 or "$" data labels, and blank...

@Nara, wow...would NOT have come up with that on my own. I had to use evaluate formula for 15 minutes until I got a mild understanding of that. Thanks to both of you for your help!

Heres what my zips looks like, and the zip references for MW and West. Now that I got this first version done, I'm going to do a lot of optimizing of the zip refs. A lot of the ranges are consecutive, but some are not. (37110 for example is not in MW.) I have no idea why these zip references...

I have it working great, but I have 45k lines of zip codes to check and this macro is currently on hour 2 of processing the regions. each region has lists of upper and lower limits, which are the code.Value and code.Offset(0,1).Value. Any creative ideas to make this more efficient? I thought...

While its compiling, I'm just baffled as to why I can't use one line to skip a cell if its a string. At this point its not getting confused about other Next cell callouts. I used your second recommendation and it works, but it complicated my loops a bit so now I have to do a bit more...

Any idea why this does not work? For Each cell In Zips If Not WorksheetFunction.IsNumber(cell.Value) Then Next cell Im trying to have it skip over cells in Zips that are strings. It says "Next without For", although For its right in a for loop...

Awesome, thanks for the help. That makes sense and I do "know" how to nest statements but it was getting late!

Greetings, This code highlights "Else" and says "Else without If". I am always running into little issues with If, Then statements, and it seems every time I do it I have to spend 15 minutes playing around with it until it works. No success this time :( It is comparing values from 2...

Not sure how to post it, and I would have to gut a lot of the data anyway. Was curious if people had seen this before...thanks anyway.

Hello, I currently have a table of variances shown by data bars. Green for positive, red for negative, applied by the stock Excel data bars feature in conditional formatting. These work great, however sometimes when the variance is 0 for all cells, some random cells will have a green or...