Search results

Hi SirJB7, thanks for getting back to me on this - unfortunately it's not one of those problems where it's easy to come straight back with an answer! Unfortunately I can't share the workbook as it's sensitive information, and not sure I can mock up a sample as I've no idea how the issue arose...

Hi all, Have come into a slightly odd issue this morning. I've had an ongoing little project to run some transformations on companies' regular reported financials in order to present them in the way I want to. Today I tried to copy one of my input sheets within a workbook. On doing so I was...

Cacos, please send me an email - malcolm[dot]begg[at]gmail.com and I will send you an example file.

Yep, data validation is probably what you're after. Also check out using protect sheet (under the review tab) if you want to stop people from messing with cells you don't want them to change.

Fancy going through a load of quarterly results pdfs and sending me a nicely formatted spreadsheet with all the data I want? :p

In fact the vba to refresh can be really simple - just put something like this on the worksheet that shows your output: Private Sub Worksheet_Activate() ThisWorkbook.RefreshAll End Sub Then it will update when you open that sheet. Gets slightly more complicated if you want to change the...

Perfect! Unfortunately what that's shown me is that there are no fields I don't need.... Gonna be a fun one.

Pivot table! I tried to do something similar to this with several criteria... with an array formula working on about 20,000 rows of data it ended up taking about 2 minutes to look up. I realised that what I was trying to do could be run practically instantly by a pivot table; only thing is it...

Hi all, Does anyone have a nice trick to quickly find cells that don't have any dependents? I'm building a model which is necessarily going to require trawling through financial reports to find the right data to enter. Obviously I want this task to involve the least pain possible, so want...

Sorry, think I missed the conditions you were trying to put on. Simple version: {=INDEX($A$2:$A$14,MATCH(1,IF($B$2:$B$14=$E$1,1,0)*IF($C$2:$C$14=$E$2,1,0)*(COUNTIF($F$1:F1,$A$2:$A$14)=0),0))} Sorted alphabetically...

Can do a simpler version using index / match countif: {=INDEX($A$2:$A$14,MATCH(0,COUNTIF($F$1:F1,$A$2:$A$14),0))} OR you can work a little harder and get them in alphabetical order...

Solved it myself! Used a pivot table with "top 10" filter applied, all total / header rows removed to fit in with the rest of the sheet, then wrote a macro to refresh when the state of the conditions are changed. The actual application of this is a lot more complicated than the example version...

1 bracket missing to close the ISERROR() function. Should be: =IF(ISERROR(VLOOKUP(H1,Sheet1!$C$1:$E$100,2,FALSE))," ",VLOOKUP(H1,Sheet1!$C$1:$E$100,2,FALSE))

Hi guys, thanks for your responses. You guys have both proposed the following solution: 1. Lookup the top 10 largest values in the values column =LARGE(IF([conditions column]="yes",[values column],""),{1:10}) 2. Lookup the row name from the values =INDEX([names column],MATCH([ranked...

Hi all, I'm dealing with a large data table, and want to be able to lookup the rows with the top 10 values (in one column) that match a given condition (in a different column). So far I've done it with some intermediate steps, but am hoping that I can cut these out as it's starting to get...