Posts by Zymurgy1

Re: Block User From Saving

Make the file read only - that will prevent Save but allow Save As

Re: Break Link to Non Existing Workbook

Sometimes the links are in the named ranges - check those.

Re: Consolidate Column Of Numbers To New Column With No Zeros

I used this method (attached) a lot for this situation

Basically, add a column to the left of the original which creates a counter (1, 2, 3, ... etc) of the non zero values.

If your data was in cells B4:B11, the formula in cell A4 would be

=IF(B4>0,MAX(A$3:A3)+1,0)

And copy down

Then use use a vlookup to bring back the counters 1, 2, 3 etc

Re: Nested If: Getting Past 7?

You can use a vlookup and countif to replicate the same results

Populate cells K15:K23 with values 10,20,30,...,90 then use this formula in cell N15 and copy down:

=VLOOKUP(P15,$J$15:$K$23,2,0)+COUNTIF(P$15:P15,P15)

or

=IF(COUNTIF($J$15:$J$23,P15),VLOOKUP(P15,$J$15:$K$23,2,0)+COUNTIF(P$15:P15,P15),0)

to return zero values instead of N/A when column P is blank

Attached shows the two options (highlighted) in colmns S & T

Re: Highlight Row Range- Conditional Formatting

Attached is an example of how conditional formats can help

Re: Capture Negative Months' Data

See attached.

On the "New" tab the first table replicates your data. Cells B4:B51 check if the benchmark is < 0, if it is it creates a counter. The second table to the right brings back the data for all records where there is a counter ie benchmark is below zero.

Re: Opposite Of Concatenate

If cell A1 contained your data then

=SUM(LEFT(A1,FIND("-",A1)-1))

will return the feet

=SUM(MID(A1,FIND("-",A1)+1,20))

will return the inches as decimal (select decimal formatting)

Re: Saving Worksheets In Less Disk Space

If there are many formulas in your file - copy them and paste special as values

Re: Linked File Is Closed Before It Finishes Recalculation

Instead of opening the file, you could try

ActiveWorkbook.UpdateLink Name:=
"X:\gas daily pricing\GD pricing weather call active summer.xls",  Type:=xlExcelLinks

Re: Forcing Recalc After Custom Function Update

if F9 is not working for some reason, try Ctrl+Alt+F9 as it forces a recalculation

Re: Display Text If Another Cell Filled?

This formula assumes that the values in A2:A13 contain the months and B2:B13 contains the values.

="January - "&TEXT(OFFSET(A1,12-COUNTIF(B2:B13,""),0),"mmmm yyyy")

If the months are already in a text format (january, february ...):

="January - "&OFFSET(A1,12-COUNTIF(B2:B13,""),0)&" 2007"

Re: Reference To Another Sheet

Wouldn't it be simpler to use the Edit/Links functionality ie "Change Source"

Re: Removing Non-alpha Characters From Text

=LOWER(SUBSTITUTE(SUBSTITUTE(A1,"-","")," ",""))

converts to lowercase and replaces all hypen and all spaces regardless of position or length

keep nesting the substitute formula if there are other characters such as '

Re: Transpose Column

Assuming your data was in cells A1 to A10: enter 1 through to 10 in cells B1:B10 then sort (A1:B10) in descending order on cells in column B then delete values from B1:B10

Assuming your data was in cells A1 to J1: enter 1 through to 10 in cells A2:J2 then sort (A1:J2) in descending order on cells in row 2 then delete values from A2:J2. To sort a row: select from menu: Data/Sort and choose Options and select "Sort Left to Right"

Re: Dynamically Drawing Lines

This does something similar using conditional formating to shade the cells from start to print

Re: Extract Number Of Variable Length From Text String

If you put sum( ) around the formula it will force the text to a number

ie

=[COLOR="Blue"]SUM([/COLOR]LEFT(B2,FIND(" ",B2)-1)[COLOR="blue"])[/COLOR]

Re: Today's Date Display In Gantt Chart

Add a second conditional format - you have the date in row 3 so use conditional format to check if date in the column = today

see attached

Re: Counting Within A Date Range

See attached:

Re: Calculating Quarterly Averages From Daily Data

Create your pivot table then right click on the "Date" heading. Select "Group and Show Detail" then select "Quarter"

Change values from "sum" to "average"

Re: Last Cell Lookup

If B1 on Sheet 2 contained a header and B2:Bx were the values

=OFFSET(Sheet2!B1,COUNT(Sheet2!B:B),0)

If there is no header and the values started from B1:

=OFFSET(Sheet2!B1,COUNT(Sheet2!B:B)[COLOR="Red"]-1[/COLOR],0)

This doesn't work if there are blanks in Col B