Tag Info

New answers tagged

0

You can use Countif with an OR expression like this: =SUM(COUNTIFS(A1:A20,{"Y","N","N/A"})) That looks at one column. It is not clear from the question how you want to treat values in the other column. Options are sum counts where a match is found in the same row of both A and D: =SUM(COUNTIFS(A1:A20,{"Y","N","N/A"},D1:D20,{"Y","N","N/A"})) add up ...


0

I believe the syntax is more like this: =COUNTIFS(Analysis!A1:A1005,"=N",Analysis!A1:A1005,"=Y",Analysis!A1:A1005,"=N/A",Analysis!D1:D1005,"=Y",Analysis!D1:D1005,"=N",Analysis!D1:D1005,"=N/A" The Excel help page doesn't say whether this function can be called from a separate worksheet. Some Functions can only reference a range on the same worksheet, but ...


0

Say we have data in Sheet1. The SKUs are in column A and B can contain any data. The SKUs are sorted: The following macro will produce re-organized data in Sheet2: Sub ReOrganizer() Dim s1 As Worksheet, s2 As Worksheet Dim N As Long, i As Long, K As Long Dim v1 As String, v2 As String Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") ...


0

Look at DirectX and App-V as update vectors. The web install of Office is an App-V package. The DLL you list is for Direct3D. If you can login to your Office365 portal, go to settings, then Setup. You can run prerequisite checking tools to ensure your system is updated.


0

You should investigate the use of Word's mailmerge feature. This allows you to specify an exact template for importing and merging tabular data.


0

A somewhat cleaner way of building the “print sheet” that will show the contents of one of the other 50 sheets is to set cell A1 to =INDIRECT($M$1 & "!A1") which builds a text string that looks like the qualified name of the cell that you want to see, and then uses the INDIRECT() function to de-reference that string (i.e., to treat it as a cell ...


1

I think you need to add a new column that combines the date and time columns into a new datetime. Then use that new column as the x-axis and the graph should be correct. How you do that depends on whether Excel has recognised the date and time columns as such or whether they are text.


4

OK, took some head scratching, but I've got it: Columns O, P & Q as you have them, titled Brand, Product & OEM. Turn the whole thing into a table with Ctrl-T (not required, but handy, and my column R relies on it, but you can use column references if you want) Column R: ...


0

All of your requests can be accommodated. But only using VBA I'm afraid. Keep the dropdown but also add a button that triggers a macro. In the macro, read the cell with the dropdown and send the appropriate sheet to print. Then update the appropriate counter which can be in a set of cells on the same page as the dropdown and button. To count the number of ...


1

For your Pivot Chart to work correctly, you'll need to setup your Pivot Table like this: Legend (Series) Agent Axis (Categories) Date Values Sum of Closed Tickets Your chart will then reflect the changes. This assumes your initial data set has at least those three columns (agent, date, closed ticket count). Once setup this way, you can group your date ...


0

If you select all worksheets (hold shift key and click on every tab) then you can format all of them simultaneously - any change you make to the source sheet will be mirrored in the rest. But that only really makes sense if you're just talking about initial setup of your sheet. If you want changes like this to happen more regularly and automatically across ...


0

As denoted by the resounding silence to your question, this is not really doable because you're asking how to violate the very principle of absolute referencing. The point of absolute referencing is that references stay the same regardless of how you copy or fill the data across, and that is what it's designed to do. At best, I have a work-around for you: ...


0

Here is the macro I used: Dim w As Double Dim h As Double Dim l As Double Dim t As Double Sub StoreDetails() With ActiveWindow.Selection.ShapeRange(1) w = .Width h = .Height l = .Left t = .Top End With End Sub Sub OutputDetails() With ActiveWindow.Selection.ShapeRange(1) .Width = w .Height = h ...


0

As you call yourself a noob I'll get you started along the right tracks... Copy cell with original style: Ctrl+c Select destination cell and paste special: Alt+e+s+v Select formats option to paste only formats of copied cell: t+Enter


1

Your formula should be: =IF([@[CLOSED Y/N]]="y", VLOOKUP([@PRODUCT],'Data Sheet'!$A$31:$B$48,2,FALSE '$' is the 'lock' character for excel (and most similar programs), it stops the program from changing like you described.


2

This analysis can be easily completed by using a PivotTable to do the counting and a PivotChart to visualize. Here is the finished product with data similar to yours. When you set up the PivotTable, you will want to determine the ideal way to do the group of MONTH and EVENT. It is possible to get different charts depending on if they are both rows or if ...


0

This is one way to handle two cells: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then If Not Intersect(Target, Range("D4")) Is Nothing Then MsgBox "Hello World" End If If Not Intersect(Target, Range("F5")) Is Nothing Then MsgBox "Goodby World" End If End ...


0

Yes, it is possible. Please take a look at this solution for example, you will need to adjust it a little bit. As @DavidPostill wrote, "Unfortunately, we are not a code-writing service" :) Dim TimeToRun Sub auto_open() Call ScheduleCopyPriceOver End Sub Sub ScheduleCopyPriceOver() TimeToRun = Now + TimeValue("24:00:00") Application.OnTime ...


1

You can use an Index/Match formula along these lines: =INDEX(B1:ZZ1,MATCH("zzzz",B1:ZZ1,1)) The Match("zzzz"...) will find the last cell with text in the range and pass its number to the Index function. It will be faster than the Lookup approach.


0

You can use the LOOKUP function. If you put the following formula in a cell A1, it will find the last non-blank value between B1 and XX1. Change the XX1 to a column that is expected to be to the right of the last data column =LOOKUP(2,1/(B1:XX1<>""),B1:XX1) Note that you cannot have anywhere near as many rows as columns, so if you have lots of file ...


2

Although Microsoft did not include many keyboard shortcuts in their Office Suite for Mac, you can make your own for any app including Office apps. To do so: Go to System Preferences and then Keyboard. Select the shortcuts tab. You can add any shortcut there either system-wide for any application or application specific by clicking the Plus button +, ...


0

A simpler approach would be to select your columns and insert a pivot-chart Click on the insert tab on the ribbon and select "Pivot Chart" under the pivot table drop down. To graph days of the week on the Y axis, simply drag your "day" field into the row labels category and your "time" field into the values category. Excel, by default, should utilize ...


0

The "=" symbol is what you use to indicate the start of a function. Say you want to autosum cells a1 through a5, and put the result in a6: In cell a6, type =sum(a1:a5) and press Enter. You don't need to press Atl-= to start defining the function. You can also type =sum(), click in the middle of the parentheses, and then click and drag from the first cell ...


0

It is perfectly possibly to convert dates to days of week using simple formatting of the date column. However, Do you want to sum by day of the week? If so, you need a pivot table. The easiest way is to create a new column with the formula (assuming the date is in column A and the new column is B) =$A1 where 1 is the row number. This is easier still if you ...


0

As far as I understand, you would like a VLOOKUP to suggest the student's most recent score, then override it with the actual score, and save the actual score to suggest for the next one? Hence, my answer adds a lookup table to look up the most recent score (technically the 2nd-most recent occurrence of the student, because the moment you add john again, ...


1

If you want to display the whole number simply with a "%" at the end, try this format from the Excel format menu, 'custom' formats: "0.0\%);(0.0\%);0.0\%);@%)" Worked for me: 81.3582914270273 = 81.4%


0

try something like: Sub generateString() Range("A1").End(xlDown).Select For i = 1 To ActiveCell.Row Range("A" & i).Select strString = strString & " " & Selection Next i MsgBox strString End Sub


0

I think you've overcomplicated the problem by attempting to run your cleanup after the data has exited the file with your VBA code. You could duplicate the sheet inside the source file, run corrections on the duplicate, and then move the duplicate out into your new workbook once it's been cleaned.


0

What range are you calling that SortString function on? If it's looping through the entire sheet then that might explain your issue. Consider using a named range, or else embed a 'Count' function so it only runs on rows which have been populated. Not going to ask about the Croissant...


0

I haven't heard of any feature like this and can't see it anywhere in the settings - I doubt this is currently possible in Excel 2013. I'd suggest either reducing the length of your sheet names, or else look at combining tabs to improve efficiency. Think of it like a Database - unless sheets are all displaying distinctly different types of data, it's good ...


0

The new '.xlsb' or binary format tends to be much more stable, and can cut the on-disk file size by ~80% in many cases. I've seen one or two larger files die inexplicably like yours did after years operating as a '.xls', but if you re-save them as an xlsb then this no longer appears to happen. Oddly, if you save from the newly stable .xlsb back into the ...


1

Ok... I admit I was so frustrated I rushed into asking the Internet for help :) I experimented a little, and what worked for me (windows 7 ultimate, 64bit SP1) was repairing your Office 2003 installation! Control Panel -> Programs And Features -> find Office 2003 -> Repair. It may ask for an installation CD. This should helped those who have ...


0

Your XML strings look odd - lots of incomplete tags? Anway I would start from the XML file and load it using the Power Query Add-In. This has a specific "From XML" handler and you can easily shape the data along the way using the Power Query commands. Here's an example I built using an edited version of your XML content (I added a root element and ...


0

Start with a single cell formula to avoid confusion. =SUMPRODUCT((0&MID($H2,1+ISERROR(LEFT($H2)+0),10))+0) Your sample data all start with a letter but the ISERROR(LEFT($H2:$S2)+0) is meant to look for either a number or letter as the first character and add 1 if it is a letter. The LEFT function defaults to a single left-most character and TRUE is ...


0

There is a Python script called wiki2csv referred at the Wikipedia:Tools page that was written for this purpose. This program [wiki2csv] was designed to help edit large tables on wikipedia in the wikitable format. It works by converting the wikitable syntax to comma separated value syntax, that can be edited with Excel or LibreOffice. The result ...


1

Requirements like these are almost always incomplete. One possible formula to get the result as asked in the question is =IF(C2="car",IF(LEFT(D2,6)="Toyota","Toyota",IF(LEFT(D2,5)="Mazda","Mazda","some other car")),IF(AND(C2="Lux Car",LEFT(D2,3)="BMW"),"BMW_Merc","some other car")) For a better answer, you need to create a better question that describes ...


0

Similar to teylyn's answer, you could anchor the starting address in concrete by making its address a text string: =COUNTA(INDIRECT("'0 Data Summary'!B10"):'0 Data Summary'!B108) and using the INDIRECT() function to de-reference that string (i.e., to treat it as a cell address and retrieve the contents of the identified cell).


0

You can achieve the required task using = (Equals to) sign . below I am adding snap of example, first you prepares you first sheet & then go to second sheet & enter = sign then link with first sheet. for example: here I added Items in B5 the linked from second sheet like =Sheet1!B5. whenever you will change the element of B5 from first sheet it will ...


0

Here are a couple of variations: A1 → =OFFSET('2nd Worksheet'!C$1, 3*ROW(), 0) This references the C1 cell on 2nd Worksheet and then moves down three rows × the current row number.  So, when you drag/fill this down, you’ll get A1 → =OFFSET('2nd Worksheet'!C$1, 3, 0) → '2nd Worksheet'!C4 A2 → =OFFSET('2nd Worksheet'!C$1, 6, 0) ...


1

One roundabout way would be to save all macros for each spreadsheet to corresponding text files and compare the same using diff/merge utilities such as WinMerge. If you have a large number of spreadsheets then this VBA code or this VBScript will help reduce manual labour. There's also a purpose-built VBA diff utility called VbaDiff you can try that "will ...


0

Enter the following formula in the A1 cell and drag down: =OFFSET(Sheet2!$C$4,3*ROW()-3,0)


3

You can make the first reference always refer to the 10th row in column B by using Index, like =COUNTA(INDEX('0 Data Summary'!B:B,10):'0 Data Summary'!B109)


1

OP answered his own question Sadly there is no way to-do what i want to-do, without a lot of very nasty and not 100% perfect reg hacks. If you need to access 32bit ODBC data sources the application in question has to be 32Bit. here is a link to just one forum post i found relating to this type of problem, it appears the only way i would be able ...


0

That is the function of now(), to give the current time. What you need is some simple VBA to return the current date/time when a button is pressed. You don't say what version of Excel. For 2010/2013 you will need to turn on the Developer tab in the Ribbon and ensure that you set Excel to allow running macro's. From the developer tab, inset a "Button (Form ...


2

If you are open to an addin, I would recommend the free PowerQuery addin from Microsoft. This would allow you to consolidate the two tables using a merge query that you can then crosstab. You need to start by changing your two tables into Excel tables. Then you can use PowerQuery to query each table. Then link the 2 together & add new columns for the ...


0

There are two steps you need to do to achieve your goal: 1 split the numbers into different columns,e.g. In Excel 2010: Data - text to columns - fixed width - set width to 8 then click finish. 2 2nd step is to unpivot your data, unfortunately it's not simple, but there're are a lot of solutions in the internet, like this: Is it possible to ...


0

This is not terribly hard to do with Excel worksheet functions.  Let’s assume that your data are on Sheet1 with headers in Row 1.  Create Sheet2.  Fill it in as follows: A1 → =Sheet1!A1 and drag to the right to cover all the columns (i.e., through Column E, based on your example of five columns), or simply copy the headers from Sheet1 to Sheet2. A2 → ...


0

You can try opening the file in LibreOffice or OpenOffice, and then resaving it. Both of those office suites are completely free of charge. Sometimes programs in those suites can open files that cause problems in other office tools.


0

This can also be done using COUNTIFS. You need to build a range of numbers to be the "buckets". If you just want a quick answer, you can just drag down far enough to capture all your data. If you want a more robust solution, you can add a conditional to the category to see if it is larger than the MAX of the data and #N/A it out so it does not plot. ...


0

I think this is what you want your columns to look like: Here are the formulas used: G2 is =IF(AND(B2=B1,A2=A1),"Yes","No") H2 is =IF(AND(B1=B2,A2<>A1),"Yes","No") I2 is =IF(B2<>B1,"Yes","No")



Top 50 recent answers are included