ExcelBanter - Excel Discussion (Misc queries)

SUMIF evaluating blank DATE data

Uncle Spike — Thu, 20 Jun 2013 06:20:03 GMT

Hmm, this daft/simple formula has got me stumped.

I have a simple table with column C that contains numeric values, and column D that contains date values.

All I want to do is a calculate the SUM of cells in column C that do not have a date value in column D.

Something like this... but obviously this does not work, possible as the evaluated data type is a date. Any ideas?

=SUMIF('TRAVEL ADVENTURES'!D3:D105,"NOT(ISBLANK)",'TRAVEL ADVENTURES'!C3:C105)

Advanced Sort/ look

applemanxxx — Mon, 17 Jun 2013 14:34:28 GMT

Good morning everyone,

I am trying to sort a document based on the information in one particular column, this Colum contains the following alpha numerical string �F907E231220084�. I need to help in figuring out how to sort based on the last 4 digits only. I appreciate any and all help. You don�t have to write a formula for me just point me in the direction I will try to figure out the rest.

Spellchecker anomaly... Checks spelling on a Forms Button text label...?

lhkittle@comcast.net — Sun, 16 Jun 2013 18:10:28 GMT

The only place I have experienced this is on this home made puzzle solver worksheet I put together. (Drop Box Link below)

Run spell check from tool bar and it offers a solution to the "misspelled" caption on the forms button ConCat AH15.

A low priority, of course, but I'm curious.

Regards,
Howard

https://www.dropbox.com/s/qju11o4xc8...0-%20Copy.xlsm

Countifs with if and date range

cvhar87 — Fri, 14 Jun 2013 16:16:40 GMT

I am trying to count the occurrences of "ack" in column D only if column A is Q1 and column D has "ack".

=IF(AND(A4:A232="Q1",D4 : D232="ACK"),COUNTIFS(D4 : D232,"ACK"),"N/A")

Right now it is returning just the result of logical true which is 57, but D4 to D32 has only 30 occurrences of ACK, the remaining are different.

Thanks in advance

Index/Match Lookup with a merged col

JoshBrunz — Thu, 13 Jun 2013 23:28:22 GMT

This should be a fun one...

I have a spread sheet of team scores. col a is the date of the game and is 6 cells merged up and down. the 6 cells next to it in col b are names. cols c-o are stats and scores for each name. the dates are not in order. im trying to find the newest game date and display all 6 columns next to it (b-o) in another section.

im using:

=CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0)))

to return the address of the newest game. how can i get the 6 columns next to it?

attached is a screenshot of one dates numbers

Attached Images

Slicers and Calculated Items in Pivot Table

League — Thu, 13 Jun 2013 15:36:18 GMT

I have a Pivot Table/Pivot Chart with slicers. There are 4 slicers attached. When one slicer is filtered the other slicers will filter(grey out) accordingly. But I also have some calculated items in Column Label field. When 1 of these calculated items is selected, the slicers all lose their filter link to each other. (unavailable items in other slicers are no longer greyed out) Non-calculated items don't affect the slicers this way.

Once the slicers are unlinked I can't get them to link with each other again.(I have to open an earlier copy)
All the slicer fields are in the Report Filter section of pivot and data is too large to move these fields to row labels section of pivot.
Pivot source is a MS query connection to an Access database.
Calculated Items are ratios and are calculated to the desired level by the use of the Report Filters.

This workkbook will be used by management so I need an easy way to select only items that are still 'available' after 1 or more of the Report Filters is used. Slicers looked like the answer but any suggestions would be helpful.

Filtered drop down list

Peter Niepel — Thu, 13 Jun 2013 04:35:21 GMT

Hi Excel experts,

Excel Version: 2010

I need some help please. I need to use a drop down list which is filtered based on a flag. As an example:

The products table has two columns, Product and Active. Product has product names and active has a flag (1 or 0) to activate/deactivate a product. On another worksheet - lets call it order items - I need to use a drop down field in a column which provides all active products as values.

What I did so far is, I added a third column in the products sheet called it "Active products" and use this as the data source for the drop down list. This has either a product name if the Active column is 1 or is blank. It works ok-ish with the one problem that the blanks do show in the drop down list. Not nice! I also used a name range for this column because I read somewhere that using a name range in the list property of a drop down removes empty cells. This doesn't work.

