Microsoft Excel Help

Hi Experts

Hope you can help

I have a sheet that is going to be the basis of outlining the performance of our warehouse stock locations in terms of anomalies

Our stock locations are made up of three double digits separated by a hyphen (eg. 01-01-A1)

To obtain these figures I’ve converted the locations by trimming them into the first set of two digits (eg. 01-01) and using SUMIFS to total and display the results.

The main report data is in Columns A to I, with column I containing the stock figures

The trimmed filter column containing the trimmed locations (eg. 01-01) is in Column J

The results data are in Columns O and P and the results data is obtained by using =SUMIFS(J:J,I:I,O:O)

Ive attached the file in question

Unfortunately I have encountered two issues:-
1) Column O, even when converted to text, is producing some of the trimmed locations as Date format and also;
2) There are some locations in the results column (P) that are not present in the location data but still they are producing results from the SUMIFS even though they are not present in the main data in Column A to I (example, 07-19 has 1 as a result in Column P but this is not present in Column J)

Is there a way of eradicating the conversion to dates in Column O and to produce the correct SUMIFS results based on the available data in Columns A to I and subsequently Column J?

Any help would be much appreciated

Thanks
J
Copy-of-Pick-Face-Map.xlsx

4 Comments

Angular Fundamentals

LVL 12

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Want to achive 2 conditions if them are true then YES if one of them are not true then blank

SI((Q89>5.5 Y D92>5.5));"SI"

4 Comments

I have a workbook that has many tabs for players. All of the tabs i want to select and print at the same time all have "PDP" in them. I am using the following code to select all of the tabs through VBA. The code works great with the exception of; if another (non- PDP) tab is selected when the process begins, it adds that tab into the selection. So if the Menu tab is selected when the process is triggered, it will add all the visible PDP tabs to the Menu selection. This creates a problem when i am trying to print them. Is there a way to not have it include the tab it is starting with?

Sub CreatePlayerBinder()
Dim sh As Worksheet

For Each sh In Worksheets

If sh.Name Like "PDP*" And sh.Visible = True Then
With sh
.Select (False)
End With
End If

Next sh

End Sub

2 Comments

I have an sheet in Excel that i am using as a template for a report. When i retrieve the requested data into the sheet i copy the sheet to another tab and value paste the data on the report so it will not change. The problem is that I am using objects on the report (text boxes, chevrons) that i also use a formula. These formulas do not get replaced by the data when i value paste the sheet and therefore their value keeps changing. Is there a way i can value paste the data from an object and keep its formatting?

3 Comments

Hi,

Having trouble deciphering what this formula is in layman's terms.

=-SUMIFS('Expanded Data'!$E:$E,'Expanded Data'!$A:$A,'EO100'!$E$5,'Expanded Data'!$B:$B,'EO100'!A361,'Expanded Data'!$C:$C,'EO100'!B361)

Any help deciphering is apprecaiated greatly.

5 Comments

I have a VLOOKUP that is working:

=VLOOKUP(E2,Account_Map!$A$1:$B$9,2,FALSE)

However it returns 2 #N/A when it can't find the Lookup Value in E2.

Is there a way to have it return the value in E2 if it doesn't exist in the Lookup Table?

Basically only the values that exist in Lookup Table are the ones I want to change.

Thanks in advance. Not quite sure how to search for this or if it's possible within a Vlookup only.

4 Comments

Hi,
I would like an amendment to a PROPER function formula to remove an apostrophe
Formula is placed in A2
Please see attached.

cell A4 is correct output

Many thanks

Ian
Proper-function.xlsx

8 Comments

I have an excel sheet with multiple columns, the first two columns: Name and Date, has a long list of names and dates in many rows. I would like to run code that takes all unique dates for a particular name that i choose. So if i select BOB, i would want all unique dates for Bob in ascending order to be listed on another excel tab. Slight trick, the dates column has some non-date items listed under "BOB" that i do not want. So i really need the code to do 2 things; filter Bob information in column 2 and filter for only what is a date. Is that doable?

21 Comments

hello,

I have sumifs formula:

=SUMIFS('All Raw Data'!H:H;'All Raw Data'!Q:Q;2019;'All Raw Data'!R:R;2;'All Raw Data'!J:J;"<>XXXXXXX";'All Raw Data'!Z:Z;C14)

but I want something "special":)
C14 is a dropdown list which holds names.
Depending on the name I get a result. It works fine.

