Search results

Hello, I'm trying to sort the rows on all pivot tables on a sheet and hide blanks I've tried: Sub SortPivots_HideBlanks() Dim pt As PivotTable Dim pf As PivotField For Each pt In ActiveSheet.PivotTables For Each pf In pt.RowFields With pf .AutoSort...

Exactly what I pointed out above (...although I'm surprised it matters outside a given Board)

Here's what I've gone with went with in the end, based on a suggestion over at MrExcel: 1. Create a dynamic list of dates (unfortunately my users wont have versions of Excel with dynamic array formulae, so it will be a Table listing valid dates) 2. Define a name for the column of valid dates...

@Peter Bartholomew , Thanks for your reply. To clarify, I already have data validation in Column B that permits selection from one of several lists depending on what is selected in A (i.e. list-based data validation, e.g. user can select in A1 Fruit or Veg: If Fruit is selected, then the list...

Hi, Does anybody know how to change data validation type depending on the contents of a cell? Whereas I can use lists and indirect for a list in B1 depending on the contents of A1, how could I make the data validation for B1 force a date, if depending on A1? (Moderator - I'm guessing it'll...

Well I didn't manage to figure out how to do this in excel so used this font instead: https://www.idautomation.com/barcode-fonts/code-39/ ...which achieves what I need by including the ASCII number under the bar code

Hi I've got a table with around 600 bar codes in column [Barcode] (formatted using "Code 128" font) corresponding to a part number in column [Part Number]. I'd like to be able to print them all with the part number under the barcode and so need to find a way to concatenate with a line return /...

Thank you!

Hi Guys, How can I count the number of times a cell reference appears within formulae in a range?

Thanks Hui. Why are the blank row (8) and column (D) needed, and included in the sum ranges?

Hi, I'd greatly appreciate help finding a formula that will calculate the figures in blue (cells C3:F6) in my mockup below. Column I contains sales thresholds at which different bonuses are paid in 4 tiers labelled in Column H. If I sell 100 units in Quarter 1 (Cell B3), that would mean all of...

Hi, Thanks very much for taking the time to reply. Table 1 is just where the master data for each pupil is held (name, demographics, etc.). It can't be the data entry place for the assessment scores because there are multiple tests, at multiple times of year, etc., so the simplified example...

Hi all, I'd greatly appreciate some thoughts on ways to approach solving the following problem. I've got a set of spreadsheets that record pupil assessments, and I've run across a problem when I add a new pupil half way through the school year, related to having the names sorted...

Unfortunately there's a whole bunch of sensitive data about pupils, etc. so posting a sample of the actual thing is not a good idea here. I'll try to recreate something non sensitive

I see. Well, then I guess the problem is that because successive queries refer to data drawn from earlier queries, it sometimes hangs/crashes because the earlier queries haven't finished drawing. Is there any way to test/trap if a query has finished drawing before refreshing the next query?

So indeed the first query might not have necessarily finished running before the second query starts? Then I'm back to the same problem?

Sure, but I would expect the first query circle to have finished spinning by the time the second starts spinning, wouldn't you?" About the code you added: With con.OLEDBConnection <--What does this do? .BackgroundQuery = False <-- is this necessary if Background refresh is already unticked...

Thanks, that did it!

That's what I had originally. Same problem. If I change something in say cell C6, the macro runs... I can't work out why

Hi, I've got bunch of code that I want to run when a specific cell is changed so the Sub starts: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Target.Worksheet.Range("D6")) Is Nothing Then 'run rest of code... Can anybody think why this wouldn't...

Are you sure about that? I have background refresh, unchecked/unticked for all my queries, yet if I I click on data > show queries then run the code below, I see the spinning wheel against the first query hasn't completed before it starts the next query, even if I put a message box in...

I'm trying to avoid getting stuck at the blue spinning circle. If I manually refresh the queries one by one. Everything works fine. If I refresh them using vba one by one everything seems to be fine too. The problems happens when I try to refresh specific queries together using vba. The...

Hi I've got several power queries which update fine by themselves but cause excel to get stuck in a loop if I try to refresh multiple queries using VBA. I tried to get it to refresh sequentially like this: Public Sub RefreshPowerQuery() dim cn As WorkbookConnection For Each cn In...

Thanks (it was indeed a dumb question).

Please can somebody tell me the PQ / M equivalent of the vba space underscore ( _) to indicate that a statement is continuing on the next line? ... or am I missing a setting somewhere that lets me wrap line in Power Query?