Results 1 to 9 of 9

Thread: dynamic tables

  1. #1

    Unhappy dynamic tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    =COUNTIFS(MVC!G:G,"Y",E:E,[@[Empl Name]])+
    COUNTIFS(php!G:G,"Y",E:E,[@[Empl Name]])+
    COUNTIFS(Awasc!G:G,"Y",E:E,[@[Empl Name]])+
    COUNTIFS('ruby'!G:G,"Y",E:E,[@[Empl Name]])

    Above mentioned formulea is not useful for me as Table names - MVC,php,Awasc,ruby - and 'Empl Name' needs to be picked dynamically.More such tables will be added and ideally i want this formulea in a loop.Like the following

    Code:
    For x = 1 To courses 
    
        ActiveCell.FormulaR1C1 = "=COUNTIFS(INDIRECT(cname&""!G:G""),""Y"",INDIRECT(cname&""!F:F""),name)" 
        y = y + ActiveCell.Value
    Code:
    
    
    however mentioned code is throwing a type mismatch error . cname, name are strings.in 'cname' i store course names like MVC,php,Awasc,ruby and in 'name' i store people names in coloumn [Empl Name] . y is integer and assuming the formulea will return an integer,this should work . But unfortunately its not working

    Can you please help me.

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,828
    Articles
    0
    Excel Version
    O365
    Does this work?

    Code:
    ActiveCell.Formula = "=COUNTIFS('" & cname & "'!G:G,""Y"",'" & cname & "'!F:F," & name & ")"

  3. #3
    Yupee . Working ..thank you so much.This is small part of a bigger problem..Will come back to you in case i face any more difficulty.Thx once again...
    Last edited by nikhil83; 2015-02-07 at 12:30 PM.

  4. #4
    Now here is the bigger problem . The formulea you gave is working fine,but giving wrong value . to Test it ,i split it into 2 halves

    Code:
    
    ActiveCell.Formula = "=COUNTIFS('" & cname & "'!G:G,""Y"")"
    ActiveCell.Formula = "=COUNTIFS('" & cname & "'!F:F," & name & " )"
    First formulea is returning 9 which is correct.
    Second formulea is returning 0 which is wrong . In string variable called 'name' i stored "James".and it appears in '" & cname & "'!F:F ,1 time so formulea should be returning 1 rite ?

    Please help.

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,828
    Articles
    0
    Excel Version
    O365
    Any chance that you could post the workbook? Need to see the data to figure it.

  6. #6
    Quote Originally Posted by Bob Phillips View Post
    Any chance that you could post the workbook? Need to see the data to figure it.
    I have attached the file . There are 2 tables in First tab named employee . I am working on table named 'Table 7' . I have commented my code as far as i can.Please feel free to make any change to the code as you please . But if you can stick to the logic i followed that would be great . Since i am in a learning mode , if you can make modifications in the code i wrote,it will help me improve.

    Code is called on click event of button 'get emp data'

    Following is my logic

    1.Pick first employee from the list (Table7).
    2.Pick first course from the list .(Table named 'Index')
    3.check if employee name figures in course nominee list (A table with same name of the course will be created each time an entry is made in 'Index' .I haven't included that macro in this sheet)
    4.if name figures,add 1 else 0
    5.Select next course and do 3,4 for all courses created
    6.Write total courses attended against employee name in employee list(In Table7)
    7.select next employee and follow the sequence from step 2 till all employees in the list is checked.
    Attached Files Attached Files
    Last edited by nikhil83; 2015-02-08 at 06:32 AM.

  7. #7
    Quote Originally Posted by nikhil83 View Post
    I have attached the file . There are 2 tables in First tab named employee . I am working on table named 'Table 7' . I have commented my code as far as i can.Please feel free to make any change to the code as you please . But if you can stick to the logic i followed that would be great . Since i am in a learning mode , if you can make modifications in the code i wrote,it will help me improve.

    Code is called on click event of button 'get emp data'

    Following is my logic

    1.Pick first employee from the list (Table7).
    2.Pick first course from the list .(Table named 'Index')
    3.check if employee name figures in course nominee list (A table with same name of the course will be created each time an entry is made in 'Index' .I haven't included that macro in this sheet)
    4.if name figures,add 1 else 0
    5.Select next course and do 3,4 for all courses created
    6.Write total courses attended against employee name in employee list(In Table7)
    7.select next employee and follow the sequence from step 2 till all employees in the list is checked.
    Can you please reply to this . I have hit a dead end

  8. #8
    please help ....

  9. #9

    Thumbs up

    Quote Originally Posted by Bob Phillips View Post
    Any chance that you could post the workbook? Need to see the data to figure it.
    I replied with workbook . can you please check at the earliest . i am in a tight corner

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •