Search results

Will conditional formatting alone do?:

try instead: Sub Lookupbyheader() Application.ScreenUpdating = False Dim x As Long, y As Long, empid As Long, LastRow As Long, LastCol As Long, SourceRow As Long, SourceColmNo As Long Dim ws As Worksheet, wsed As Worksheet, red As Worksheet Dim strFile As String Dim TableArray As Range, hRange...

Not sure what you mean by that, so a guess in the attached. In C5 and the same formula as a named lambda in cell C8.

I can't find any merged cells in that attachment. I doubt it; what exactly are you hoping to show? Perhaps a file closer to your actual file?

Change a value in the range O5:O35 (or click button) to update all. You have Kermanshah twice and 4 regions not referred to.

What version of Excel do you have?

Formula at cell B11 of the attached.

Having got myself in front of a computer I see now that I forgot (a) that EOMONTH needs 2 arguments and (b) that EOMONTH won't work as MONTH does with arrays. So, half tested: =IF(EOMONTH(B5,0)<>EOMONTH(B4,0),SUMPRODUCT((MONTH($B$4:$B$33)=MONTH(B4))*(YEAR($B$4:$B$33)=YEAR(B4))*($C$4:$C$33)),"-")

Still can't test but you replaced ALL (4) instances of MONTH in the formula?

I can't test this at the moment but maybe try changing all instances of MONTH in pecoflyer's formula in msg#6 to EOMONTH ?

=FILTER(Sheet1!G2:H7,Sheet1!J2:J7="Yes","None")

Power Query solution at cell F1 in attached. Differences: Cell G5 has removed both matching Dede.Hickmans G6 has more results than yours.

This is an awful solution but I think it works. In the attached: Sheet1: You can delete everything below row 6 in this sheet. Formula in C3, copy down one cell to C4 Formula in cell B4 Copy B4:C4 down as far as you need to. These formulae all use a reference to cell A2 of Sheet2, so on to...

It looks like you're using Excel 2007 ! Is that correct? If you were using a more recent version it would be easy with WORKDAY.INTL which allows you to specify exactly what weekend days are. Am I right that your weekend is only Sunday? Excel 2007 only allows WORKDAY which assumes your weekends...

The 3 bits you mentioned in your first message have already been addressed; those things all pertained to what happens within a cell. To address the new things, the code below: 1. skips blank cells 2.1. removes all bullets wherever they are in the cell (including those not at the beginnng of a...

In this case, if you change type before filling down, all is OK!

…continued from above. Apart from putting the CFs in an appropriate order to ensure you get the CF appearance you want, if that ends up being difficult you can also change the formula to make CF rules 'exclusive'. Here the CFs can be in any order:

Yes, conditional formatting is very confusing indeed. When I had a look at this I thought it wasn't working as it should and Stop if True seemed a bit random! So looking into it a bit deeper I found where I was going wrong. The first thing I noted was that the first of the conditional formats...

…or call this instead: Sub blah(myRng) For Each cll In myRng cll.value = Evaluate("TEXTJOIN(CHAR(10),TRUE,""•"" & SORT(TEXTSPLIT(" & cll.Address(external:=True) & ",,CHAR(10),TRUE)))") 'or, to treat lines which have only space characters as totally blank lines: 'cll.value =...

Guessing a lot. You can use a formula to get a result in another cell; this one looks at cell S2:=TEXTJOIN(CHAR(10),TRUE,"•"&SORT(TEXTSPLIT(S2,,CHAR(10),TRUE))) Convert that to a spilling formula that works on a column of cells, S2:S5...

Very easy in Excel, in D2: =XLOOKUP(A2-1,A1:A$2,D1:D$2,1,,-1)*C2 copied down, but haven't been able to do it in Power Query yet!

Both the absence of VSTACK and you wanting to assign missing values makes it difficult for me to put together a formula for this. I think I can put together a user defined function using VBA which would mean that the workbook would have to be a .xlsm file and macro-enabled but the formula you'd...

In cell D7 of your latest workbook you have the yellow highlighted desired result of EBU36001, yet in cell A13 you already have EBU36001. I'm going to assume this is a typo and that A13 should be something else, perhaps beginning ERE36… . So if I overwrite A13 with anything else, just to take it...

See formula in cell E3, copied down. It may have to be array-entered (use Ctrl+Shift+Enter instead of plain Enter to commit the formula to the sheet depending on your version of Excel. Cell E2 is a bit different.

A lot it would seem: and @vletm 's is a value you can do arithmetic with while @ExcelGoogler 's is text (although Excel's implicit data type conversion would probably come in if needed)! I've assumed UK style dates. Edit post posting: Looking more closely at @uday 's formula I'm guessing...