Any ideas how I could filter the list of a drop down based on another column's value?

Thank you for your time.

Peter

Excel 2007 pivot table using analysis services cube as data source

Peggy Holzkopf — Wed, 12 Jun 2013 20:15:10 GMT

Hi,

I have created a pivot table using excel 2007 with an analysis services cube being used as the data source. The cube has numerous dimension tables and four fact tables. When I try to drill down into a value in the pivot table, the results that are returned contain the Keys to the dimension tables that reference the actual data but not the actual data itself.

Has anyone else experienced this type of problem before? Do you know what I need to change in order to return the detailed data and not the keys?

Any help would be greatly appreciated.

Thank you,
PH

Excel Stops Working

MLD[_2_] — Tue, 11 Jun 2013 22:43:05 GMT

Random occurrence: In closing Excel a pop up comes up saying "Excel has
stopped working etc". Then Excel closes, reopens, works normal after the
restart after which it will shut down normally---that is, until the next odd
ball time. Gone through the Repair procedure without effect. Has this been
explained and are there any solutions that have proved effective?
Using Vista with MS Office 2007
MLD

Spell Check Protected WORKBOOK

tso — Tue, 11 Jun 2013 18:06:41 GMT

Hi Everyone,

I have found the Macro code to check spelling for one particular worksheet.

However, I want the macro to check the spelling in all 10 worksheets.

This is what I am using now.

Sub SpellCheckIt()
Sheets("Sheet1").Unprotect "Password"
ActiveSheet.CheckSpelling
Sheets("Sheet1").Protect "Password"
End Sub

Thank you,

Excel Stops Working

MLD[_2_] — Tue, 11 Jun 2013 13:21:39 GMT

Random occurrence: In closing excel a pop up comes up saying "Excel has
stopped working etc". It then shuts down followed by a restart. Works
normal after restart and even shuts down OK---that is, until the next odd
ball time. Gone through the Repair procedure without effect. Has this been
explained and are there any solutions that have proved effective?
Using Vista with MS Office 2007
MLD

Change text color with an IF statement

WGD — Mon, 10 Jun 2013 19:11:14 GMT

Please, would like to change the color of text in a cell t driven by
an IF statesment in another cell.

How is this done?

Thank You

Wayne
Sarasota

the cells that some rows have in common

analarana — Mon, 10 Jun 2013 16:49:33 GMT

Hi,
I know nothing about access, and I don't even know if excel is the right program for what i need.
Could someone please tell me how I can find the cells that some rows have in common? I need to do this lots of times with lots of names, but this is a small example:
https://www.dropbox.com/s/ylqs36s0nmvbvx4/excel2.jpg
So for example, the first time I need to know the numbers that Ana (row 1), Laura (row2) and Manuel (row3) have in common, so the computer should give me n�17 as the answer. If I ask for the numbers (or cells) Chris (row 4)and Eddie (row 5) have in common, the computer should say n�2, n�3 and n�4. I'm not looking for the number that appears more times in the grid, but for the one that the rows I select have in common.
Cheers.

need to extract email addresses from excel files

dunndealpr — Thu, 06 Jun 2013 20:33:31 GMT

Hey there. I have many Excel files which each have four columns of data (A, B, C, and D) and tens of thousands of rows. I need any email addresses present in column D extracted to column E. What's the best solution here? The text surrounding the emails is always different (ie the emails are not surrounded by <> or any other kind of constant characters that can simplify this).

I've tried DigDB but it seems to have a run-time error 6 and/or 1004 issue no matter what I do. DigDB also misses more than a few emails that I can see in plan view. I've tried a couple other programs but they save the emails in a separate document, and I need the emails saved in the original file alongside the data it was extracted from, so that's no help to me.

Any ideas? Thanks in advance.

a function that deletes all rows with no value in a certain column?

dunndealpr — Thu, 06 Jun 2013 19:44:41 GMT

Hey all, I use Excel 2007. Can it be set up to delete all rows in an excel file when there is no value in a certain column? For instance, every row with no value in column H would automatically be deleted?