Tag Info

New answers tagged

0

Pivot tables are the best way to go here. Create a pivot table out of your sheet. When you have it drag the email column to the rows field, then the club you are interested into the filter field. Set the filter to "1" This will give you a list of all the people that are interested in that club.


1

If you want Monday, Tuesday, and Friday (not Thursday) then try this short macro: Sub DateMaker() Dim d As Date, K As Long, fmt As String K = 1 d = CDate(Now) For i = 1 To Columns.Count fmt = Format(d, "dddd") If fmt = "Monday" Or fmt = "Tuesday" Or fmt = "Friday" Then Cells(1, K).Value = d K = K + 1 ...


0

Given the structure of the file and the fact that authors are listed Last, First - you need a way to specify which , is the delimiter. I'd do a find and replace on all "" and " to (blank space, not nothing), then you can delimit on any , that is preceded by a blank space (which can't be shown in markdown apparently). If I were you, I'd try to re-export ...


0

It seems more like each whole line is encased in double quotes1, thus: "<line>" Then the first column has no double quotes encasing it (just the ), but then the subsequent columns are doubly double quoted. Finally, there is an extraneous comma at the end. So, each line finally takes the form of (I have aded spaces to enhance clarity): " ...


0

Its easier if you are working in rows you can just double click the little black box in the corner of a2 when it is selected. For copying across columns I just navigate to Z2 from A1 (up, ctrl+right,down) select the range and copy (ctrl+shift+right, ctrl+right) Fills the range, takes about a second, seems fast enough for me.


1

1) If your used range is between columns A and Z, then you can press Shift+Ctrl+End to select cells Ax:Zx (x being your current row). If you accidentally paste something outside of this range and delete it, then you can restore that range by saving the workbook. 2) You could put all of the formulas in Column A and then select all of the formulas and fill ...


-1

From Microsoft's Control when external references (links) are updated: Break a link to a source Important When you break a link to a source, all formulas that use the source are converted to their current value. For example, the link =SUM([Budget.xls]Annual!C10:C25) would be converted to =45. Because this action cannot be undone, you may want ...


2

Using the mouse does not mean that you have to drag a formula down hundreds of pages. You can use a simple double click instead. Enter the formula into C1, then double-click the Fill Handle (the black square at the lower right corner of the cell) Before the double click: After the double click on the fill handle:


1

You can use Ctrl and the arrow keys, with or without Shift held down, to navigate around the spreadsheet quickly. Ctrl+arrow keys jumps directly to the "last" cell in that direction which has content. Holding down Shift selects cells as you move around. Depending on how your spreadsheet is laid out, you may also be able to use Ctrl+Home (jumps to the top ...


0

An easier-to-understand way to do it would be to fill a helper column by putting the formula =IF(A3=A2,B2,ABS(B2-1)) in a helper column (column B). You'd have to just put a zero in B2 (I'm assuming you have header row), paste this in B3 and then drag it down. You then use the conditional formatting formula: =$B5=1


0

I checked the Excel Developer Reference in my copy of Excel 2007 and in the documentation for the Series.Name Property it says this: Remarks You can reference using R1C1 notation, for example, "=Sheet1!R1C1". I've tested this and it works for me: I set it to a particular cell, and when I changed the text in that cell, the series title updated ...


0

So as I understand it you have a sheet "Aug" with a table in it, let's say Date is column A, Location is column B, Risk Level is column C and Status is column D. On another sheet you want a summary of this information by location. Using formulas, suppose your locations are in cells B1, C1, D1, etc. and you want to put the information from sheet "Aug" in ...


0

You cannot access the list sown in the worksheet's filter dropdown. Build a variant array of values based upon criteria instead and pass that array into the .AutoFilter Field:=1, Criteria1:=(myArray), Operator:=xlFilterValues.


0

I think there are too many variables at play here. The main factors would be: font family, font size, wrap text, merge, and which characters are used. The following might be a close hack. You could loop through the columns (and/or rows) and store the width of each in an array. Autofit the columns. Loop through the columns again and test if the current width ...


0

