# Posts by Bebbi

• ## change output to percentage

Re: change output to percentage

use the TEXT() function. e.g. like this: ="result = "& TEXT(A1/B1;"0.00%")

• ## temporarily stop vba function while vba sub is running

Re: temporarily stop vba function while vba sub is running

Try application.Calculation=xlCalculationManual at beginning of sub and app.cal. automatic again at end of sub.

• ## Seek data from an open file in another open file

Re: Seek data from an open file in another open file

=MID(CELL("filename");SEARCH("[";CELL("filename"))+1; SEARCH("]";CELL("filename"))-SEARCH("[";CELL("filename"))-1)
the above will result in the fielname. You may include it in INDIRECT

• ## Concurrent Calls With Start And End Time- How many?

Re: Concurrent Calls With Start And End Time- How many?

in C2: =SUMPRODUCT((A:A<B2)*(B:B>A2)) and copy down

• ## LookUp criteria is between values of consecutive cells (i.e. A1:A2 or A6:A7)

Re: LookUp criteria is between values of consecutive cells (i.e. A1:A2 or A6:A7)

do you mean that if in your above stated example the lookup value were 97 then the return value should be 0 since 97 is closer to A2 than to A1?

• ## multiple columns into one column and autofill question

Re: multiple columns into one column and autofill question

Code
``````Sub reorg()
destline = 2 'line number of first line to be copied into on Reorg Sheet
For i = 2 To 16 ' 16 = number of lines on original sheet
For j = 1 To 6 ' 6 = number of columns in original sheet
Cells(i, j).Copy Destination:=Worksheets("Reorganized").Cells(destline, j)
Next
destline = destline + 1
Next
End Sub``````
• ## LookUp criteria is between values of consecutive cells (i.e. A1:A2 or A6:A7)

Re: LookUp criteria is between values of consecutive cells (i.e. A1:A2 or A6:A7)

regular VLOOKUP with range_lookup TRUE will do, e.g. =VLOOKUP(A1;A3:B7;2;TRUE) where A1 is your 95

• ## TimeData

Re: TimeData

=IF(B2="yes";A2-\$A\$1;"") will give days elapsed since first date
=IF(B2="yes";A2-\$A\$1-MAX(D\$1:D1);"") in column C will give you days elapsed since last YES

• ## Is this possible - generate a report using checkboxes for criteria

Re: Is this possible - generate a report using checkboxes for criteria

Try the following:
link your check boxes to a cells. They will show TRUE or FALSE if checked or not.
In other cells (e.g. B5) something like =IF(B5=TRUE; "=Americas"; "XXXX"). C5 D5 etc. accordingly

Code
``````a = Range("b5")
b = Range("c5")
c = Range("d5")

Worksheets("the data").Select
Columns("A:C").AutoFilter Field:=1, Field:=1, Criteria1:=Array(a, b, c), Operator:=xlFilterValues``````

Hope this helps

• ## Is this possible - generate a report using checkboxes for criteria

Re: Is this possible - generate a report using checkboxes for criteria

just use regular filtering

• ## Complex COUNTIFS track progress in matrix

Re: Complex COUNTIFS track progress in matrix

I suggest you "calculate" the status of each applcant on the control sheet and then set your matrix up to that. For example find the las use cell with

Quote

=MATCH("*";G79:L79;-1)+3

in column M.

• ## Multiple SUMIFS Functions

Re: Multiple SUMIFS Functions

wouldn't know with SUMIFS but this should work:

Quote

=SUMPRODUCT((\$A\$2:\$A\$111=A48)*(INT(\$C\$2:\$C\$111)=INT(C48))*(\$D\$2:\$D\$111))

• ## Convert Data to Line Chart

Re: Convert Data to Line Chart

Quote

=OFFSET(Sheet1!\$B\$1;COUNTA(Sheet1!\$B:\$B)+2-MIN(COUNTA(Sheet1!\$B:\$B);Sheet1!\$C\$1);0;MIN(COUNTA(Sheet1!\$B:\$B);Sheet1!\$C\$1);1)

the above shows the last x number of values in column B. x is set in cell C1.
Amend as needed.

• ## lock cells for a certain time after an entry

Re: lock cells for a certain time after an entry

try this:

Code
``````Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Protect
Application.OnTime Now + TimeValue("00:00:05"), "resetlock"
End Sub``````

Code
``````Sub resetlock()
ActiveSheet.Unprotect
End Sub``````