Re: Block User From Saving
Make the file read only - that will prevent Save but allow Save As
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: 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