Posts by dichs

  • Count With Filter

    Hi there


    I have a challenge when using autofilter.
    In the attached file I have sales reps with sales numbers.


    In cell B2 and B3 I have succeded with formulas which will calculate when using filter in the list.


    However, in cell B4 I want to count number of records which are above value 500, but when filtering sales rep "A", then formula "Countif" still counts all records above value, i.e. 15.


    Question: What formula should I use to count only visible records above value 500, e.g. 4 records for sales rep "A"?


    Ths in advance for any help.
    Have a good day.


    BR Stoffer, Denmark

  • Create Drop Down List In Cell

    Re: Create Drop Down List In Cell


    GREAT, thx for the help, what a simple function. I had just forgotten all about it, but remember it now I see it.
    Dave, I see your point. I'm still medium advanced in VBA though, both Excel and Access - consider this a memory blunder from me. :?
    Enjoy your day.
    Dich Stoffer

  • Create Drop Down List In Cell

    Hi there


    Pls have a look at the attached example file, that I have from someone.
    It has this function you can see in the sheet "Scheme" in the cells B2:B5. Now, when moving onto to cells a data list becomes available, and obviously the list items come from the sheet "Variables".


    My problem is, I have no idea how the cells B2:B5 is actually created, although I have researched the help functions of Excel. Probably I'm using wrong search key words for the function.
    Simple challenge I'm sure, however can someone direct me to how I can create such "cell lists" if I should create them myself from scratch?


    I think it is a great function, so thx in advance for any help.


    BR Dich Stoffer, Denmark

  • Transpose Data In Access ...

    Hello Ozgrid


    Don't know if the headline above is very good, but I couldn't think of a better. I have a challenge, which is about optimizing an Access database.
    Unfortunately I can't attach a sample Access file, so I hope to have summarized the essentials below. But the essense is I have an Access database with the following tables:


    "Tbl_APO_Forecast_Xport", which holds data imported from an Excel file.
    "Tbl_APO_Versions_4_PivotTool" holds data that are appended from the table above.
    "Tbl_Hierarchy_Products" is a table holding data related to the two tables above.


    Now, the first table holds some month data, and there are separate fields for each month. These data are appended to the second table, where data from alle months are consolidated to one field, however with information about what month it is to a "month" field (pls. see one of the 12 append queries – e.g. "Qry_Append_Tbl_APO_FC_Xport_YEE_2008_01_Jan". In other words, the idea is to arrange the months vertically (so to speak) instead of horizontally - hope this is understandable. (In case you are wondering why didn’t he transpose the data in Excel before importing, then this is because there are too many records for Excel. So I’m doing like this within Access instead of having some Excel VBA code create 12 Excel files/sheets that each need be imported.)


    Now, this has worked fine, and it has been OK to create the 12 append-queries individually, but now I face a new challenge, which is weekly data, which I want to arrange in exactly the same way.


    So I’m wondering (of course); is there another and smarter way to re-arrange or transpose my data, instead of having to create 52 individual queries. I’m thinking there HAS to be a way to hardcode this in some way in Access (vba), but my experiments so far has not succeeded.


    Can anyone show me a way to get around this, using the sample month data attached?!


    Thx in advance for any help. Hope I have explained myself sufficiently.


    P.S: I don’t always need to append all months, which is another reason for wanting another way to control the append process.

  • Reverse Engineer Formula Result

    Re: Reverse Engineer Formula Result


    Thx Dave


    I don't think goal seek solves my problem.


    Forget my first question and the numbers and what I wrote about POWER function - I shall rephrase ...


    Let's say I have the value 8% in a cell, and that I know that the 8% is the growth rate over 3 years.
    In my case the growth in the individual years is different, but I want to figure out the average annual growth over the 3 years.


    Can you fix that? Thx very much for your help.


    BR Dich Stoffer, Denmark.

  • Reverse Engineer Formula Result

    Hello there


    Probably an easy question:


    If I use this formula "=POWER(1,02;3)" I raise the number 1,02 to the power of 3, and the result is 1,061208.
    Now, if I know the result 1,061208 and the value 3, which formula do I then use to find back to the value 1,02.


    Thx in advance for any reply.


    BR
    Dich Stoffer, Denmark

  • Weeknum Function Shows #Name?

    Hi there


    How come the WEEKNUM formula in the attached file does not work?
    The cell gives me "#Name?", and I have no idea why.
    I noticed one thing though, which is that WEEKNUM function is described when I look in "Help", but the function is not in my "Insert Function"-wizard, so perhaps the function is not there at all, which is very odd.
    Thx in advance for any help.


    Dich Stoffer, Denmark

  • Count Of Records With Filter

    Count With Filter


    Hi there


    I have a challenge when using autofilter.
    In the attached file I have sales reps with sales numbers.


    In cell B2 and B3 I have succeded with formulas which will calculate when using filter in the list.


    However, in cell B4 I want to count number of records which are above value 500, but when filtering sales rep "A", then formula "Countif" still counts all records above value, i.e. 15.


    Question: What formula should I use to count only visible records above value 500, e.g. 4 records for sales rep "A"?


    Ths in advance for any help.
    Have a good day.


    BR Stoffer, Denmark

  • Count Of Records With Filter

    Hi there


    I have a challenge when using autofilter.
    In the attached file I have sales reps with sales numbers.


    I found out from Ozgrid to use subtotal formule in C2 to sum only lines visible. Ozgrid, thx a lot for that input. :)


    However, in cell C3 I want to count number of records, but when filtering sales rep "A", then formula "counta" still counts all records, i.e. 27.


    Question: What formula should I use to count only visible records, e.g. 7 records for sales rep "A"?


    Ths in advance for any help.
    Have a good day.


    BR Stoffer, Denmark.

  • Extract Number Of Variable Length From Text String

    Hi there


    Pls see attached file with very simple example of my problem.
    Imagine that I have this list of thousands of customers, who are listed in column B in the format "customer no+space+customer name".
    Now I want to extract ONLY the customer no from the text string in column B and insert it as number value in column A on the same row.
    Would have been easy with "Left" command, but as the customer numbers have variable length, I have a problem.
    Does anyone know how to fix my problem.
    Many thx in advance.


    BR Stoffer, Denmark.

  • String Names In Different Subs

    Re: String Names In Different Subs


    Well, my code is more complex than my example, so therefore it is not an option to enter all arguments to the same routine.
    However royUK solved my problem.
    Thx very much for your reply.

  • String Names In Different Subs

    Hi there


    How can it be that this works ?


    Code
    Sub AA() 
    Dim ReportFileName As String
    Cells(1, 19).Select
    ReportFileName = ActiveCell.Value
    ChDir "C:\"
    Workbooks.Open Filename:=ReportFileName _
