Free Microsoft Excel 2013 Quick Reference

Workaround missing "repeat item labels" function in excel 2007

does anyone know a workaround for the "repeat item label" function that exists in 2010 but not in excel 2007 for pivot tables?

Capture.PNG

thanks very much


Using the VLookup Function in Excel 2007

</img></img>
Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position ...

Hi,

I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula:

{=SUMIFS(range to sum, range to evaluate, evaluation criterion)}

The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999.

So my formula looks like this:

{=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)}

This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?

Thanks!

I used to use Form function in Excel 2003 to create form function with macro such as drop down listing, arrow up and down to adjust the numbers. Where can I find similar function in Excel 2007? I added in the from function in the ribbon tab but this function is not the same as what I mentioned.

Any help?

I am wondering if this is by design or if it is a bug. When you use the Sort
button in Excel 2007 and then try to repeat the sort action again either by
pressing F4 or Ctrl + Y nothing happens. The repeat function seems to work
fine for anything else except sorting. Is this normal, a bug, by design or
what? I have tested it in Excel 2003 and it works perfectly. Please help.
Appreciate any help or advice on this.
--
Thank you.
MeekilB

I'm new to excel but reckon it can help me do a lot of the mundane tasks i've been landed with so - I'm attempting to work out overdue invoices using IF AND function in Excel. The worksheet is one I use for work so cannot be changed - I have to work with what's there. Attached - it's quite big so I've deleeted some of it ...

In column AA I've created a formula that looks at the current month entered in month in AA2 and then checks to see if invoices are overdue for the preceding months - trouble is there are 12 months that I need to check and the function only allows me to check 7.

Formula:

=(IF((AND(AO3>0,current_month="April")),"Due",IF((AND(AO3>0,current_month="May")),"overdue",IF((AND( AQ3+AO3>0,current_month="June")),"overdue",IF((AND(AS3+AQ3+AO3>0,current_month="July")),"overdue",IF ((AND(AU3+AS3+AQ3+AO3>0,current_month="August")),"overdue",IF((AND(AW3+AU3+AS3+AQ3+AO3>0,current_mon th="September")),"overdue",IF((AND(AY3+AW3+AU3+AS3+AQ3+AO3>0,current_month="October")),"overdue","OK "))))))))

anyone got any work around or can suggest how I can do this more easily using another function? - bear in mind I have no power to change how the file is laid out!!

I have a next function that stopped working in Excel 2007, what should be changed?
Function GetFolderPath() As String
    Dim oShell As Object
    Set oShell = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Select folder to retrieve files:", 0, "c:")
    If Not oShell Is Nothing Then
        GetFolderPath = oShell.Items.item.Path
    Else
        GetFolderPath = vbNullString
    End If
    Set oShell = Nothing
End Function
The function is showing GetFolderPath message box.

Good morning,

We seem to have lost some functionality in Excel 2007 pivot charts. Can anyone confirm this? I hope I'm mistaken and that there are some parameters or options I haven't found yet, but I fear not.

In earlier Excel a pivot chart retained its own row and column labels and drop down filters independently of the underlying pivot table. This meant that you could drag and drop these around on the chart and filter in the usual way. In 2007 we sem to have lost this functionality, and the only way to interpret or modify a chart is to go back to view/change the underlying Pivot Table.

I hope I'm wrong. Can anyone comment please?

See attached file containing pictures of the tables and charts.

Hi,

I was running a user defined function in Excel 2007, is working fine but when i apply in excel 2000 it was failed and with the result #values!

Would it be the coding below cannot run at excel 2000? Any setting to run the UDF in excel 2000?
Thanks and appreciate your help.

Below is the function created:
Option Explicit
Function LookupAll(vVal, rTable As Range, ColumnI As Long, ColumnII As Long, ColumnIII As Long) As Variant
Dim rFound As Range, lLoop As Long
Dim strResults As String