Much as been already suggested, you need to check if you are returning a result. As you only want to check existance, a VLOOKUP function is a bit of overkill. I would suggest a simpler MATCH function with a check to see if it is returning a row number (found) or error (not found). =IF(ISNUMBER(MATCH(D4, '[05.14_Wave 5 Priority 14 (Big+Cranberry) ...


1

Assuming the sheet wherein you stored your list of codes is called CodeSheet ={SUM(IF(IFERROR(MATCH(f3:t3,CodeSheet!$A$8:$A$22,0),0)>0,f4:t4))} This is an array formula. Enter it without the curly braces, and hit control-shift-enter. Excel will put the curly braces in for you. How it works: MATCH(f3:t3,CodeSheet!$A$8:$A$22,0) ...


0

Change the file to a .zip extension.. You will see some folders that contain ETL files. You can open those with the Microsoft Event Viewer Snap In, however they aren't very useful. Try http://connect.microsoft.com/site/sitehome.aspx?SiteID=216 .. It might be a decent tool to try. Good luck!


0

Wrap each VLookup in NOT(ISERROR()) to achieve what you're looking for: =IF(NOT(ISERROR(VLOOKUP(A1,sheetname!A:A,1,FALSE))),"some statement","another vlookup")


1

You will need to use vba for this I believe. It used to be a real pain to get out conditional formatting colours even with vba but they have since added a new function. The below will copy the colour over in cells A1 to A3. This does not remove the conditional formatting so you will either have to do that manually or add something to the macro to do it. ...


0

Fix the API references as follows: In Excel press Alt+F11 to go to the coding window Open the Tools menu and choose References Uncheck the Missing: item in the list Scroll down and add a reference to the new version of Microsoft Word.


0

You can achieve this with a macro. Put below macro in the sheet that contains the pivottable and update the slicer name and filter item name :) (you need to refer to the slicer forumla name wich can be seen in Slicer Settings) Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) On Error GoTo err_handler 'filter value might not contain ...


0

Your question is a bit ambiguous. I'll address what I think you're asking. What is stored If you enter something that Excel interprets as a date, it stores it as a date, rather than the actual character string that was input. A date is stored as a number that represents the number of days since a reference date. So the stored value is actually a day ...


0

You still have a little way to go but it is not looking that bad :) Here is some tips for you that will make it easier to write your code: Store each worksheet as a variable, this makes it easier to see which sheet you are grabbing data from: Dim wbMaster As Workbook, wbCurrent As Workbook Set wbCurrent = ActiveWorkbook Set wbMaster = ...


0

You can stop Excel from automatically inserting the first day of the month by formatting the cell as Text. This does have to be done before the data is input though.


0

