Free Microsoft Excel 2013 Quick Reference

Convert function not working

Sir/Mam,
I tried this function =CONVERT(2.5, "ft", "sec") but it is not working. it
is giving #name?

I m using office 2003.

yours

Rao Ratan Singh


hi to all!!

Im having problems with the vlookup function, I have two big ddbb of about 30x1500 each and im trying to add data from one of them to the other using the vlookup fuction. the common field for both DDBB is the id number. this numbers match one to one in both DDBB (i have checked that by doing a pivot table) but when it comes to comparing both ID numbers on Datbases. Many Id numers are not recongnized by the vlookup function giving me an N/A like the number wasnt there.i check one ID number in both ddbb and they are the same. I dont know what is the cause for that .All the fields are numbers.
I hope any of you have an idea of what is happening, the issue looks that easy that i dont know what ican try
I have attatched a little example of both data bases and the vlookup function not working
thanks in advance to you all!
Carlos

I have attached a file. They are shoe sizes, and I need the spaces removed in each cell. Not sure why the trim function isn't working. I've tried formatting the cells a million different ways.

Trim function...not working
copy, paste special values only....not working.
Clean function...not working

Please help. Thank you!

In cell a1, I have the following sumproduct equation,
=sumproduct(b1:b5,b6:b10). The data in b1:b5 is formatted as currency. The
data in b6:b10 is percentages; the percentage values come from a link to
another worksheet.

The sumproduct functions works fine on the original worksheet. However, I
made 10 copies of original. For 4 of them, the sumproduct function does not
work. Cells b1:b5 have numbers greater than zero. The percentages in b6:b10
are 100%. Therefore, cell a1 should have a number greater than zero. Yet,
cell a1 shows only zero.

If I eliminate the links for the percentages in cells b6:b10 and instead
manually input the percentages, the sumproduct equation in cell a1 returns
the proper value.

I have two mysteries to solve:
1. How come the sumproduct equation works on some of the copied worksheets
but not all?
2. How come the sumproduct equation works if I convert the percentages from
linked data to manually inputted data?

Thanks,
Scott

Hi guys,

This is my first post here and I am pretty new to VBA and completely new to UDF. Today I built this function to be used in a code, but I don't understand why it is not working.
This function should return true if finds in the array the value (myvalue) and false otherwise.


	VB:
	
 
     
    For i = 1 To UBound(myarray) 
        If myvalue = myarray(i) Then 
            ismember = True 
        Else 
            ismember = False 
        End If 
    Next 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
then I called the function

	VB:
	
 Equity(3) 
 
Equity(0) = "Common Stock": Equity(1) = "Mutual Fund": Equity(2) = "Preference": Equity(3) = "Depositary Receipt" 
X=Ismember(Equity,typ) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
typ is a string that takes the value from a cell.
The function returns false also when it should be true.
I managed to achive my goal using match, but I would like to understand why it is not working so I can learn a little more about UDF

Thanks

Hi
I have two columns set up with drop box list from data validation. One is subdependent on first one using indirect(substitute function). I am trying to use sumproduct function to count number of occurence such as fire and fire alarm. Sumproduct function will not work for some reason with drop box input values but instead if I type Fire and Fire Alarm into column 1 and 2, It will register? What is the problem? does excel treat drop down list values differently from manual input? Please Help, greatly appreciated, thanks.

Hi guys,
I'm at a loss to understnad why the vlookup function in the attached spreadsheet is not working. I have copied it from another spreadsheet where it is working fine but I can't ascertain what is different. It is supposed to put the week number in J1 based on what is selected in G1 and matched against the entry in the Dates sheet.
Could anyone offer any assistance?
Thanks!

I created a spreadsheet for my checkbook it has worked perfectly for the last few months. This week however the sum function has stopped working. This is the formula I had set up =SUM(H359,F360,E360) (it continued all the way down the screen) The E column is for deposits, the F column is withdrawls and the H column is the balance. All I had to do was enter an amount into either E column or -amount into F column and H would automatically change. It is no longer working. I now have to go into the H column and access the formula then hit enter for it to total. I have to go into every cell below that and do the same thing to keep the balance correct. It isn't working on any of my spreadsheets where the total is supposed to automatically change. (it's not working at home or at work) Help! It's getting to be a pain to manually total it each time.
Thanks!

I cant get the below code that calls the function (below) to work. Can
anyone tell me whats wrong with it?

