Microsoft Excel

137K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

Excel Pivot Table - Calculated Fields

The attached file has a formula in column H which identifies each row as "HIDE" or "SHOW", which allows me to find unique TAG_Number using that field in a pivot table.

I would like to know if and how this might be done using a pivot table without the field Criteria which has the formula.

I have removed most of the columns which makes each row unique.

The goal is to find a list of Unique TAG_Number based on the formula.

On tab results is a list that I am after.

To say it another way:  How would I get my results if I do not use the formula in column H?
CalculatedFields_V1.xlsx
0
Excel formula for fluctuation



the spreadsheet screenshot below represents the number of articles(items) that a company sells every month.
 I would like a formula that calculate the growth (positive or negative) of the sale.

Thank you
 ex
0
Dear Experts:

On a worksheet named 'custom_sets' of the active workbook I would like to perform the following action using VBA.

Check for cell entries in Column E. If cell entries are found (i.e. Cell <> "") then enter the term 'Quantities chosen' in Column F on the same row.

Thank you very much in advance. Help is very much appreciated. Thank you very much in advance.

Regards, Andreas
0
Hello I am trying to create an Excel that would display a summary of values in graphics like a dash board, how can I go about it?  Are there add-ons or VBA or other apps that is design for this?  Thank you.
0
I have given out 10 Excel for filling out forecast numbers, expected expenses, expected sales, etc and want to merges these 10 Excel to one.  Is there a tool for this or Excel has this capability?  What is the way to get this working?
0
Dear Experts:

I got hundreds of smart tables spread over a lot of worksheets.

I would like to run a macro on all of these smart tables with the following requirements:

Loop thru all smart tables in all worksheets with the exception of worksheets named 'base_sheet' and 'final_sheet'.

Enter the term 'Quantities per one set' in the third column (ie. e.g. C2 or C 23 or C 84)  of all header rows, overwriting the cell contents. All of the smart tables have header rows.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Hi Experts

Hope you can help

I’ve already received some expert assistance on this workbook but unfortunately am having trouble with the criteria set up on the Conditional Formatting

The main issue is that the sheet titled Results Costs looks at the Data tab - Columns R to T. A lookup in the conditional formatting on the Results Costs sheet should return the appropriate colour based on the value in column T on the Data Tab against the corresponding location (eg. 01_01 has a value of –£241.60 therefore the location cell on the Results Cost sheet should be red)

However, as you can see in the example, the cell containing 01_01 location on the Results Cost sheet (cell A21) is an orange colour instead of red (value less than -100)

Ive tried to amend the criteria against the conditional formatting however it does not seem to work as well as trying (unsuccessfully) to include an IF AND logic that I could not get to respond

Any help would appreciated with this as Im completely flummoxed

Regards
J
Copy-of-Copy-of-Cycle-count-pick-fa.xlsm
0
Want to create an Excel macro that will take someone to the desired sheet by using the contents copied into the clipboard, but I have two problems:
1. When I run the macro below, I get a "Compile Error:  User-defined type not defined" on the first line.  The help reference didn't help (https://docs.microsoft.com/en-us/office/vba/Language/How-to/user-defined-type-not-defined).
2. When I tried to save the macro, it said that in order to save it with a VB Project, I needed to "choose a macro-enabled file type in the File Type list, but I couldn't find the File Type list under any of the menus and couldn't find it in Excel Help.  Thanks.

Dim DataObj As New MSForms.DataObject
Dim myString
DataObj.GetFromClipboard
myString = DataObj.GetText
   
Sheets(myString).Select
    Range("A1").Select
0
Excel - Strike through not working again????

Cannot understand why this is not working.
StrikeThrough.xlsm
0
Excel - IF and OR statement

=IF(AND(P821>1001)*OR(N821="O",W821<>"Test",W821<>"Flush",W821<>"Open",W821<>"Run"),"HIDE","SHOW")

This should return a HIDE but it is returning a SHOW

P821 is  <  1001   but   N821 is   "O"   and   W821  is  <>  equal  to  Test or Flush or Open or Run