You can do this with a formula. Do an index/match lookup through each sheet. If the sheet does not contain the number it is cached by the IFERROR and you try to look in the next sheet. Below formula looks through all 5 worksheets :) Paste it into cell B2 in sheet ATT and copy it down... =IFERROR( IFERROR( IFERROR( ...


0

I lied, you totally can do this in a way that creates easy to read references for your users. Anywhere in your workbook (aside from row one, where you are putting the end result), type this: =Sheet2!$G5 And autofill down to however many rows you need. Then copy all of those cells, select A1 and paste special - transpose.


-2

To switch Excel columns and rows, 1.Open the spreadsheet you need to change. 2.If needed, insert a blank worksheet. 3.Click the first cell of your data range such as A1. 4.Shift-click the last cell of the range. Your selection should highlight. 5.From the Home tab menu, select Copy. 6.At the bottom of the page, click the tab for the blank worksheet such as ...


2

If we have this in Sheet1: and run this short macro: Sub ReOrganize() Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = Sheets("Sheet1") Set sh2 = Sheets("Sheet2") Dim N As Long, M As Long, i As Long, j As Long, K As Long Dim t1 As String, t2 As String N = sh1.Cells(Rows.Count, "A").End(xlUp).Row K = 1 For i = 1 To N ...


1

Assuming your original table is in A1:D5 (with headers in row 1), in E2, array formula**: =INDEX(B2:D2,MATCH(TRUE,ABS(B2:D2-55)<=5,0)) Copy down as required. Regards **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it ...


2

If the user enters a date, they can hide the day or the month or the year by formatting. But this is only how the cell is displayed. If, however, you look in the Formula Bar; the day will be visible. If your only interest is to determine which of the two formats the user has selected, (and not the actual day-of-the-month), the use the =Cell() worksheet ...


1

Yes, that's the only thing you can do. You can't have half a row, at least if you need both halves to be separately accessible to formulas and so on. If you don't actually need two independent entries, but you want to put two things in the cell, you can press Alt+Enter during entry to add a newline into the cell contents. But that's only going to help you ...


0

Just found this and other examples of how to do this via VBA and it got me thinking and I figured out a relatively quick and painless way of doing the same thing without having to know or copy any script. Apply the desired Conditional Formatting to whatever row you want and then highlight the entire row. Next Right-Click anywhere along the border (mouse ...


1

The change to incorporate multiple columns is quite simple. Only one line of code needs to be changed: Private Sub Worksheet_Change(ByVal Target As Range) Dim A As Range, B As Range, Inte As Range, r As Range Set A = Range("I:I,K:K,P:P") Set Inte = Intersect(A, Target) If Inte Is Nothing Then Exit Sub Application.EnableEvents = False For Each r In ...


1

Quick and dirty - You can spread out your data a bit as shown below. When you insert a stacked column chart, and set the gap width of the column series to 0%, you get a chart like that shown below. Longer answer - I wrote a tutorial showing how to get a decent looking Clustered and Stacked Column Chart in Excel.


1

A function is part of a formula. So the below should work in exactly the same way. Select the column you want: Paste your formula: Then hit Ctrl + Enter. Done!


1

While the fix recommended by Raystafarian helped, the full solution included removing the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib{00020813-0000-0000-C000-000000000046}\1.8 I'm now a very happy camper.


1

As well as the observation by Clif, your current formula would in any case only be suitable for providing a single return, not the list you give. For that you would require (assuming you have Excel 2010 or later): =INDEX(ORIGINAL_ARRAY!$A:$A,AGGREGATE(15,6,ROW(ORIGINAL_ARRAY!$B$2:$B$10)/ORIGINAL_ARRAY!$B$2:$B$10,ROWS($1:1))) and copied down. As to ...


0

Got there in the end... Thanks for the nudge in a different direction Prasanna. Added an extra column to my data source and pivot tabled with that to show unique only count. Kind of like this...


0

What you are describing could be accomplished with a pair of offset() functions: B2 =offset($a$1, row()*5,0)-offset($a$1, (row()-1)*5,0) offset expects a starting cell, the number of rows to offset by and the number of columns to offset by. row() returns the current row number so =offset($a$1, row()*5,0) references A5 in the first row (row()*5 = 1*5 =5) ...


1

The answer is pretty simple. Use the in-built tool called Pivot table to do this task. To make Pivot table, Goto the Insert tab and select "Pivot table". Enter the range of your table - like shown below - this is for my example Now Click "OK". You will see a blank box. The next thing to do is to add the fields are rows and columns as you want. See ...


0

I solved this by doing a google search on the Url and using the google results page url for the hyperlink e.g. for sweettoothrewards.com I used https://www.google.ca/?gfe_rd=cr&ei=xwK5VY-LG8KC8QeGn4LoDw&gws_rd=ssl#q=+sweettoothrewards.com In opera browser with me not signed in.


0

In A1: =OFFSET(Sheet2!$G5,COLUMN()-1,0) This returns a reference to Sheet2!G5, and when dragged across to B1 returns a reference to Sheet2!G6. The same can be done in A2: =OFFSET(Sheet2!$G25,COLUMN()-1,0)


0

Open Task manager and start end application one by one til you find the one causing the issue. mine was dropbox good luck


0

@wwl I have modified this existing answer to hopefully offer you a closer match to what you're after - Your data result lends itself to the Data -> SubTotal routine. With some slight adjustments you can get a Total figure (Sum) of each identical match. Create a temporary joining column to merge the Column A & B together for easier manipulation. To ...


0

If you sort by ID then by Category, then by date you'd at least have it in a close format. Then perhaps find a way to pivot chart to list the values of dates. Otherwise you'd have to manually transpose each id/category combo. Maybe take the dates and put them into a frequency chart? It wouldn't be organized data but at least you could see ...


1

If your sheetname includes spaces, you must use single quotes: ='My Sheet'!B3


1

How do I reference an excel sheet name in an excel formula? Use the following syntax: SheetName!CellAddress Notes: The worksheet name comes before the cell address, followed by an exclamation mark !. If the worksheet name includes spaces, enclose it in single quotation marks '. Example: 'Sheet Name with spaces'!CellAddress Further reading ...


2

Without VBA In C2 enter 1, in C3 enter: =COUNTIF($B$2:B3,B3) and copy down:


1

You simply use =Sheet2!C25 In this case, I'm referencing Sheet2, cell C25 If I had named the sheet something like DataSheet, I would use =DataSheet!A1 The above would access the A1 cell of DataSheet So, if my DataSheet A1 had the value 5, and my Sheet1 A1 had the value 15 , then on my ResultsWorkSheet I could have =DataSheet!A1 + Sheet1!A1 or ...


1

This VBa does what you want. Remember to take a back up fo the file first, there is no undo function Option Explicit Sub EeekPirates() Dim startRow As Integer startRow = 2 'Ah hoy cap'ain, enter the startRow Dim entryColumn As String entryColumn = "C" 'Enter the entry column so we can place the gold... Dim ...



Top 50 recent answers are included