'Loops through B100 to find a match
Counter = 1
Do
Counter = Counter + 1
'Calls the Closed workbook reference code to be used in
the match loop
p = "Q:CS Management ReportsReports Setup"
'p = "scgvlfs05sesfaCS Management ReportsReports
Setup"
f = "Authorized List.xls"
s = "Reports Setup"
a = ("B" & Counter)
'If 5 and 3 MATCHES an Authorized ID, perform found action
RemoteValue = GetValue(p, f, s, a)
If NN = RemoteValue Then
MsgBox ("Profile " & NN & " is authorized. Click OK to
continue")
MatchFlag = 1
Exit Do

Below is the function the code above calls.

Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) "" Then path = path & ""
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

It will not work if the workbook is closed like it is
supposed to do. Why does it only work when the workbook
is open?

When I copy cells in Excel 2007, then try to paste it within the same
worksheet or a different worksheet, the Paste function is not highlighted.
This function has worked just fine in the past, but all of a sudden I can't
select Paste.

I'm trying to apply conditional formatting to a spreadsheet using the IF
function. I know I'm using the correct formula. But the formatting is not
being applied. It's like the function is not working.
--
DruD

The first and second IF functions are working, but not the third:
=IF(B1=B2,C1*.01,IF(B1>=B3,C1*.0 12)))
I assume it has something to do with the >= but I don't know another way.
Thank you.

Here is my function:

=LOOKUP(A18,'Week 1 Totals'!A18:A94,'Week 1 Totals'!J18:J94)

For some reason this is not working for me. I am trying to find a
total number of hours worked for a given job. A18 is a job number.
A18:A94 on week 1 totals sheet is a range where the job numbers are
located for each job. J18:J94 on week 1 totals sheet is where the
total hours are for each job. For some reason this function is
returning 0 even though J18 = 1 where A18 on the current sheet matches
A18 on the week 1 totals sheet. So it should return 1. I cant figure
out why its not working. Any one know why.

--
hcamelion
------------------------------------------------------------------------
hcamelion's Profile: http://www.excelforum.com/member.php...o&userid=36736
View this thread: http://www.excelforum.com/showthread...hreadid=568541

For some reason, the copy tab function is not working for me. I can move
Tabs, but when I highlight the tab(s) and select "copy", nothing happens.

Suggestions?

Thanks!

I have a simple comparison function that is not working.

=IF(AD110=AI110,AD110,"")

Basically, I am comparing two cells that both have a value of 1. I expect to get 1 in the new field but it is blank. The formatting is set to general on both fields. I have also run into this problem before and am wondering if anyone else has seen it??

This does not occur if I just start a new sheet and enter those two values, so it isn't the function itself. I'm wondering if this type of thing can somehow occur if you copy data over? This data came from a csv file that was opened into excel.

Also, the cells in column AD are also a calculation of another formula.

I have an excel spreadsheet I want to make multiple columns of data change all from all caps to just the first letter of each word caps. I tried using the PROPER function as directed but it's not working.. I can only get it to do the function for one cell at a time. I put in to go from A1:A798. It only does A1. What am I doing wrong?

Hi Everyone,

I came up with a macro to save a .csv file generated by a database program I use with the date and time. It then deletes the original file so that another can be generated with the same name (the database program always generates files with the same name and will not replace older files with the same name)

The macro brings up the Save As window with the correct file name and type, however when I click the save button nothing happens, the window closes but it does not Save a Copy of the file with the new name.

Because nothing happens the Kill function will not work either because the Kill function does not work on files that are currently open.

Here is the code:

Sub SaveFileAs()

fileSaveName = Application.GetSaveAsFilename( _
    InitialFileName:="C:Documents and SettingsusernameMy DocumentsMonthly ReportingMSOWS Daily Bedsheet " +
VBA.Strings.Format(Now, "mm-dd-yyyy, HH:MM:SS") + ".xls", _
    fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
   
    If fileSaveName = False Then
        Exit Sub
    End If

Kill ("C:Documents and SettingsusernameMy DocumentsCLTLST.csv")

End Sub
I have also attached an excel file containing the macro. Obviously some of the file name directories will need to be changed for it to work on other machines).

Thanks for any help or suggestions.

I have one cell A1: 16000200,
in cell B, I want *16000200*, so in cell B, I define a function concate("*", A1,"*"), if I use '*', also not working. any one can help?

The first and second IF functions are working, but not the third:
=IF(B1<B2,C1*.005,IF(B1>=B2,C1*.01,IF(B1>=B3,C1*.012)))
I assume it has something to do with the >= but I don't know another way.
Thank you.

First I want to copy the calculations from sheet "Calc" to sheet "Cost" then in column J in the same row, the background color will change like conditional format on numbers

The code works when copy the calculations from sheet "Calc" to sheet "Cost" but the color function (codes in sheet "Cost") does not work after pasted in sheet "Cost".

But the color funtion does works when enter numbers in Column J alone.

I know there must be a simple codes that can do this while paste from other sheet.

This code is in module

