Posts by smuzoen

    Re: RankIF - what formula do i need


    Just add in an "AND" condition


    =IF(AND($G6="Long",$F6>0),(SUMPRODUCT(($G$6:$G$25="Long")*($F6<$F$6:$F$25))+1),"") - copy down
    =IF(AND($G6="Short",$F6>0),(SUMPRODUCT(($G$6:$G$25="Short")*($F6<$F$6:$F$25))+1),"") - copy down

    Re: adding a percentage in a progress bar without losing visuality


    Try the attached workbook - the bar will change with a change in percentage. Depending on your Excel version with the gradient fill at 100% it will never fill the cell - in earlier versions (2007 and below I think) you need to add in a second conditional format rule to make the entire cell fill when at 100%. I don't use percentages but rather numbers - just format the number as a percentage

    Re: Get Elements by ID Keeps varying.


    If the page has been coded correctly then strict HTML/XHTML would dictate there is only one element with that id - so John_w suggestion should get you the id you require because no other element on the page "should" have the same "id". You can only use an id once in HTML or XHTML - you can use the same class as many times as you want but id is a different story. That being said though it will only cause a failure in validation and problems with associated javascript not necessarily prevent the page from resolving correctly.

    Re: RankIF - what formula do i need


    I don't think it is because there is nothing to make numbers that are equal have a different ranking - there would need to be some other factor to decide the ranking. Perhaps someone else has an idea/solution but at this point in time I cannot see a way to differentiate between two numbers that are equal.

    Re: RankIF - what formula do i need


    Problem is that how do you decide which should be ranked higher/lower when the number is the same? First occurrence gets the higher ranking? Basically 10 is the same as 10 obviously, which of the 10's should be ranked higher?

    Re: VBA: Rename Worksheet based on cell value in another Workbook


    Try


    Reading your code I assume that you want the sheet called DATA copied from the Target file to Master dB.xlsm then rename the sheet you copied called DATA to the value in C6 in the Target File from the sheet called inputs general.
    Try as good practice to reference your sheets - there would be a conflict here that you have an open workbook (Targetfile) as the active file so when you try to rename the sheet DATA it would try to rename the workbook you opened. It makes it easier to read and maintain the code.
    I also changed the file to open to allow you to select both xls and xlsx files as well (and xlsm) - you can change that back if you want to only select xls files

    Re: How to Disable / Enable specific columns based on the input from a cell


    With respect to adding the hours you could use Sumproduct


    =SUMPRODUCT(--($B$2:$B$16=$B19)*($C$2:$C$16))+(SUMPRODUCT(--($B$2:$B$16=$B19)*($D$2:$D$16))/60)


    Have a look at the attached workbook


    Try and place a link for a sample workbook - you could use Dropbox or Mediafire - I am not sure about the first post part of your question - with a sample workbook it would be easier to see how your data is set out


    PS: The sum of hours and minutes is in decimal e.g. 28.75 means 28 and 3/4 hours = 28hours 45 mins

    Re: Delete First Row &amp; Column in multiple CSV


    You could try this method - this code will allow you to browse to the folder with CSV files - it will then iterate through all the csv files and remove row 1 and column 1. BEFORE running this code BACKUP all the files - this code will alter the csv files so make sure you have a BACKUP. I cannot emphasise this enough.


    See attached workbook.
    Option 2 - You could open the CSV for binary access and then manipulate the resultant array - this is more complicated - sample code:


    However option 1 I think would be easier

    Re: Trying to modify this 'text to rows' code to only use semicolons as delimiters


    Can you supply a sample workbook with no sensitive data - what you are asking sounds very straight forward but I am not sure how your data is set out. As a really simple example


    See attached workbook

    Re: Filter based on Input


    Without a sample workbook with dummy data, all sensitive data removed it is very difficult to help you - if you select "Go Advanced" when you post you can attach a sample workbook.

    Re: Copy page margins from one workbook to another


    You could set up a template sheet in Master.xlsm with no data set up how you want - then copy this to Numbers.xlsm, delete sheet1 in Numbers.xlsm and rename the copied sheet to sheet1 - this could be done via VBA - the Page Object has an enormous amount of properties (try recording a macro and you will see what I mean) - I think this would be the easiest way. There are some macros/add ins around that may be looking for what you want. What specifically do you want to replicate wrt page margins?

    Re: vba code to populate combobox from another workbooks (excel 2007)


    What I assume you have is a combobox on a sheet in Main.xlsm - you want to populate it with the data from name.xlsx Range B2:B6 - and then also another combobox with data from city.xlsx. I am just going to show you how to do the first part - populate a combobox on Main.xlsm with data from name.xlsx. I assume you want this done when Main.xlsm is opened as you have your sample code in the workbook open event. This code will open name.xlsx and get the data from the range, populate the combobox and then close name.xlsx. You will need to follow the example to do the same with the other combobox.


    Attached is a sample workbook - code is in "This Workbook".


    It is faster to use the List property rather than iterating through the array and adding each item one at a time with additem property however the data range is pretty small anyway