I think this is the order of what I need:
HIDE  - If   P   > 1001
then
HIDE -  If  N  =  "O"    AND   <>  Test or Flush or Open or Run
0
Dear Experts:

I got a set of ActiveX-Control checkboxes on the active sheet whose names all start with 'my_chbx_'

I got another set of ActiveX-Control checkboxes on the active sheet whose names all start with 'chbx_'

For the second set of these ActiveX-Control checkboxes (names starting with 'chbx_') I would like to insert a toggle button that performs the following action:

Set all checkboxes to Value = 1 and run the actions on that value
Set all checkboxes to Value = 0 and run the actions on that value

Help is much appreciated. Thank you very much in advance. Regards, Andreas
0
Dear Experts:

On the active worksheet I got 519 check box controls (normal form checkboxes, not Active X control checkboxes).

They are named as follows: m_cb0001, m_cb0002, m_cb0003 to m_cb0519

I would like to run a macro with the following requirements:

If All of these checkboxes have the ControlFormat.Value = 0 the macro has to say so and exit the sub. If not, another macro called 'MyMacro_2' is to be called up.

The checkboxes in question (m_cb001,m_cb002 etc.) may also be hidden, i.e. the rows in which they reside may be hidden. So the macro has to look for the controlformat.Value in all of them, no matter if the checkboxes are hidden or not.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Dear Experts:

I would like to run a macro on the active worksheet with the following requirements:

If ALL of the rows 23 to 541 are hidden in the current worksheet, the macro has to say so in a msgbox and the macro is to exit.
If any number of the rows 23 to 541 are not hidden (it may be just one, or two etc. that are not hidden in that range) the macro is to call up a macro called 'Copy_Cells'

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Excel - strike through issue   VBA and or  formula  not working ???

Please see attachment and explain why the formula is not working.
StrikeThroughIssue.xlsm
0
Hi Experts,

This is in reference to the following.
I would like to have the lunch column getting populated with the correct amount.

Thanks in advance.
0
Dear Experts:


I would like to check if the 'Wss' Worksheet's used range has data, i.e. the worksheet is blank or not ...

The macro however should only check the visible cells, because there are thousands of hidden rows which contain data.  

If all of the visible cells are blank (again, the invisible / hidden cells are not to be considered) the macro is to say so and the macro is to exit

Help is much appreciated. Thank you very much in advance.

Regards, Andreas



Sub Check_For_Blank_Cells_in_Visible_Cells_Only

Dim Wss As Worksheet
Dim i As Integer

Set Wss = ThisWorkbook.Worksheets("Customized_Choice")


End Sub
0
I have an excel Macro that I have been running on Windows 7 for years without a problem. I recently updated to Windows 10.
Every time I run the macro now I get the Error 1004.

Below I put the VbScript that it does not like.
Any ideas how to fix this problem?

Thanks...
RIck

Error 1004
Method SaveAs of Object   Workbook failed

ActiveWorkbook.SaveAs Filename:= _
        "M:\IT\Macros\ESCOffTheShelf\Web-ESCUploadInventory1.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
0
Excel - Lowest and Highest

I have a sheet with tag numbers in column A on tab1 - column P  is the a number
Each tag may exist multiple time with different numbers in column P
Column A (tag number)   Column P (number)
123456                                        8760
123456                                       35040
123456                                         4380
123456                                         8760
123456                                       35040
123456                                         4380
123456                                         8760

On tab2, I have a list of unique tag numbers and I need to pull the lowest and highest number for each tag number
Column A (tag number)    Column B Lowest     Column C Highest
123456                                           4380                                35040
0
I need help with a function that will filter a two dimensional VBA array based on some criteria and store the filtered elements into a new array.
What I need would be clear from the code included.
As you would note I am running a loop to filter a startingArray and storing the matching elements in filteredArray.
The code I have posted works but in this case I was able to ReDim filteredArray(1 To 3, 1 To 3) As Variant because I knew how many elements I would get in the filtered array (in this case 3). In a real scenario I would not be able to do this because I would not know in advance in how many elements in my starting array would match the filtering criteria.
I would appreciate your help in the correct way of doing this.

