Posts by Greg.Com

Re: Match Formula Returning Incorrect Result

Perfect thanks Kris.

Re: Autofill Formula Across Incrementing Rows?

An example wkbk always helps.

Hi Guys,

I'm having a hard time getting a MATCH formula to return the correct results. Please see attached workbook.

I have a list of names and wish to look for the first 6 characters of each name in another list and return the row number that the 'MATCH' appears in. The formula I am having problems with is in column 'E' of the 'Analysis' sheet. I have entered the expected result in column 'F'.

Any help appreciated as always.

Greg.

Re: Lookup Price Per Unit Based On Quantity Sold

Thanks for that Domenic. Really helpful.

Dave, I would've used a pivot table but there are 2 many iterations of price break in the actual dataset. Thanks anyway.

Re: Return A Result From A Range Where Criteria Is Met

Quote from jamierodgers

Hi

I would lay it out different (personally).

See the attached example, using the vlookup formula

Jamie

Display More

So would i but I'm stuck with this format I'm afraid ! But thanks anyway.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Quote from Domenic

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

S3, copied down:

=INDEX(D3:Q3,MATCH(B3,IF(MOD(COLUMN(D3:Q3)-COLUMN(D3),2)=0,IF(D3:Q3>0,D3:Q3,"")))+1)

Note for example that for Row 8 if the P.O. Quantity is 150 the formula will return #N/A and if the P.O. Quantity is 250 the formula will return 1.07.

Hope this helps!

Display More

Works a treat thanks Dom. The error for a qty of 150 in row 8 is irrelevant as the user is unable to place an order for lower that the first scale quantity.

I'd like to understand what the formula is doing so I'd better raid the help library !

Thanks again.

Greg.

Re: Conditional Formats Between Dates

Have you tried removing the quotation marks (") from your formula ?

I can't seem to get my head around this one. I have a list of ranges of prices for different materials depending on the quantity bought. I want a formula that returns the correct price depending on the quantity required.

Please see attached workbook. My desired results are in column S. I can't stop thinking that a nested IF statement is the solution so I was hoping for a fresh pair of eyes on it.

Any help greatly appreciated as always.

Greg.

Re: Removing Grey Plot Area As Default

Quote from Dave Hawley

No idea then, when I create a default chart and then use it via Custom Types I get a chart with full formatting as I set.

Thanks anyway Dave.

Re: Removing Grey Plot Area As Default

Quote from Dave Hawley

Did you choose your default chart from the Custom Types tab?

Yes. User-defined in the custom types tab. It also defaults to it when using the wizard.

Re: Removing Grey Plot Area As Default

Quote from Dave Hawley

Create the chart as you want it, right click on the chart area, choose Chart Type and then click the Set as default chart button at the bottom of the Standard Type tab.

That's what I did but it still defaults the grey plot area.

Re: Removing Grey Plot Area As Default

Tried that Dave but it just sets the chart type as a default, not the formatting. I also checked the 'Default Formatting' box but this just returns the selected chart to Microsofts defaults (i.e. grey plot area) and it's these settings that I want to change !

Hi Guys,

I was just wondering if there was a way of changing the excel chart default so it doesn't always return a grey (or gray for those of you over the pond !) plot area every time I create a chart. It looks rank and wastes toner IMO.

Cheers,

Greg.

Re: Vlookup, Looping Through A Folder

No problem Ray. Glad to be of help.

Greg.

Re: Vlookup, Looping Through A Folder

Quote from Ray Drayson

Hi greg, thanks for the reply.
I think I see what you are dooing, corect me if I am wrong, you seam to be working through the one sheet with info in it, then giving the answers back to the Master, which is a farr better way than I thaught of. My way for every entry it would have to look in almost all wb`s one after the other, but as you seam to do it I only have 2 excel workbooks open at one time. its looking good now all I got to do is get the code to work.
Thankyou, and if you get it gowing before I do please let me know.

Hi Ray,

Got it working now with help from Derk ! See attached file. All you need to do now is dump all of the wbs you want to search in your C:\Temp folder, list the filenames in the Filelist sheet of Master.xls, change the range FILELIST to capture all files and run the macro.

Greg.

Re: Error Handling Advice

That makes sense and works a treat !

Thanks for your help Derk.

Greg.

Re: Error Handling Advice

It's still crashing when I run it I'm afraid. It looks like it's closing the active file and then trying to close it again ??

Re: Error Handling Advice

Quote from Derk

I have not used Clear in the past, but had hoped it would reset the error handling. Well try this attempt:
[vba]cleanup:

Windows(FILELIST.Value).Activate
ActiveWindow.Close
Windows("Master.xls").Activate
Resume Here
Here:

Next
[/vba]

Display More

Didn't like that either I'm afraid but thanks for your help . . . keep 'em coming !

I've uploaded the workbooks if that's any help.

Thanks again.

Greg.

Re: Transpose Columns To Rows Based On Titles

This worked for me but I'll think I'll be using Derk's method in the future !! :

Sub Macro1()


    Range("A1").Offset(2, 0).Activate
    Selection.Name = "START"
    Selection.Offset(42, 0).Activate
    Selection.Name = "END"
    Range("START:END").Select
    Selection.Copy
    Range("END").Select
    Selection.Offset(-43, 1).Activate
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("START:END").Select
    Selection.EntireRow.Delete
    
    Do Until ActiveCell.Value = ""
    
    Selection.Offset(1, 0).Activate
    ActiveCell.Select
    Selection.Name = "START"
    Selection.Offset(42, 0).Activate
    Selection.Name = "END"
    Range("START:END").Select
    Selection.Copy
    Range("END").Select
    Selection.Offset(-43, 1).Activate
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("START:END").Select
    Selection.EntireRow.Delete
    
    Loop


End Sub

Thanks.

Re: Transpose Columns To Rows Based On Titles

Sorry . . . got the wrong end of the stick ! A pivot table wouldn't work for this.

Re: Error Handling Advice

Nope. Still doesn't work I'm afraid Derk. I'm getting an 'Object variable or With block variable not set' run-time error if that helps.