End Sub


    And this doesn't work?


    Code
    Sub BB() 
    Dim ReportFileName As String
    Cells(1, 19).Select
    ReportFileName = ActiveCell.Value
    CC
End Sub 
Sub CC()
    ChDir "C:\"
    Workbooks.Open Filename:=ReportFileName _
End Sub


    Somehow it seems that the value of the string "ReportFileName" is forgotten when running the subroutine CC. How do I make it not "forget" the file name?


    Appreciate your feedback very much - thx in advance.


    BR Stoffer

  • Run Access Macro

    Re: Run Access Macro


    Hi there

    Well, alreeady I have some code that (in Excel) converts the layout of thousands of records in an Excel file.
    These records then need to be imported to an Access database, and this is what the macro does in Access.
    So what I want to do is extend my excel code to open the Access file, and run the already existing macro in Access, thus achieving one process instead of 2 process (i.e. first converting data in Excel and thereafter manually running the macro in Access).

    BR
    Dich Stoffer

  • Run Access Macro

    Hi there


    A short question:
    Let's say that from Excel vba code I want to run an Access database called "yyy.mdb", then run a macro called "zzz" in this database, then close the database, then return to the Excel file.


    What would the vba code look like for that in Excel?


    Many thx in advance for any help.


    Dich Stoffer, Denmark :)

  • Copy Data To Powerpoint

    Hi there


    I need to copy some data to a powerpoint presentation. Pls find some simple sample data in the attached file "SampleData.xls".


    They way I need it done is like this:
    First I imagine that I have a blank powerpoint file on my C-drive, with 3 blank slides. Let's call that file "Blank.ppt" (C:\Blank.ppt) - also attached.
    The data in sheet 1 in my excel file should be copied onto slide 2 in "Blank.ppt", i.e. copied into the slide as bitmap.
    Then the chart in sheet 2 in my excel file should be copied onto slide 3 in "Blank.ppt", i.e. again copied into the slide as bitmap.
    These 2 objects on separate slides should each be sized so that they fill out the whole slide, both horizontally and vertically.


    Then the text in cell "H1" in sheet 1 should be copied into a textbox on slide 1. You decide whether a text box should already exist in "Blank.ppt", or the code can create a new text box with the text in "Updated.ppt".


    Finally, the file "Blank.ppt" should be "saved as" with another file name, let's save it as "Updated.ppt" on the C-drive. In other words, the file "Blank.ppt" remains blank on my C-drive, to be used again later. I have attached a file "Updated.ppt" which shows what we should end up with. Pls don't mind my simple sample data, my real world is much more complex. :)


    I'm reasonably experienced with VBA, and have used it for years, however I don't know how to integrate other applications, such as powerpoint as explained above.
    So can anyone design the (probably simple) VBA code that can start up powerpoint and do the job?


    It would be great if I could see some alternative code, that positions the table and chart as bitmaps onto the same slide, and arranges the them over/under each other, and sized as exemplified in "Updated.ppt" on slide 4.


    Hope this all make sense - thx in advance for any help.
    Dich Stoffer, Denmark