Search results

Afternoon all, In our organisation, one department has a system that require Excel to be in manual calculation mode. Which is a pain when they send you a file and and it knocks it off on all your own files. I'm looking for a simple way to identify this, maybe being able to change the colour of...

hello all, I was wondering if it was possible to lookup a date value in one column, then sum the values in the next column for variable number of rows, eg. Data DATE PRICE 01/4/15 10 02/04/15 11 03/04/15 11 04/04/15 10 ... Then i have cells with date and number of...

Afternoon, I need some help with what I thought was a simple refresh. I'm trying to create a button that will update the data in a hidden sheet. I use this same code in a different file and it works fine, but this new file is using a data table. ActiveWorkbook.Worksheets("2015...

Hi, I've got a file that prepare a report pack and prints, various reports. Someone has asked to have a pack excluding a few reports, so I thought I would add a checkbox next to the report list. Just been testing this and it doesn't like it. My VBA knowledge is light, but thought this was...

Hi, I've just completed a rather large product/pricing model and realised that unless I have something that allows the actual data (formula) to be pasted as values, I will have do a lot of manual copy/pasting everything I open a file. This is way beyond my VBA skills (even been generous to...

Hi all, I have a large list of products, each with a product total line, e.g. Product1 Product1 Product1 Product1 Total Product2 Product2 Product2 Product2 Total I'd like a button to be able to filter to only see the total lines. It need to ignore "0 Total" as i have a few blanks. Any...

Hello all, I'm trying to link to an external file, but using the data in a cell to reference the sheet name: So this formula works, as it is referencing the file/sheet - the Product Sales.xls file has 24 sheets, one for each product. =VLOOKUP($C$4,'[Product Sales.xls]Product...

Hello all, Bet most of us have been caught by jumping from a file with calculations turned off to another file expecting calculations to be on, printing some work and going into a meeting with a pile of nonsense! Is there anyway, without added something to each and every excel file, that Excel...

Hi all, I was wondering if anyone could give advice or point me in the right direction of some good articles on spreadsheet performance. I.e. Some general rules to manage file size, speed, security. Maybe some guideline on choice of functions, I know some are more CPU heavy than others...

Hi all, run into a brick wall with doing some counts. I'm looking to create a count based on a few columns. So been using countifs, all good. But now as well as one value from two different columns i want to look for a range of values in another columns...

Hi, I've a set of values that creates a line graph, most values are around the 90% mark, but there are a few zeros. Rather the the line graph dropping from 90% to 0%, I'd rather not show the line to zero. Tried everything (well everything i thought of), the only way it work is when i...

Hi, I'm trying to create a price/quality matrix, started off thinking it was easy, but hit a brick wall! If you imagine this dataset, a list of competitors, a price which would set the Y axis and Quality score that would be the X axis. E.g. Comp2 would be lower on the price and quality...

Hi, In Cell A1 have a ProductName, Cell B1 I link to another file to pull in the version number. I'd like to make the ProductName a hyperlink to the file referenced in cell B1. e.g. A1 = ProductA A2 = "V2" from file ='C:\document\[ProductA - V2.xlsm]Version'!$E$3 I'd like to be able to...

One for you Luke, from old forum that I could find on here. This worked a treat. How's this look? Sub PrintBooks() Dim c As Range Dim myPath As String Dim newBook As Workbook Application.ScreenUpdating = False For Each c In Range("A1:A23") myPath = c.Formula 'Remove first bracket...

Hi, I've created a macro to sort by two columns. This first column i'd just like to order by the first character which is a number, then by the second column. Any ideas would be great. Thanks ActiveWorkbook.Worksheets("FleetChanges").Sort.SortFields.Add Key:=Range( _ "F8:F35")...

Hi, Hope you can help. Looking to create a simple script to select columns E:AF and then delete any columns that do not have "Yes" in row 7 of that column. e.g. Column G, cell G7=Yes, so don't delete it. H7=0 therefore delete it. Thanks in advance

Hi, I have a cell that links to another file showing a a version number, in cell A1, as below: ='C:Foldersub folder[File1.xlsm]Prices'!$A$1 I also have 23 more of these linking to different files. I want to write a bit of VB to open the File1.xlsm and print the Prices sheet, plus a...

Hi all, Hopefully a simple one to resolve. I have a hidden sheet, SearchData12, and I am using the following to update the date. ActiveWorkbook.Worksheets("SearchData12").Range("C11").QueryTable.Refresh BackgroundQuery:=False I use it in exactly the same way in another file and if...

Hey, I was wonder if it is possible to have a macro retrieve data from a sql database using some parameters in a workbook, basic example. Cell A1 has customerID, if I enter 123 and run a macro can it select customer 123 from a database to show name is cell B1 Thanks Paul

Hi, I'm looking to count the number of bookings that revenue value falls between a set range. Tried sumifs and sumproduct as think it may need an array, but going round in circles. More info on the attached. https://dl.dropboxusercontent.com/u/9071274/Revenue%20between%20values.xlsx...

Hi all, I'm looking to count values and select min price in the range on a specific date. The count bit is easy, but getting a little stuck trying to find the min value (exc 0). Example here https://dl.dropboxusercontent.com/u/9071274/MinVal.xlsx Thanks Paul

Hi I'm trying to cum the quantity of product I have between specific ranges. Been trying with Sumifs, but now thinking this may not be the best solution. Wanted to double check before I start trying something else. I've explained on the attached. Thanks in advance for you help...

Hello one and all, Been looking pop a simple message on opening a file. Checks if the data is up to date, if note alerts me to update. Tried this in thisworkbook object, but no joy? Private Sub Worksheet_Open() If ActiveWorkbook.Worksheets("FleetChanges").Range("Q4") > 7 Then MsgBox...

Hi, Hope someone can help here. I have a bar graph that has 5 bars, say Product1 - 5. The data is order best to worst performing and depending on the data selection Product 1 may appear top or bottom, therefore moves on the chart. I want each product (bar) to be a specific colour, i.e...

Hi, I want to display a persons username from active directory in cell A1. Seen a few ways (some quite complicated) but can't seem to get it to work. Any thoughts? thanks in advance Paul