Previous Topic Next Topic

Help with excel, formulas

Closed
najibk - Jun 15, 2009 at 08:55 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Jun 16, 2009 at 01:41 AM
Hello,

I am trying to connect to sheets together. I need the cells in sheet 2 to equal certain cells in sheet 1. The problem is the data on sheet is horizontal, and the data in sheet 1 is vertical. Furthermore, the data in sheet 1 is separated.

For example, in Sheet 1, you Have the company name in column 1 of a specific row, then different things like Year of inception, and size in each other column.
Now in sheet 2 in have the years 1990-2000 on the top row, and all the companys on the first columns. And I need to insert the companys size at year of inception. For example, if Company X was incepted in 1994, with a size of 45 million, then in sheet 2 i would need to put 45 million where company X and 1994 meet.

But there are hundreds of companies that I need to do this for. Is there any formula I can use?

Is it possible to transpose the date from sheet 1, but only paste it if there is date for it?

3 replies

Answer 1 / 3
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jun 15, 2009 at 10:51 AM
Hi
Use hlookup(cell address of compnayNameinsheet2(eg A1), rangetolook from companyname to company size(lock the range address),no. of rows between company name and company size in sheet 1, false)

example =Hlookup(A1,Sheet1!$A$1:$B$100,3,false
0
Answer 2 / 3
najibk
Jun 15, 2009 at 12:47 PM
Hi
Thanks for the help, but its a little more complicated than that.

Can I send you the excel sheet so I can explain it better?
Thanks
0
Answer 3 / 3
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jun 16, 2009 at 01:41 AM
Hi

you can make an If statement to check the year of inception (use hlookup to pick the year of inception) and for the Yes portion of if statement, use another hlookup to pick the company size and for the false part put an "".
If any further complications are there, Just type the complications...
0