Forum

It looks like you've already got the basic code - just amend for example FileToOpen in your first routine to use a specific path and file name rather ...

Try adding: ChDrive "H:" ChDir "H:PROJECT-OPSNSW WarehouseNSWTA Inventory Listing" before the line to select the file.

You could use something like COUNTIF instead of Find: Sub Draw() Dim i As Integer, Number As Integer Dim DataRange As Range Set DataRange =...

You need to set a reference (Tools - references in the VB Editor menu) to the Microsoft Outlook object library.

I sympathise as I'm in exactly the same situation at work!

So I guess you don't have functions like FILTER or UNIQUE available?

For which version(s) of Excel?

You could use something like this: Sub ReplaceHyphens() ' ' ReplaceHyphens Macro ' Used to replace week ranges with specific week numbers ' ' Di...

I think you'd be better off with VBA for something like that.

Then it appears all you need to do is remove the other columns from your 2 queries and append one to the other. Where are you stuck?

INDEX would also be more efficient than your current volatile INDIRECT and ADDRESS formulas. 😉

It's not really clear to me what output you want, but your description of getting it all in one column sounds like you just want an append query to me...

Do you mean that in BM15 on the Contract Change Sites tab (not the Revenue tab), you want the sum of cells AM4:AQ4 from the Revenue 2018-19 tab? ...

You could use something like this I think: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformCo...

Using helper columns: In b2: =IF(A2>=0,IF(OR(A3<0,A3=""),COUNTIF(A$2:A2,">=0")-SUM(B$1:B1),""),"") and copy down. In C2: =IF(A2<0...