Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    18

    Unanswered: Can't figure out how to get a cell to auto populate

    I have a workbook with many tabs. Each Tab represents a Fiscal Year.

    I have a distribution tab that has all data for estimated growth and distribution.

    What I am trying to do is on tab FY09 for example have it look through the worksheet for Distribution and populate the cells based on Orign, Destination and Fiscal Year.

    For an example:
    Tab FY09
    Origin Hub: NOMA
    DESTINATION: EDNJ
    Cell C76 on tab FY09 shold say 9134
    from the tab called Distribution Info Row 2194 Column F


    Can someone look at this sheet and give me hand?
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. One approach is to use Dynamic named ranges, in this case names. Note, I changed the name of the worksheet to DistroInfo (so no spaces in names, easier to setup). I added a column to that worksheet in column A. And used this formula starting in A2:

    =C2&D2&E2

    Named Range: DataD

    =OFFSET(DistroInfo!$A$1,0,0,COUNTA(DistroInfo!$A:$ A),COUNTA(DistroInfo!$1:$1))

    Named range: DataD_LU

    =OFFSET(DistroInfo!$A$1,0,0,COUNTA(DistroInfo!$A:$ A),1)

    Then on FY09 worksheet, in cell C71 I added worksheet name (you can use a formula to determine that name dynamically).

    Then in cell C76, put this formula.

    =INDEX(DataD,MATCH(C$72&$C$71&$A76,DataD_LU,FALSE) ,7)

    Now, this can be automated even more, but I don't know your restrictions, etc. This might get you started.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jun 2007
    Posts
    18
    Thank you that is what I am looking for. Is there a way to put

    =IIF(INDEX(DataD,MATCH(C$72&$C$71&$A76,DataD_LU,FA LSE)=0,0,(INDEX(DataD,MATCH(C$72&$C$71&$A73,DataD_ LU,FALSE),7))))

    I have tried Null and 0 and teh result is either #N/A or #NAME?

    Also, if the cells equal themselves Noma/FY09/Noma have it = 0 as well.

    I was up late reading up on other ways like this posted below but it seems to be hanging up somewhere as well and I am completely lost know.

    I am not sure if this is correct or not.

    Sub populatedata()
    Application.ScreenUpdating = False
    sheetname0 = ActiveSheet.Name
    a = 2

    Do Until Sheets(sheetname0).Cells(a, 1) = 0
    sheetname = Sheets(sheetname0).Cells(a, 3)
    originhub = Sheets(sheetname0).Cells(a, 2)
    destinationhub = Sheets(sheetname0).Cells(a, 4)
    estimatedvalue = Sheets(sheetname0).Cells(a, 6)

    Sheets(sheetname).Activate

    For i = 3 To 31
    If Cells(72, i) = originhub Then
    For j = 73 To 129
    If Cells(j, 1) = destinationhub Then
    If originhub = destinationhub Then
    Cells(j, i) = 0
    Else
    Cells(j, i) = estimatedvalue
    End If
    End If
    Next
    End If
    Next

    a = a + 1

    Loop

    Sheets(sheetname0).Activate


    End Sub
    Last edited by ajames420; 10-11-07 at 09:47.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by ajames420
    Thank you that is what I am looking for. Is there a way to put

    =IIF(INDEX(DataD,MATCH(C$72&$C$71&$A76,DataD_LU,FA LSE)=0,0,(INDEX(DataD,MATCH(C$72&$C$71&$A73,DataD_ LU,FALSE),7))))

    I have tried Null and 0 and teh result is either #N/A or #NAME?
    IIF is not a function in Excel, only in Access. Try IF
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Jun 2007
    Posts
    18
    No matter what I try I can't get it to work. It is either saying I need more parenthesis and I add them and then it tells me I am missing +, -, " and more.

    I just can't get it to either so a 0 value or just be blank.

    And I didn't see anything from you about the other question:

    Also, if the cells equal themselves Noma/FY09/Noma have it = 0 as well.

Posting Permissions

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