## Hot answers tagged microsoft-excel

8

Data validation will not change formatting of the cells. You will need to use Conditional Formatting to do what you desire.
Once you have your lists in place, apply conditional formatting rules to those cells which use a formula that checks for the words RESTRICT and CASCADE.
Select the cells with the lists
Create a new conditional formatting rule
Choose ...

5

In B1 enter 1
In B2 enter:
=COUNTIF($A$1:A2,A2)
and copy down.
For example:

4

Not on a windows box. I bet there is a formula that would do it. VBA for sure:
In VB Editor click Insert Module then type:
Sub posInBnegInC(ByRef aList as range)
Dim cll as Range
For each Cll in AList
If cll.value2 > 0 then
Sheet2.Range("B65000").end(xlup).offset(1,0).value2 = cll.value2
Else
...

3

You can use this formula to convert
=SUBSTITUTE(A1,"T"," ")+0
SUBSTITUTE takes out the "T" and replaces with a space and then adding zero "co-erces" the resultant string to a date/time value that Excel will recognise
Then just format in the required format, e.g. m/d/yyyy hh:mm:ss

2

This formula finds the last number in the range A2:Z2, adjust as required:
=LOOKUP(9.99E+307,A2:Z2)
9.99E+307 is [almost] the largest number that excel can handle, when the lookup value is so large that it won't be found in the range then that results in the formula finding the last number in that range

2

Your question isn't clear, but this should get you going
Sub AvoidThePirates()
Dim startRow As Integer
startRow = 2 ' EDIT THIS ME HEARTIES
Dim columnToLookUp As String
columnToLookUp = "C" ' Arrrgh ye scurvy sea dog
Dim columnResults As String
columnResults = "D" ' Show me ya gold
'Touch below this and I'll feed ya to the sharks
Dim isUpper As ...

2

I'm not sure I understand exactly the effect you are after, but if you want to be able to copy (or drag to extend) a formula while keeping some reference the same, you can prefix either the column or row (or both) identifier with a $ sign.
For example, if you have in cell A1 =B3 and copy this to cell A2, it becomes =B4 because the reference is updated to ...

2

Check out the Worksheet event Worksheet_Calculate or the Workbook event Workbook_SheetCalculate
It's easy for those to start firing infinite loops, though, so be careful

1

This should be easy with vlookup, but I don't understand your WS structure.
Let's say Sheet1 starts at A1 and Sheet2 has the first product at A1
In B1 of Sheet2 you need
=Vlookup($A1,Sheet1!$A$1:$M$400,COLUMN(B1),0)
Then drag this down and over (to column M and down to the bottom of selections) in Sheet2 to fill in the matches.

1

I figured it out, it's super simple.
All you need to do is to change the number value of whatever you're using as your "Cell link," which you can easily do in VBA.

1

There is not a native way to do a horizontal version of the filter in Excel.
You could possibly achieve this functionality by means of pivot tables, but that would be highly dependent on how your data is arranged and what you need to see. Your only other options would be a custom coded VBA routine, or to select each column and hide or unhide them as ...

1

After adding the new column, it appears that First Name is in B1. In A2, you could add any one of the following formulas,
=COUNTIFS(B:B, B2, C:C, C2)
That will produce the number of occurrences for the first and last names (e.g. 1 for single, 2 or more for duplicates).
If you want a more complicated formula with simpler results then perhaps,
...

1

You need to provide an extra level of processing so each row in F3:F11/G3:G11/H3:H11 is added. This can be done with a SUMPRODUCT wrapper on a SUMIF.
The formula in F13 is,
=SUMPRODUCT(SUMIF($B$3:$B$14,F$3:F$11,$C$3:$C$14))
Fill right as necessary.

1

I tried a few things and ended up with a working solution, but it requires a helper column in your first sheet. I'm going to assume that your example data is in columns A:C of Sheet1, and they have to go into columns A:C in Sheet2.
The helper column goes into column D of Sheet1, and merely counts the occurrences of the "No answer" values. So from D2 and ...

1

