Posts by Sicarii

Re: Lookup A Date And Return A Summed Range

Array Formulas <CTRL+SHIFT+ENTER>:

E3=SUM(IF(Sheet2!C2:R2<DATE(YEAR(C3),MONTH(C3),1),Sheet2!C3:R3,0))

F3=SUM(IF(Sheet2!C2:R2>=DATE(YEAR(C3),MONTH(C3),1),Sheet2!C3:R3,0))

Re: Roi For Automation

Yes, thank you very much, that is a great thread and info. In its simplest form I basically outlined how I currently save my site ~64k with automated processes I've created so far and if they would utilize my talents across our other sites, and only 50% of them had processes to automate and only saved 50% of what I do at this site, that it would be 1.6 million dollars in annual savings! So, I'm hoping they won't be obtuse, just gotta wait and see.

Re: Count All Programs Ytd That Have Evaluation Result

post the example with wanted results...

Tried to title this as "VBA ROI"...

I'm just wondering if anyone has ever put anything together relating to automation of Excel through VBA regarding cost savings? I'm drafting up a proposal idea for my company to save them money and sell myself at the same time; always looking to make more money :). To me it's a no brainer when it comes to saving money with everything I've done so far here, but I'm not the greatest with words.

Re: Find File Looking At Extension With String

I would suggest not letting the user control the date naming. Code it to get the date itself and save that way.

Re: Combine And Subtotal Rows

one way...assuming ACTY(colD) is your seperator amongst a few other things

Re: Track Changes By Computer Name In Domain

http://www.vbaexpress.com/kb/getarticle.php?kb_id=768

Re: Looking For More Than One Condition, Than Adding The Value Of Each Condition?

References:
http://www.cpearson.com/excel/ListFunctions.aspx
http://www.cpearson.com/excel/noblanks.htm

Select/Drag C2:D2 down your data, then you can choose from drop down in F5 to get value in H5.

First, we grab the unique entries from colA into ColC, then ColD removes the blanks, finally using menu:Data\Validation in F5 and sourcing our list as the named range of unique entries. Either you, or someone else, can look into removing the blanks from showing up in the validation list.

Is this what you meant by "one step" or were you looking for a single formula?

Re: Looking For More Than One Condition, Than Adding The Value Of Each Condition?

Is the logic that you want to sum colB where the first 3 digits of the 5 digit number in colA match?

Re: Chart Legend Text, Linked To Cell, Not Displaying

If you switch it to Custom Type and then back to Standard\Column it fixes it...

also, if you format E6, Alignment\Text direction as Right-to-left, it will mimic the legend...

So I would suspect that when the chart was created maybe Right-to-left was selected under menu:Tools\Options\International tab or something. If you select D6:H31 and insert a new chart it displays teh legend correctly. I don't see any options of changing the Legend Text Direction.

Re: Chart Legend Not Displaying

right click chart, goto 'Source Data...', Data Range tab, Series In:, try using Rows versus Columns.

Does that display how you want?

Re: Chart Legend Not Displaying

How isn't it? What are you expecting your legend to consist of?

Re: Execute A Program &amp; Load File

looks like autocad has its own vba ide...

http://usa.autodesk.com/adsk/servlet/item?siteID=123112&id=2767040

Re: Conditional Format By Font Colour

http://www.cpearson.com/excel/colors.aspx

Re: Calculate Text Width In Points

http://www.ozgrid.com/forum/showthread.php?t=54274

Re: Pivoting Data With Inconsistent Rows

will the names, or the beginning of a new record, always be bold?

Re: Copying Data From One Sheet Into Another With Script

you would only need to enter the formula and drag it down/copy...maybe attach a small sample to even see if it is a viable solution.

Re: Copy Formula In A Cell Based On A Value In Another Cell

Why not just protect colU so they can't delete it?

ADDED BY ADMIN
Unlock cells (Locked by default) in data entry columns/cells via Format>Cells-Protection.

Ensure formula cells are locked & hidden. F5 > Special - Formula OK. Format>Cells-Protection.

Tools>Protection>Protect Sheet.

Re: Find Item Copy And Paste Each Row And Past Into New Sheet In Excel?

<ctrl+f>
<ctrl+c>
<ctrl+n>
<ctrl+v>

Re: Counting Contiguous 0's In A Row?

Is "1,3,0,0,0,4,5,0,0,0,0,0,0,0,5,3,1,0" in a single cell or does each number represent a cell? i.e.

A1: 1
B1: 3
C1: 0

etc