Private Sub FilterTwoDimensionalArray()

Dim startingArray() As Variant
ReDim startingArray(1 To 6, 1 To 3)

Dim filteredArray() As Variant
ReDim filteredArray(1 To 3, 1 To 3) As Variant
' In the above line I am able to Redim filteredArray to (1 To 3, 1 To 3)
' because I know in advance how many elements filteredArray will need to accomodate
' as I can see the data


startingArray(1, 1) = 1
startingArray(1, 2) = 3
startingArray(1, 3) = "This is an apple."

startingArray(2, 1) = 4
startingArray(2, 2) = 9
startingArray(2, 3) = "He looked happy."

startingArray(3, 1) = 10
startingArray(3, 2) = 12
startingArray(3, 3) = "This is an apple."

startingArray(4, 1) = 13
startingArray(4, 2) = 16
startingArray(4, 3) = "This is a good 

Open in new window

0
Dear Experts:

I got an .xlsm file which only should be saved as .xlsm file by the user.

Is it possible to restrict the user to just save it as .xlsm-file and not as an .xls or .xlsx or other file type? And If yes, how would this VBA code look like?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Dear Experts:

There is a built-in command in MS Excel that says:

File - Share - E-mail - as attachment (Excel 2013-2016) or
File - Save&Send - as e-mail attachment (Excel 2010)

For some specific reason I would like to intercept this built-in functionality in the following way.

Right before the user clicks the Save&Send (or Share) command I would like to run a macro code.

Is this possible?

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas
0
I have a workbook that I have copied and pasted a sheet into workbook 2.

Both workbooks are exactly the same but its finding data from workbook 1 in workbook 2.

How do I change links in workbook 2 so it links to data in workbook 2 without having to copy and paste links for all workbook again
0
Hello experts,

I am looking for the best way to built 3 procedures:

1-Display last used column letter in activesheet
2-Display last used row number in activesheet
3-Display initial Range and end range of activesheet example: A1:C4000

Thank you very much for your help.
0
Dear Experts:

I got a form with Active X Control boxes.

In the below code only Range("F23") is checked whether it contains the boolean value 'False'.

I would like to expand the IF statement so that Range "F23" to Range "F85" is included, i.e. the IF statement is true
 if all cells in that range ("F23" to "F85") have the boolean value of 'False'.

I do not want to add more than 60 'And' formulas to my vba Code. I am sure there is a quicker and shorter way.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

Sub Check_for_value_in_a_range()

If ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = True And ActiveSheet.Range("F23").Value = False Then
ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = False

End If

End Sub

Open in new window

0
I'm having a problem with getting a specific result with an Excel Formula.

As shown in the image below, I'm trying to match data from two Sheets (which represents data from two different sources. For the sake of the picture, the two sheets are shown together). The problem is that one sheet has multiple entries for the same Invoice #, where the other sheet has only one entry. What I need to be able to do is the following:
  1. If 'Value' from Sheet2 is greater than 0, and matches "Item C to match" entry in corresponding Invoice #, then 'pink' Conditional formatting does NOT Apply to any Item for corresponding Invoice # (See red rectangle). This is my problem I'm having, as it applies to items A, B & D which should not be highlighted in 'pink'. "Item C to match" here IS correctly NOT highlighted.
  2. If 'Value' from Sheet2 is greater than 0, and does NOT have a corresponding "Item C to match" entry for the corresponding Invoice #, then apply 'pink' conditional formatting to all items for that corresponding Invoice # (See green rectangle). This works correctly.
  3. If 'Value from Sheet2 is '0' and matches "Item C to match" entry for corresponding Invoice #, then apply 'pink' conditional formatting, but NOT for any item of the same corresponding Invoice #. This works correctly.

Tricky Matching Criteria

Current 'Conditional Formatting' Formula:
=IF(NOT(B1="Item C to 

Open in new window

0