Tag Info

New answers tagged

0

You can use INDEX and MATCH with the rows offset by 2 or whatever number you need, e.g. for your example =INDEX(A3:A6,MATCH("Bananas",A1:A4,0)) With a named search range you can do the same with OFFSET, i.e. with Fruit as the named range =OFFSET(Fruit,MATCH("Bananas",Fruit,0)+2-1,0,1) Where +2 defines the "offset"


2

List separator set in Language & Region settings will not work if you have Decimal Separator set to same character. So if you have , set as decimal separator set there - you cannot have , as list separator and Excel will use default ;. You have to use . as decimal separator or write some macro that will save your document in desired format.


0

I created the following VB code to resize the header row when a cell within a range (B2:B1500) because date values above 12/28/2014 would cause the header to show a warning that these dates, in a timesheet, would go into Week1 of 2015: Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the ...


0

In Excel- select the Data header tab Go to Data Validation Settings Allow - List Source - Select the area of a spreadsheet that has the drop down choices


0

To keep the random value formulas from updating, you need to overwrite them with their values. To do this, copy the range and Paste Special > Values back into the same range. This will remove the formulas and leave only the values. If you want to disable manual editing (e.g., accidental deletion or keyboard input), you'll need to lock the range with the ...


2

I will explain how to put this macro into Excel as simply as I can. To add a macro, press Alt + F8. This will open the Macro window. Then type a name for a new macro and click Create. VBE will open and you can paste the below code in between Sub and End Sub. Dim WordApp As Word.Application Dim myDoc As Word.Document Application.ScreenUpdating = ...


0

