Lookup - is this possible?

[Guest]

I have a spreadsheet with 2 tabs and I'm trying to write a lookup formula on
Tab 2 based on data contained in Tab 1.

The first tab looks like this:
A2 = Project A
B2 = Unit Sales
C2 = 2005 data (say 10)
D2 = 2006 data (say 20)
A3 = Project A
B3 = Closings
C3 = 2005 data
D3 = 2006 data
etc. for the rest of the financial information for Project A, then
A30 = Project B
B30 = Unit Sales
C30 = 2005 data (say 20)
D30 = 2006 data
etc. for the rest of Project B and all subsequent projects

Tab 2 is a basic P&L for one Project structured as follows:

B4 = Project Name (say Project B in this example)
D11 = 2005 E11 = 2006
B11 = Unit Sales should say 20**
B13 = Closings

**I'm trying to write a lookup formula to get the result of 20 unit sales
for Project B (obviously will change based on which Project I define in B4)
in 2005 based on the data in Tab 1. I'm not sure how to write a formula to
first lookup the Project Name displayed in cell B4 and then lookup the
financial piece of data in B11 and display the result in D11. I'd then need
to do the same thing for B11/E11, etc.

My intent is to add a drop down box on the P&L tab so a user could select a
particular project and the formulas will automatically update with the
appropriate data for that project from Tab 1.

Hope this makes sense. As always, any help is greatly appreciated!!

HJ

 

[Guest]

If each set of data is going to be the same (i.e. on Tab 1, first row of
Project A data contains Unit Sales, second row contains Closings, first row
of Project B data contains Unit Sales, second row contains Closings, etc.)
then you can use basic index formulas in Tab2 like:

D11 = "=INDEX(Tab1!1:65536,MATCH(Sheet2!B4,Tab1!A:A,0),3)"
E11 = "=INDEX(Tab1!1:65536,MATCH(Sheet2!B4,Tab1!A:A,0),4)"

D13 = "=INDEX(Tab1!1:65536,MATCH(Sheet2!B4,Tab1!A:A,0)+1,3)"

But you are probably not going to make it this easy, right?

 

[Guest]

If the data is not going to be in the same "layout" for each project, then
we'll need to use with SUMIF's or Array formulas.

For the latter, enter the following formulas in the cells specified, but hit
Ctrl+Shift+Enter to enter the formulas to make them array formulas:
D11 = "=SUM((Tab1!A1:A500=B4)*(Tab1!B1:B500=B11)*Tab1!C1:C500)"
E11 = "=SUM((Tab1!A1:A500=B4)*(Tab1!B1:B500=B11)*Tab1!D1500)"

D13 = "=SUM((Tab1!A1:A500=B4)*(Tab1!B1:B500=B13)*Tab1!C1:C500)"

Let me know if this works and you need an explanation of what is actually
going on in the formulas.

 

[Guest]

Or you could do the following (regular formulas):

D11 = "=SUMPRODUCT((Tab1!A1:A500=B4)*(Tab1!B1:B500=B11)*Tab1!C1:C500)"
E11 = "=SUMPRODUCT((Tab1!A1:A500=B4)*(Tab1!B1:B500=B11)*Tab1!D1500)"

D13 = "=SUMPRODUCT((Tab1!A1:A500=B4)*(Tab1!B1:B500=B13)*Tab1!C1:C500)"

 

[Guest]

See:
http://www.microsoft.com/office/previous/xp/columns/column10.asp
and
http://www.j-walk.com/ss/excel/tips/tip74.htm

 

[Guest]

Hi Nick!

Thanks for the feedback. I should have time this morning to work through
your suggestions and I'll let you know how it goes!! I'm sure it will work
great. Thanks again.

 

[Guest]

Hi Nick,

I was able to get the index formula to work but you are correct in that the
layout wil not always be consistent. I can't seem to get the sumproduct
formula to work though, I keep getting a #Value error. I thought I
understood what the formulas were doing but apparently not. I also had a
typo in my original post. The cell where I need to add the formula is D12
(not D11, that has the year header in it). I adjusted your formulas for this
mistake but no luck so far. Any suggestions?

 

[Guest]

Hi again,

Disregard my previous post. I found another typo in my formula. It works
like a charm!! Thanks again for your help.