Here is some code to get you started (based on the cell placement in your image).
Sub SeriesFill()
Dim RowCount As Long
Range("C4:C1000").Cells.Clear
RowCount = Range("C2").Cells.Value + 4
Range("C4").Cells.Value = "0"
Range("C4").AutoFill Destination:=Range("C4:C" & RowCount), Type:=xlFillSeries
End Sub
This was tested in Excel ...

1

Earliest due date (column C, starting at C2) can be calculated with =MIN(A2:B2), then copy this formula down across the column.
You can then get the matching headers into column D, starting at D2, with =IF(A2<=B2,$A$1,$B$1). Then, again, copy this formula down the entire column.
In case both overhaul dates are identical, this solution will always choose ...

1

One style of multi-row headings:
Create a table with a single line header
Select any of the heading cells,
Hit f2 and then move the cursor to the end of the title/text
Press and hold Alt, then hit Enter for all additional lines you wish, release Alt.
Add any text you wish.

1

Okay, fixed it all up. :)
And answering my own question lol - Just for googles sake.
Private Sub Workbook_Open()
Const fromFile = "NetworkDriver\Folder\Workbook.xlsx"
Application.DisplayAlerts = False
Sheets("Sheet8").Delete
Sheets("Sheet9").Delete
Application.DisplayAlerts = True
Dim srcBook As Workbook
Dim wkb As Workbook
Set wkb = ActiveWorkbook
' ...

1

Could it be as simple as
D2 = 1
D3 = =IF(A2<>A3, 1, IF(C2=C3, D2, D2+1))
(and drag down)?

1

With input in cell A1, in B1 enter:
=DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,9999))
If you format B1 as "mm/dd/yyyy hh:mm:ss"
you should see:
NOTE:
The format with the T is only valid after the fact.
This is, once you have valid date/time in a cell, you can then apply a CustomFormat of:
mm/dd/yyyy"T"hh:mm:ss

1

Arjan already mentioned this twice so but I'm trying to post this as an actual answer instead of a comment. He said the post is locked but I'm so fresh to Stack Exchange that I don't get why it's locked but I can still post this as an answer.
Anyway, Microsoft has an exact solution for this. I would only modify it slightly by wrapping it in IFERROR to return ...

1

You're basically asking how to get the n-th filtered value from some column into the n-th row of another column. Microsoft explains that in Excel: Finding the nth Value That Meets a Condition. This uses "array formulas" to perform calculations on items in an array (a list).
If your array is in A2:A8, and you want to only show values >= 0, then the ...

1

It turned out to be an update distributed through Microsoft Update. I removed all updates relating directly to Excel and Office Service Pack 2, and functionality was then restored.

1

This does it
Sub EeekPriates()
Dim row As Integer
row = 2
Dim cols(2) As String
cols(0) = "A"
cols(1) = "B"
cols(2) = "C"
Dim i As Integer
For i = 0 To UBound(cols)
Dim col As String
col = cols(i)
Do While (Worksheets("Sheet1").Range(col & row).Value <> "")
Dim ws1value As String
Dim ws2value As String
ws1value = ...

1

Assume your data are in D and E:
In column A, all dates of the month.
In column B, all dates of the month converted to numbers
(It's something like days since 1 Jan 1970 as a default.)
In Column F, all dates in Column D converted to numbers.
Then cells in in columns G:AM (or so), this formula:
If (F1>B$1,0,E1)
For each column, B1 goes one higher, so it's ...

1

Create the top half of the brace using the "freeform" autoshape (not the "scribble" that looks to be a freeform). You will make a zig-zag with 3 straight lines.
Right click the image and select "edit shape."
Left click to select each of the middle two points; right click and select "smooth point." Do this one point at a time.
Left click each end point; ...

1

No addon making required!
Store it in a special workbook called Personal.xlsb
Steps:
This advanced tip shows you how to make macros available across all your workbooks, by saving them to a special workbook that Excel loads each time it is opened.
http://youtu.be/xWL78KcDVGU
If you found this video helpful, kindly edit this answer to add transcribed ...

Only top voted, non community-wiki answers of a minimum length are eligible