You will need a formula to convert to text, remove the decimal (don't forget to cope with other number formatting if the sheet will be used in countries that swap . and ,) and left pad with the usual: =RIGHT("00000000"&REPLACE( A1, FIND(".", A1), 1, ""), 9) Note that I've not actually tested the above. Also note A1 should be whatever cell you want to ...


1

Identifying the lesser values in two columns can be also marked using 'IF' formula. An another idea. This will help you to filter out the lesser values in the column 'B'. Please follow the simple steps: Select a cell in the 'E' column > Go to Formulas tab > Insert Function > select 'IF' function > now, select a cell in the 'B' column, enter '<' symbol ...


0

Change the file extension from ".csv" to ".txt" on the file you are having problems with. Open excel by itself. Don't click on the file to open it. Click "Open", change the file types to look for from "All Excel Files" to "All Files" Find your file and click open. Since Excel doesn't auto format txt, it will then take you thru the wizard. Makes sure the ...


0

Try: Create a formula, say in column D, that =b1-c1 (essentially giving the difference between columns b and c) and copy down the column. This can be hidden if not required. Highlight columns B and C. Click on Conditional Formatting (in the Home menu of Excel) Manage Rules (bottom option) New Rule (leave the drop down as current selection as columns B & ...


1

Yes you can. It happens to be so simple it gets overlooked. All you have to do is enter the values separated with a coma. There is no special code required. NW, NE


0

Select all cells by Ctrl + A Right click on any cell and select "Format Cells" Go to "Protection" Tab and uncheck "Locked", Press OK Select the cell that you want to lock or hold down Ctrl to select multiple cells, right click on one of the selected cell and select "Format Cells" Go to "Protection" Tab and check "Locked", Press OK Go to "Review" Tab and ...


0

To recap and correct, B6 = number of questions answered correctly C6 = total questions asked So the portion of questions answered correctly is B6/C6.  For B6 = 15 and C6 = 50, this will evaluate to 0.3.  You can display this as 30% by formatting the cell as Percentage, or you can simply multiply by 100. By the way, ... so B6/C6 is equivalent to ...


2

You have two options if you don't like Excel's default assumption that you want your line chart to show "in between" dates where you have no specific data points. Option 1 - show them as blanks If you add the weekend dates into your data, but leave the values blank, your chart does this. This might be what you're looking for. Option 2 - skip the dates ...


0

Just to summarize what was in the comments section. Kyle has two cells: C6 = Total questions B6 = Questions asked Here is what he wants to find: Percentage questions not asked = (C6-B6)/B6 Percentage questions asked = 1-((C6-B6)/B6) Using the "1 -" will inverse the original result. This may seem basic to some but hopefully it helps others.


0

Merged cells are just formatting. The issue is correctly pointing to the cell that has the value. One approach would be a helper column where Excel can find what it needs (either the actual value or a pointer to it). This is a lot more practical if every row is associated with either an actual value or a "merged value". If some rows can have no value, it ...


0

Here is another way (using VBA) to know if a cell is part of a merged region. I sometimes use this function (and some others derived form this one) : Public Function IsMerged(AnyRange As Range) As Boolean Dim MergedRegion As Range IsMerged = AnyRange.MergeCells If IsMerged Then Set MergedRegion = AnyRange.MergeArea Else Set MergedRegion = ...


0

While there is an NEI (Not Enough Information) flavor to your question, if the value column P is usually a number or date and you want the value from column L is column P contains #N/A or N/A or #NA or even an actual Excel #N/A error code then perhaps something like this would do. =IFERROR(--P1, L1) Please remember that we are not mind readers and our ...


0

In U1 enter: =IF(ISNA(P1),L1,P1) and copy down. NOTE: the obvious =IF(P1="#N/A",L1,P1) will not work!


0

It's in points. 72 points = 1 inch.


0

You could use this: =IF(D19=E19,"Values are accurate",(D19-E19)/MAX(D19:E19)) This scales your differences between -100% and +100%. So, if you have 3 system stock and 1 physical, the value it will be +67% off (instead of +200% as by your previous formula). If you can live with that, this solution should work.


0

Something like - =IF(D19=E19,"accurate",IF(E19=0,magic formula,(D19-E19/E19)) But the magic formula will only work if you know, mathematically, that your scale is absolute. For instance a change from 1 to 2 is 100% and -1 to -2 is -100%. But, from 0 to 1 is only a change of 10% if the scale is 0-100. If the scale is 0-3, it's 33%, etc One magic formula ...


-1

Two possible solutions that I can see A)how about you wrap a IF statement around your existing IF, to cover for conditions like E19=0 or E19!="" (i'm not sure if != is the right excel syntax for not equal to) E19!="" means it's equal to something. You have to decide what value you want when E19 is 0, then have it covered by your conditional.. OR ...


0

I applaud your effort to generate two sequences from one formula, but I submit that this is hard to maintain.  I recommend that you establish the two sequences in two separate columns (“helper” columns), and then fuse them in column A.  For example, A1 — =IF(C1="TOTAL", X1, Y1), and drag down X1 — =IF(C1="TOTAL", 1, 0) X2 — =IF(C2="TOTAL", ...


0

I have come across this issue. It happens when you have a lot of cells to be selected, along with this it depends on your hardware because Excel 2013 consumes a lot of RAM. To solve your issue. Close other unnecessary apps this would free some RAM memory After you do the CRTL+SHIFT+END, Please wait for sometime until the excel finishes selecting the ...


1

Yes, you'd need something like this in the worksheet module for the workbook - Private Sub worksheet_change(ByVal target As Range) If Not Intersect(target, Range("Z3")) Is Nothing Then If Range("Z3") = "CSI" Then Range("B3") = Range("A3") Range("A3").ClearContents End If End If End Sub You could change the range to reference the ...


0

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Something like this works well =VLOOKUP(A1,Sheet3!$A$1:$B$6,2, false) This means =VLookup(What value to look up, in this case A1, From where? Select entire table on Sheet2, Which column number has the value you want to return, false means an exact match) So Sheet 1 is And My other ...


0

Use VLOOKUP. Sheet 1 cell B1 would be: =VLOOKUP(A1,SHEET2!A1:Bnn,2,false) Replace SHEET2 with the name of sheet 2. I've shown the data range on sheet 2 as A1:Bnn; use whatever the last row number is for nn. This looks up the value of sheet 1 cell A1 in the first column of the data range and returns the associated value from the second column of the ...


0

Something like this, just define your range and offset properly Sub test() Dim c As Range For Each c In Range("A1:A100") c.Offset(, 24) = c c.ClearContents Next End Sub If you'd rather do it another way, you can iterate through the cells - Option Explicit Sub test() Dim i As Integer Dim j As Integer i = Range("A" & Rows.Count).End(xlUp).Row ...


0

I've posted an answer on another question that may resolve this issue. One reading the commments here and elsewhere it seems that the issue may be related to running a 64-bit version of Windows. Excel 2013: "We couldn't free up space on the clipboard. Another program might be using it right now"


0

If you base the relative positioning on wherever the current selection or active cell is, you can have any row's cells from columns A & Y perform these tasks. Sub A2Y() Dim r As Long, sr As Long If Not Intersect(Selection, ActiveSheet.UsedRange) Is Nothing Then For r = 1 To Intersect(Selection, ActiveSheet.UsedRange).Rows.Count ...


0

I have been looking at your problem and I think I know what you may be looking for. If you were to type in the ranges in G5:G14, H5:H14 & I5:I14 then you could use the INDIRECT function to reference them as part of your VLOOKUP formula. However, I do not believe that is necessary and INDIRECT should be avoided if possible due it being a volatile function ...


0

When you have pasted data from an external source (e.g. web pages are horrific for this) into a worksheet and numbers, dates and/or times come in as textual representations rather than true numbers, dates and/or times usually the quickest method is to select the column and choose Data ► Text to Columns ► Fixed Width ► Finish. This forces Excel to reevaluate ...


2

Try =if(isblank(b4),"",IF(NOW()-B4>=15,"OK","Warning - Time to Renew"))


0

This is a job for Macros, though you didn't tag it. Number Formatting can not be used here as the cell is null. Put the below code into your macros, for some reason I had to put this into the code on "Sheet1" - when I tried to create a separate Module (which we would usually do) or put it in "ThisWorkbook" it didn't pick it up. Private Sub ...


0

I made my left hand column start with one as number one of the actual first row. Then I unchecked the 'view headings' box in the page layout view. That took out the left hand auto numbered column and the ABC etc out of the header. cl


1

It is hard to tell without seeing the actual data, but what I'd reccommend to check is if the format of the given cells where the IDs are stored are indentical in the two sheets.


3

The date format for the cell in question is differnt from the other date cells in column A. I suspect you are displaying Mmm-YY. Please right click the cell and correct its date format.


0

If you have a text value like 8:00 in, for example, cell J42, then put =TIMEVALUE(J42) into cell K42 and you will get an Excel date/time value of eight hours.  It will display as 0.33333333 by default (because 8 hours is ⅓ of a day), but you can format it to display as a time value.  And, depending on your needs, you may want to copy column K and paste ...


1

There is a way to do this using Data Validation, which I am assuming you used to create your lists in the first place. Not knowing how your sheet is setup, this answer can only be generic in nature. To do what you are asking uses tables, data validation and formulas to hide previously used items from the list. You will need to do the following; Set up ...


1

Excel is automatically converting that text you inputting with the macro into a date (and then applying a date format. To force excel to view it as text, prepend a single quote to the date string: ActiveCell.Value = "'" + Format(Now(), "dd-mmm-yy") Alternatively, set the format of the cell to text type first: ActiveCell.NumberFormat = "@" ...



Top 50 recent answers are included