But I want IF C14 = X then it should just give everything. So the last criteria should then not be taken into consideration. So a total of H:H (still taking in a account Q:Q/R:R/J:J

is that possible?

6 Comments

I need to modify the ribbon in Excel 2016 by adding another tab labeled "Templates". In this tab I want to list template files I've created. When I select a specific template I would like to open the template as a new file. I've searched the Internet but I cannot seem to connect with the right discussion to solve this for me.
What I have been able to do is create a new tab labeled "Templates" and a new Group named Job Costing. In that group there is an icon I've labeled as Costing Packet Item that opens as a new file, but NOT the template file I am needing.

6 Comments

Become a CompTIA Certified Healthcare IT Tech

LVL 12

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Hello,

in the following table every line within revenue is an order.
I want to count the number of orders for the first month except the order for customer C.

How can i do that? countifs doens return an error

month      revenue      Extra      customer
1       € 10,00        € 5,00       a
1       € 20,00        € 2,00       a
1       € 25,00        € 4,00       b
1       € 40,00        € 5,00       c
2       € 50,00        € 1,00       a
2       € 1,00        € 66,00       a
2       € 4,00        € 32,00       b
2       € 182,00        € 2,00       c

1 Comment

Hello,

I have a large sharepoint list. Within an excel file data connection to this list.
In this excel i have created a table were all data is in.

Based on the value's of some of the field i want to make some calculations.
For example, from the table below i would like to know the total revenue of month 1 for all customers expect c.
Is there anyway do this?

month      revenue      customer
1       € 10,00       a
1       € 20,00       a
1       € 25,00       b
1       € 40,00       c
2       € 50,00       a
2       € 1,00       a
2       € 4,00       b
2       € 182,00       c

8 Comments

Wanted to replace data from one column to another.

For example:
On the "REC" Tab - On column W if there is something there then that should go to column H and what's in Column h should be in Column X. The end result should look like what's in the "SHOULD BE" tab. This should only happen when there is something in column W

See attachment.
C--Users-lfreund-Downloads-EE---REC.xlsm

3 Comments

Is there a way to have data on a webpage downloaded in an xlsx file format?

Anything I see seems to do xls (in which Excel warns you about opening the file) or csv... which is non formatted, etc.

Thanks

5 Comments

LVL 1

Question by

LD16

6d agoSolvedPrivate

Excel VBA: add string in multiple columns

Hello experts,

I have the following procedure which allows me to add specific string at the beginning or end a range.
https://www.experts-exchange.com/questions/29132606

Sub Add_Specific_Char()
Dim rng As String
Dim strSpecificChar As Variant 'New declaration in order to add numeric and non numeric values
Dim strCol As String
Dim lngLastRow As Long
Dim lngRow As Long
Dim intWhich As Integer
    intWhich = 0
    intWhich_temp = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")
    
    If intWhich_temp = vbNullString Then
     MsgBox ("No input!")
     Exit Sub
    End If
    If IsNumeric(intWhich_temp) Then
        intWhich = intWhich_temp
        End If
    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select
    strCol = InputBox("Please report column letter in which you want to apply procedure", "Choose Column Letter")
        If strCol = vbNullString Then
     MsgBox ("No input!")
     Exit Sub
    End If
    temp_rng = Application.InputBox("Please select the cell in which is reported value that you want to add", "Select cell", Type:=8)
        If VarType(temp_rng) = vbBoolean Then
     MsgBox ("No input!")
     Exit Sub
     Else
     If VarType(temp_rng) <> vbString Then
     MsgBox ("Cell not a string")
     Exit Sub
     End If

Open in new window

…

9 Comments

I have a macro that needs to run in Windows 10 and Windows 7. My issues is that when it opens in Windows 10 I need to one time manually set references in the VBA Project dialog box. The problem is if someone try's to open the macro from Windows & machine the References are missing. I then manually set the reference and it is fine...until someone opens in Windows 10. Note as soon as the macro opens it automatically compiles and we get an error that reference is missing.

I have tried OnClose to remove references and then OnOpen to set references once I determine the environment, but that does not consistently work.

I know late binding is an option, but I have 25 macros that are used and to do late binding would I assume be a lot of work given the amount of code found in some of the 25 macros.

How can I use these macros in both environments. Making a copy to use in Win10 and then another copy in Win7 is unrealistic since invariably someone will open the Win7 macro in Win10.

5 Comments

Hi Experts

Could point a way to copy an Excel sheet with 03 cels that compose an SQL insertion to a plain text (to use them as SQL insertions) ?

Accordingly to:

Thanks in advance!

2 Comments

Excel conditional formatting NO Match

How do I highlight the items that do not have a match in column S?
NoMatch.xlsx

5 Comments

Excel - Need to determine next due date on an ongoing bases.

Please see attachment
NextDueDate_v1.xlsx

5 Comments

Exploring SQL Server 2016: Fundamentals

LVL 12

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Hello experts,

I am looking for a procedure to cover the following requirement:

1-Read range A1 which contains directory
2-Add at the end of the various files located in reported directory datestamp ie : & Format$(Date, "yyyymmdd") _
& Format(Now, "hhmmss")

Checking:
if A1 doesn't contain a directory string exit sub with msgbox: "A1 doesn't contain a directory string"

If you have questions, please contact me.
Thank you in advance for your help.

6 Comments

Hello experts,

I am looking for a procedure to add to my personal.xlsb that cover the following requirement:

1-Inputbox: Please select the range which contains the string to replace
2-Inputbox: Please select the range which contains the string which will replace the previous selected string
3-Inputbox: Please select the sheet in which you want to apply the replacement

Replace reported in inputbox 1 by the one report'ed in inputbox 2

If you have questions, please contact me.

3 Comments

In column A I have age in years count starting from 0 down to 94. In column B I need to have a formula that will separate age groups by two parameters: 1. I need age group 18 to 39; 2. I need age group 40 and older. Everything else is “not relevant”
Example: A1 = 57 will need B1= 40 and older
       A2 = 18 will need B1 =18 to 39
       A3 = 0 will need B1= not relevant
A4 = 25 will need B1 = 18 to 39
       A4 = 65 will need B1 = 40 and older

4 Comments

I have a table in Excel with headings: Sep 18, Oct 18, Nov 18, etc. I have a total row at the bottom too.

I want to use the indirect function that used given month to define which totals to average. So for example, I might want an average of the last 3 months, or the last 6.

Depending on what I want to see, in cell E62 I have the value Jan 19 and a second cell (F62) with the value Oct 18. Both are formatted as Custom Date fields as they are generated themselves by the eDate function.

I want my indirect function to then pick these up like this:
=AVERAGE(INDIRECT("SALESBYWEIGHT[[#Totals],["& E62 &"]:["& F62 &"]]"))
But I get a #REF! error.

Not quite sure what I'm doing wrong.

Thanks in advance.
Richard

5 Comments

LVL 1

Question by

LD16

2019-02-12SolvedPrivate

Excel VBA: compare rows by rows v3

Hello experts,

I have the following procedure reported at:
https://www.experts-exchange.com/questions/28997130
that I would like to use as a reference in order to cover the following requirement:

1-Perform comparison row by row based on fields reported in 1-Configuration sheet
2-Highlight the one’s that don’t match in red
3-Report the number of rows which don’t match in False related column and the one's which match in True related column

Additional information:

Check configuration should be simplified as following:
If an error is found in the configuration sheet exit sub and display msg:
Unable to proceed check that your config sheet is properly set up

I attached dummy file
If you have questions, please contact me.
Compare-columns-based-on-range-name.xlsx

6 Comments

Question by

Euro5

2019-02-12Solved

Nested if error

I have tried to modify this formula, but now getting error - I know I'm using wrong number of parenthesis or something simple, but can't figure it out!
Can anyone help??

=IF(OR(K2="SPOZ",K2="SPOZDEF"),AB2,ROUNDUP(IF(AND(OR($K2={"SPLB","SPLBDEF","SPLBHW","SPLBDEFHW"}),'Enter Data'!$B$4=""),$G2/'Enter Data'!$A$4,IF(AND(OR($K2={"SPLB","SPLBDEF","SPLBHW","SPLBDEFHW"}),$G2>=('Enter Data'!$B$4*1728)),$G2/'Enter Data'!$C$4,IF(AND(OR($K2={"SPLB","SPLBDEF","SPLBHW","SPLBDEFHW"}), $G2/'Enter Data'!$A$4, IF(AND(OR($A2={"E2","ESP","FO","PO","SO","E2AM","F1","F2","F3"}),'Enter Data'!$B$2=""),$G2/'Enter Data'!$A$2, IF(AND(OR($A2={"E2","ESP","FO","PO","SO","E2AM","F1","F2","F3"}),$G2>=('Enter Data'!$B$2*1728)),$G2/'Enter Data'!$A$2, IF(AND(OR($A2={"E2","ESP","FO","PO","SO","E2AM","F1","F2","F3"}),$G2<('Enter Data'!$B$2*1728)),$G2/'Enter Data'!$C$2, IF(AND($A2="HD",'Enter Data'!$B$7=""),$G2/'Enter Data'!$A$7,IF(AND($A2="HD",$G2>=('Enter Data'!$B$7*1728)),$G2/'Enter Data'!$A$7, IF(AND($A2="HD",$G2<('Enter Data'!$B$4*1728)),$G2/'Enter Data'!$C$7, IF(AND(OR($A2={"GR","PRP"}),'Enter Data'!$B$4=""),$G2/'Enter Data'!$A$4, IF(AND(OR($A2={"GR","PRP"}),$G2>=('Enter Data'!$B$4*1728)),$G2/'Enter Data'!$A$4,IF(AND(OR($A2={"GR","PRP"}), $G2<('Enter Data'!$B$4*1728)),$G2/'Enter Data'!$C$4,IF($E2="PR",$G2/'Enter Data'!$A$2,IF(AND(OR($A2={"IP","IE","IPF","IEF"}), 'Enter Data'!$B$10="",D2="Exp"),$G2/'Enter Data'!$A$10,IF(AND(OR($A2={"IP","IE","IPF","IEF"}),$G2>=('Enter Data'!$B$10*1728),D2="Exp"),

Open in new window

…

3 Comments

Microsoft Excel

Related Topics

Microsoft Excel

Related Topics