MS Excel / VBA help thread

Excel question.
I have a large sheet tracking when I receive certain documents.
In column F i input "received 20120913" when the doc comes through.

I want to sum all the docs I have received, so want to add all the cells that contain the word 'received'. I know SUMIF should get me there but I can't quite get the formula.
Google has not been very helpful. Naughty google.

Any care to help?

Why not put "recieved" in column F and the date in column G, then its a piece of piss SUMIF function?

Yeah, that would be an option if the sheet wasn't tracking 3000 odd docs.
Cracked it any way.

=COUNTIF(F2:F3000,"Received")

I am obviously late to this but Mr Prince - I would probably do some remedial work on the spreadsheet NOW in order to save pain later.

Putting two different forms of info in one cell is usually a recipe for disaster! (No offence!)

Right now, for example, you can't manipulate the dates even though you have gone through all the effort of typing them in.

If you just called column F the "date received" column and left it blank until you had a date to fill in - you would have a much friendlier set of data!

In particular, if you have mis-typed "received" in any of the cells your COUNTIF will be wrong. (Although excel ignores capitalisation IIRC.)

I am sure know this but putting more than one piece of info into a cell is recipe for a disaster.

But if you have stuck with the exact format of your entry "received 20120913" then you can quickly create that new more friendly column.

Just create a new column and copy this cell down the whole thing...

=IF(F2="","",DATE(mid(F2,10,4),mid(F2,14,2),mid(F2,16,2)))

That should do it.

You can just use =COUNT(F2:F3000) to find your number received.

PLUS you can do more "fun" things like count the number received by month or year.
Or create a new column that compares date received with the date the document was created or sent out or whatever (assuming you have that data) and then track the time taken to receive it and tell if things are getting faster or slower...

The exciting possibilities are limitless!! With such a friendly column you are empowered beyond the realms of your excel dreams - but with your existing data you are limited to panicking as to whether or not you remembered "i before e except after c" correctly...

pissing INDIRECT formula not working on closed workbooks. I understand there are workarounds but they won't work on other peoples machines.

Balls balls balls.

Back to a straight VLOOKUP and edit replace each week.

FIND, LEN and LOWER have totally rocked my world today!

If you want people to think you are some kind of Excel ninja, download the ASAP utilities add-on that is a bundle of a load of VBA macros that adds loads of useful functions that aren't present in Excel menus but are things you might need to do.

http://www.asap-utilities.com/

IM is like the Stasi here, no way I can have add-ons.

Initially me too but your network manager + beer = Admin status on your machine.

I'm running Chrome in an office that only just got IE8 in Autumn.

Windows/Excel being what it is these things usually cause more problems than they solve, sadly.

I have all my custom bits loaded in a Personal.XLS file that I've had since I was a nipper (and taken with me everywhere (professionally)).

Initially me too but your network manager + beer = Admin status on your machine.

I'm running Chrome in an office that only just got IE8 in Autumn.

Sounds like my relationship with IT.

Hi all

Desperately hoping for a solution to my problem. I use an Excel spreadsheet on Office for MAC 2011 to keep track on my searches and I use a Hyperlink to link to the webpage that holds the information on that person (LinkedIn etc). Historically I have never had a problem with using CMDK and CMDV to copy the link over, then I type the persons name in the Display box. And this always works.

Except for now !!!!!!!!

I missed the CMDK step and just CMDV'd the link straight into the Cell, now I can't use the CMDK CMDV copy steps at all. It either :-

1. Will allow me to do CMDK, CMDV, then name in the display box - but it won't allow me to click OK or hit return, so sits there in limbo.
   
   

or

1. Won't allow me to do Jack. It won't CMDV into the LInk Box, you can't type anythning in,you can't use EDIT PASTE or anything.
   
   

I have shut the spreadsheet down. Nothing
I have shut EXCEL down. Nothing.
I have powered down and removed the power to the AIR and still NOTHING !!!

ARRRRRRRRRGGGGGGGGGHHHHHHHHH !!! WTF can I do ? ?

It will now not allow me to use these commands in any EXCEL sheet including brand new ones.

Please, please, please can anyone help me ?>?????

Pretty sure it would be CMD C?

Have you just tried copy and pasting without using keyboard short cuts?

Its CMDC to copy and CMDV to paste. And yes, I have used everything, its not the command it just won't allow anything, I can't even type it in !!!

Hmm. thread dredge.

I have lots of centres sending me data.
I can split their records by centre using some vba code I robbed. I've got conditional formats applying colour to blank cells in a record.
I've got column totals counting the number of cells that meet that conditional format.
So centres can see where they're not filling in data. on each of their records.

However, I'd like to spit back to them which records (which have a unique id) have blanks in certain fields.

does that make sense?
HOW DO I DO THIS?
I HAVE NO CODING EXPERTISE but! have a BIT OF TIME to learn now.

[code]Sub which_are_the_blank_cells()

Dim myRange As Range, myCell As Range
Set myRange = Selection.Cells

' For...Next loop goes through all of the cells in the range and
'   tests if they are blank
'   pops up a message with the cell address
For Each myCell In myRange
    If myCell.Value = "" Then
        MsgBox myCell.Address
    End If
Next myCell

End Sub[/code]

That's pretty groovy.

But it's not quite what I'm after (though I am grateful and I should have explained better).

Each record has 100 fields to it. What I'd like is to be able to generate a separate "cheat sheet" so that users from these centres can see that for field 96 they have these records (unique id) with errors (based on conditional format applied to that cell).

Should I turn it off and on again?

You can test for conditional formatting here:

http://www.cpearson.com/excel/CFColors.htm

If you could paste an example of the spreadsheet (just a few lines) then it would be easier to imagine what you're after (though I'm a bit busy today, so I may not be able to look at it in much detail...)

I would test for the underlying condition - i.e. the one that is driving the formatting - It's less likely to break on different version of excel.

centre name record number age date of birth
a 1 37 20/04/1983
b 1 20/04/1983
a 2 37
b 2 37 20/04/1976
a 3 37 20/04/1976
summary (number of blanks) 1 1
summary 3 3
(number of errors in conditional format)

So there is only 1 blank in each column, however there would be 3 conditional format errors (blanks and an age, age wrong from dob, dob but no age).

What I'd like is
Centre A Record number
Error in age? A1

etc.

Any use?

I would test for the underlying condition - i.e. the one that is driving the formatting - It's less likely to break on different version of excel.

I have a count for those cells in a column that are "wrong". What I want (and I know you can do this on a pivot but can't do pivots for 32 centres etc etc) is to say
this column has 32 errors in it for this centre. Which records are this? What is their unique ID number (from column C).

Dear God
It's me Damo.

Why can't my developer team see that this is important? Why do I have to do it myself? Why do I have to ask outside consultancy agents?

Love
Damo

Chalfie - pm me an email address I can send a workbook to. It's probably not exactly what you need, but might get you going in the right direction .

Damo, if you send me a sample of the data I'll sort it out.

Workaround?

Sort the data by centre.
Then create a new bunch of columns that error check each cell in the data: Age blank?, DOB blank? Age error?
Every time they ID an error you insert the unique record number in that row.
So for your third row above checking for DOB blanks put in:
=if(D3=0,B3,"")
That would return the value 2.

So you have a bunch of blanks and some cells filled where there are errors and the data in those filled cells is the record number.

Then sort those new columns individually again so you get rid of the blanks and send that over to each centre.

You would get something like:

Dear Centre A - here are your errors:

Age blank
2
5
16
18

DOB blank
5
7
9

Age error
1
20
26

I am sure others will have better answers.
One thing though - the conditional formatting is a red herring. Ignore that.