With rTable.Columns(1)
Set rFound = .Cells(1, 1)
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, vVal)
Set rFound = .Find(What:=vVal, After:=rFound, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

strResults = strResults & "; " & rFound(1, ColumnI) & "," & rFound(1, ColumnII) & "," & rFound(1, ColumnIII)
Next lLoop

End With
LookupAll = Trim(Right(strResults, Len(strResults) - 1))
End Function

When I am in excel (2007) the up & down arrow keys are not functioning. Typically when the up/down keys are used it will navigate me around the different cells. Today however it is not, instead it simply moves the entire excel sheet either up or down.
Anyone ever experienced this? Definitely could use any sort of advice as this is very frustrating.

I have a worksheet where I have the records sorted in a particular order. I am using the remove duplicates function within Excel 2007. I am hoping that it will automatically keep the first record found for each set of dupes. Can anybody confirm if this will be true.

I have several text strings in single-byte Japanese I need to convert to
double-byte Japanese. The JIS and ASC functions in Excel are listed in Excel
Help, but they are not available in Excel 2007. Attempted use generates a
NAME? error. I am running English Vista, and I have Japanese fonts installed
in both Vista and Office 2007. How do I get the JIS & ASC functions to load
/ install / work?

Hello,

I have several xls running on Excel 2003 that use the RTD function to
invoque an RTD server to collect data from an external source. However, I
just migrated to Excel 2007 and the RTD calls stopped working. They all
return #N/A and never update.

I wrote a simple RTD server (from the msdn pages) to test the RTD
functionality in Excel 2007 and under debugging I see that the RTD server
entry points are never called by Excel. I played around with all the new
security settings to the point where I just enabled all macros and everything
else, still with no result.

I know that RTD is not supported by Excel Services but I see no reason why
it would not work under Excel 2007.

Could anyone comment on this?

Thanks,

Jose

I need to design a worksheet where I need to put in 7 criteria. My patients
can get admitted in "Superdeluxe room" "Deluxe Room " "ICU" "General ward" ,
"ICU And then Deluxe room " Etc. If I have the case function in Excel then, I
can tell excel to put "a " amount for the "Deluxe room, "Y" amount for the
superdeluxe room etc. The IF function is too cumbersome.

This is the first time I'm trying to use a user defined function in excel. I want to use it in the columns I & J. I already have the formulas in column I & J.

In Column I :
=((H3/20)/G3)

In Column J:

=IF(A4="","",IF(SUMPRODUCT(--($A$2:$A4&$F$2:$F4=A4&F4))=1,(20-H4)/20,(20-SUMIF($F$2:$F4,F4,$H$2:$H4))/20))

Now here's the user defined function which I've got from my friend which I can use.
Public Function SplitNames(NamesCell As Range)
Dim vecNames As Variant
Dim cell As Variant
Dim yourPcge As String
Dim tmp As String

vecNames = Split(NamesCell.Value, ",")
For Each cell In vecNames

yourPcge = "xx" ' replace with code to calculate percentage
tmp = tmp & cell & " : " & yourPcge & " ; "
Next cell

SplitNames = Left$(tmp, Len(tmp) - 3)
End Function How do I use the formula I've in column I & J in this user defined function in the code-line in italics? Also, how do I use this in the excel I've created.

I need to design a worksheet where I need to put in 7 criteria. My patients
can get admitted in "Superdeluxe room" "Deluxe Room " "ICU" "General ward" ,
"ICU And then Deluxe room " Etc. If I have the case function in Excel then, I
can tell excel to put "a " amount for the "Deluxe room, "Y" amount for the
superdeluxe room etc. The IF function is too cumbersome.

I was wondering if anyone could shed some insight on an issue I'm having.

I have some standard bar charts that look great in Excel 2000/2002. But when I open the workbook in Excel 2007, the axis data labels are cut off after a single letter. It's the x-axis labels, not the axis title or data labels. See the attached image for what I mean.

I've looked around for possible fixes, but none of the following work:Adding spaces or periods after the label textsChanging the zoom or screen resolutionChecking print preview to see if looks okay then
Does anyone have any ideas? I'd make new charts in Excel 2007 but there are a lot of them to make, and I don't know if it will just happen again!

Issues that are fixed in Excel 2007 by the 2007 Microsoft Office ...

However, in Excel 2007, the new series does not resemble the previous primary ... the performance of the NORMDIST , NORMSDIST , NORMINV and NORMSINV functions in Excel 2007.

Multithreading and Memory Contention in Excel 2007

Most of the built-in worksheet functions in Excel 2007 are thread safe. You can also write and ... pxRetVal must be freed later by xlAutoFree12 LPXLOPER12 pxRetVal = new XLOPER12; ...

Improving Performance in Excel 2007

... because it does not involve volatile functions like OFFSET coupled with counting functions ... All user-defined functions in Excel 2007, regardless of the language and add-in method ...

I hate the new filters functionality in Excel 2007. Instead of the classic style dropdown list, you have checkboxes - which can be a pain if you use filters often and have to keep checking and unchecking boxes. Is there a way anyone knows to use the classic style filters in Excel 2007?

Thanks!
Scott

Hi,
In previous versions of Excel you could change the values by adjusting the
series in the graph. I can not find that function in Excel 2007.
Can someone help me?

In excel 2003 you could change your pagebreaks in preview mode before
printing. My question is where do I find this function in excel 2007.

i don't know vb.is there any formula or function in excel 2007 to do the
following convertion.
number "100" convert in to text"hundred"

Improving Performance in Excel 2007

All user-defined functions in Excel 2007, regardless of the language and add-in method ... multiple dynamic ranges with a single column requires special-purpose counting functions.