Sub Try()
Dim Calc As Sheet4
Dim Cost As Sheet6
Set Calc = Sheets("Calc")
Set Cost = Sheets("Cost")
    Calc.Select
        Range("C14").Select
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 2).Range("A1").Select
    Calc.Select
        Range("C7").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C10").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C11").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C30").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C17").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C28").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C16").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C23").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C24").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C26").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C29").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C33").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C13").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1").Select
    Calc.Select
        Range("C32").Select
        Application.CutCopyMode = False
        Selection.Copy
    Cost.Select
        ActiveSheet.Paste
        ActiveCell.Offset(1, -15).Range("A1").Select
        Application.CutCopyMode = False
End Sub
And the color function code is in sheet "Cost"
Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
Dim ifont As Integer

 If Not Intersect(Target, Range("j1:j1400")) Is Nothing Then
 Select Case Target
    Case 1 To 10
    icolor = 35
    ifont = 1
    Case 11 To 20
    icolor = 4
    ifont = 1
    Case 21 To 30
    icolor = 5
    Case 31 To 40
    icolor = 45
    Case 41 To 50
    icolor = 3
    Case Else
    'Whatever

End Select

Target.Interior.ColorIndex = icolor
Target.Font.ColorIndex = ifont

    End If



End Sub
Thanks in advance
Color_Function.xls

A coworker recently had a new version of Excel installed and she is having trouble getting her sum function to work. She created a form with a grand total at the bottom, but when you enter numbers in the range of the sum function, the total does not update.

I opened the spreadsheet on my pc and had the same problem. However, when I redid the function, it works fine. Then when I drag the formula to the next cell it does not work as usual.

Is there a setting that needs to be changed?

Any help would be appreciated.

Thanks!

Hi,

I have following sub and it is working...
with the relation of A2 = A1^2

=======================================
Sub MyS1()

'sum = 0

Set inputc = ActiveSheet.Cells(1, 1)
Set outputc = ActiveSheet.Cells(2, 1)

For i = 1 To 10

inputc.Value = i

sum = sum + outputc.Value

Next i

MsgBox sum

End Sub

=================================================
But by function is not working...

=================================================
Function MyF1(inputc As Range, outputc As Range)

For i = 1 To 10

inputc.Value = i

sum = sum + outputc.Value

Next

'MsgBox Sum
MyF1 = sum

End Function
==================================================

Please shed light on this problem for me!!!!!!!!!!!

Hope someone can help. I am losing my mind with this. I have attached the file for you to view. I have two drop down boxes which serve as the inputs to an Index function with 2 nested Match functions. The Index function works for the first 10 items of the "Largest Pipe Size" drop down, but does not work for any of the other. I have tried multiple versions of the forumla and formating the cells...nothing seems to work.

Here is my formula:
=INDEX($G$6:$P$44, MATCH($C$7,$H$6:$P$6,0), MATCH($C$6,$G$7:$G$44,0))

Any assistance would be Greatly Appreciated!!

Austin

Hi all excel gurus

I have hit a brick wall and would appreciate some input.

I have a formula in a worksheet called Trading FY1011, which returns the interest for a particular financial year once a match occurs in an interest worksheet called INTEREST FY1011.

In Trading FY1011, cell A1 = FY1011 which is returned from the sheet tab name ie Trading FY1011.

The current formula which is specific to one worksheet works a treat:
=INDIRECT("'INTEREST FY1011'!B"&MATCH(J5,'INTEREST FY1011'!$A$1:$A$50)).

What I want to achieve is to make the formula generic so that as new trading worksheets are created for future financial years the formulas reference cell A1 which will update to reflect the current sheet financial year status eg FY 1112, FY 1213 etc.

I have managed to modify the INDIRECT component of the current formula. So when I substitute
"'INTEREST FY1011'!B" with "'INTEREST"&" "&A1&"'!B" it works well as in the second formula below ie.
=INDIRECT("'INTEREST"&" "&A1&"'!B"& MATCH(J5,'INTEREST FY1011'!$A$1:$A$50)).

However when I try to modify the second element of the MATCH function I hit the snag. When I substitute 'INTEREST FY1011'!$A$1:$A$50 with "'INTEREST" & " " & A1 & "'!" &"$A$1:$A$50" as in the formula below:
=INDIRECT("'INTEREST"&" "&A1&"'!B"&MATCH(J5,"'INTEREST" & " " & A1 & "'!" &"$A$1:$A$50")) it fails.
Can anyone highlight if there is something missing or is the second element of the MATCH function not receptive to the referenced data in cell A1.

Many thanks for any insights.

Cheers
mgerada

When I use a value from the Left(x,x) function, the workday(x,x,x) function does not work. It returns a NA value. If i use a number instead of the value returned from the left function, the workday function works. Anyone know how to fix this?