Posts by FINgers

    Hi all,


    The forum wouldn’t let me edit post so have clarified the data set as to what I want to do.


    ive searched but can’t find a solution to this. I gave a big dataset that I want to pull the nth cell value from in a column and the use that value to populate a set of 6 formulas in a column, which then repeat down the sheet.

    for example here is the data set:

    __________A_________

    Row 1 | Val 1

    Row 2 | Other val

    Row 3 | Other val

    Row 4 | Other val

    Row 5 | Val 2

    Row 6 | Other val

    Row 7 | Other val

    Row 8. | Other val

    Row 9 | val 3


    And here is the other sheet with what I need to do:


    ____________A________________B__________

    Row 1 | Data1 | = Sheet1Val 1 & A1

    Row 2 | Data2. | = Sheet1Val 1 & A2

    Row 3 | Data3. | = Sheet1Val 1 & A3

    Row 4 | Data1 | = Sheet1Val 2 & A4

    Row 5 | Data2 | = Sheet1Val 2 & A5

    Row 6 | Data3. | = Sheet1Val 2 & A6

    Row 7 | Data1. | = Sheet1Val 3 & A7

    Row 8 | Data2. | = Sheet1Val 3 & A8

    Row 9 | Data3. | = Sheet1Val 3 & A9


    in the real sheet I have a lot of data so I need to be able to drag the formula down the table column and for the references to the cells to increment correctly.

    I have already tried variations on


    Code
    1. =OFFSET($B$5,(ROW(A1)*n)-1,0)

    but can’t get it to increment correctly:

    thanks all for your time!

    Hi all,

    ive searched but can’t find a solution to this. I gave a big dataset that I want to pull the nth cell value from in a column and the use that value to populate a set of 6 formulas in a column, which then repeat down the sheet.


    for example here is the data set:

    __________A_________
    Row 1 | Val 1

    Row 2 | Other val

    Row 3 | Other val

    Row 4 | Other val

    Row 5 | Val 2

    Row 6 | Other val

    Row 7 | Other val

    Row 8. | Other val

    Row 9 | val 3


    And here is the other sheet with what I need to do:


    ____________A________________B__________
    Row 1 | Data1 | = Sheet1Val 1 & A1

    Row 2 | Data2. | = Sheet1Val 2 & A2 Row 3 | Data3. | = Sheet1Val 3 & A3


    in the real sheet I have a lot of data so I need to be able to drag the formula down the table column and for the references to the cells to increment correctly.


    I have already tried variations on


    Code
    1. =OFFSET($B$5,(ROW(A1)*n)-1,0)


    but can’t get it to increment correctly:


    thanks all for your time!

    Hi there, thanks for the reply,


    That's nearly there, but I also need, at row 40 you will see the variable in column I changes, so I need col j to start at 1 again, but on row56, col B reverts to the first data name and that has appeared before, so at that point I need it to put the corresponding previous value in column J..


    In other words, Column B value has appeared previously, in which case the number in J should match the number from when B appeared previously.


    If the value in column B has not appeared in the list previously, then it should add +1 to the highest value that has appeared against the corresponding column I.


    I have added a column of numbers in column L to show the numbers I need the formula to create.

    I have a big set of data that will grow over time.


    I need a formula that that will do the following:


    I need to add an incremental set of numbers starting at 1 and adding 1 every time column B changes set.


    On top of this I need to restart the numbering every time column I changes.


    But crucially, if either column B or column I matches a previous value I need to add the same number again. And if it is a new number in the sequence then add that...


    Difficult to explain in words but I have attached a sample work book that hopefully makes it clear - the number in column J is what I need to replace with a formula.


    Thanks

    Re: Create a list of filtered data based on week number and payment detail


    If there was a way that I could create a new "database"set of columns that is dynamic, preferably on a new sheet that is autopopulating with the 4 columns as you have described that may work - I could then pivot table the data in the summary, but my excel knowledge doesnt stretch to that!

    Re: Create a list of filtered data based on week number and payment detail


    Thanks for the reply. I have thought about that but I will lose funtionality in other areas of the spreadsheet. Currently if I have another payment I can just insert a row and drag the functions down. There are a number of named ranges within the sheet that I would struggle to maintain.

    I have a weekly payments spreadsheet that distributes monthly payments into a sheet showing where they fall in the month, allowing for a weekly cost forecast, by week number. NB week 1 is April 1


    I want to create a summary page that shows which payments are due.


    I have been trying to do this with a pivot table but each column will only pick weeks from a specific month.


    This is not very easy to explain in text but I have attached an example sheet which shows what I am tryng to achieve.


    In short, I want a page that looks at a user specified date, a function then relates that to a week number, and the sheet auto-populates to show which payments and their values are due that week. I do not want to show all payment names, and just populate relevant values as this will make it hard to read.


    Hope that this is clear and that someone can help me!

    Re: Extract Data According To Criteria


    Hi Sabarius,

    The first bit works a treat thanks!

    I've started looking at the second section of code also to do the task code but but I suspect that looking at what you have written so far - I have a feeling that it may not quite work yet - it will be complicated by the fact that on any particular day there may be more than one task codes.
    I wrote the initial code to sum up all the hours on a particular day, so there is only one date record per day in the array, if that makes sense. I think that this time round the Task Code will be the driver for the records. But also, by virtue of the way that the code works at present, the records must be in date order - this is not always the case with the task codes.
    I will keep thinking and post up something when I have made a good stab at it.

    Thanks again for all your help.

    Re: Extract Data According To Criteria


    Thanks again!

    I've modified the code slightly to this as it was putting the dates etc in the wrong place, and added Ubound function to count the array.

    Code
    1. For iArr = 1 To (UBound(HoursArray))
    2. Selection.Value = HoursArray(iArr, 1)
    3. Selection.Offset(0, 1).Value = HoursArray(iArr, 2)
    4. ActiveCell.Offset(1, 0).Select
    5. Next iArr



    One thing I don't know how to do is stop it from cycling right through the full 500 lines of the array when it dumps back onto the worksheet - normally I would think that only about 14 - 21 records would be extracted but I put 500 to ensure that there is enough flexibility. Is there a way to redim the array with a variable?

    Re: Extract Data According To Criteria


    Cheers Sabarius,

    I've modified your suggestion slightly as it was creating a seperate record in the array for each line, so by putting the iarr count outside the loop I think that it is totalling the hours for the same array entry before moving on to next line.

    But now I am having problems with the dumping of the info back to a new sheet. I keep getting an Application Defined or Object defined error - I have tried various ways to get the data into the worksheet but nothing seems to work!

    Please see below for the latest incarnation of the code...

    Thanks again!

    Re: Extract Data According To Criteria


    Hi there,

    Sorry it has taken me so long to come back online - I've been a bit busy!

    Thanks PCI for the suggestion - I don't think that advanced Filter will do it though - although I could be wrong. As suggested, I've added here the results as I need to extract them. There are infact 2 extracts required - the first being very simply a record of hours against a date and the second a little more complicated, as a record of hours against a date and a job number.

    Thanks in advance for any help...

    Files

    • Dataexample.xls

      (27.14 kB, downloaded 79 times, last: )

    Hi there people,

    I am trying to write some code that will extract data from an excel sheet and sumarise the hours for a particular date.

    The excel data is not very tidy however - and the code needs to make allowance for this. I have attached an extract of the data. In short, I want to move through the data - and if the record meets the criteria that it is between 2 chosen dates and it is a time record not a session, it will sum all the hours for a given day, which it then records (I thought probably to an array). It then moves to the next date and does the same. When the last date within the chosen range is reached and the data populated to the array, the array then needs to be dumped to a new worksheet.

    Essencially, I want to summarise each days hours for a particular date range within a timesheet record.
    I have attached the code that I have written here but to be honest I'm no expert so am having problems with the array and it's not really working very well!
    Oh, the variables DateRangeStart and DateRangeEnd come from a form that I've made that works... so that's a start!

    Thanks for your help in advance and sorry if this seems a little long winded.

    Here's what I have done so far...

    Files

    • Dataexample.xls

      (28.67 kB, downloaded 88 times, last: )

    Hi there,

    I have 3 comboboxes, called dayInputBox, MonthInputBox, and YearInputBox on a Form. They contain a list of numbers: 1-31, 1-12, and 2010 -2020 respectively, all populated from a worksheet in excel.
    Within my code I also have a variable called DateRangeStart. When I press the search button on the form I want the 3 integers to be taken and converted to a date format dd/mm/yyyy and the DateRangeStart variable populated with this date. This should all happen in VBA.

    I am having problems with the conversion of the integers to a date within vba and would welcome any suggestions that you have.

    Thanks very much!

    Hi People,


    I hope someone can help - I have a problem creating a circular reference with dynamic ranges. I have searched already but can't find the specifc problem.


    I have a spreadsheet where I use vba code to insert extra columns. Within the worksheet there are sum calculations at the end of each row. When I add a new column within the table I would like the sum calculation to expand to account for this extra column.


    Now, I thought that dynamic ranges would be the way to go but the only way I can see to do it creates a circular reference as the CountA function trys to count the cell with the sum function in it.


    Any suggestions?


    Thanks in advance.

    Hey people,


    I;m looking for what I think is a simple solution. I need to look up a maximum value in a table depending on a condition. I have attached the spreadsheet.


    Basically, I need to find the maximum value for "Sum" column on sheet "Column Data" depending on the Coltype value on Column Summary. This should be returned to Sheet "Column summary"


    For example, the formula should look in Cell A4 of column summary, see that Coltype is B, then look in column C of sheet column data and find the maximum value in column F:F of all the Coltype (column C:C) values that are of type B.


    DOes that make sense?? I assume that it is a simple formula but can't get it to work..


    Cheers!


    FIN

    Re: Autodating Access Records


    Hey carlmack,


    Thanks for the reply.


    Yes I was thinking that VBA was the most likely answer, although I have not a clue how to do it. I've done some coding in excel but never in Access.


    In the long run for this project I want to actually write a full VB application (still very limited knowledge in VB but learning), so I am trying to limit the amount of VBA coding requirements in the Db at the moment, but if some is required to make it work in the short term then so be it.


    Any ideas on how to code it??


    Cheers,


    FIN