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?

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.

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

IIF is not a function in Excel, only in Access. Try IF

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.