Lookup across sheets

**Eeeee** · 07-19-2007, 01:04 PM

Guys,

Big problem for me ........hopefully small problem for you....

I have numbers displayed in a column on one worksheet and I want to display them in a row on another worksheet.

The only way I have been able to do it is to individually set up each cell which is very time consuming. I've entered a few cells and then tried dragging the sequence but it doesn't work properly. As i drag across the row of the 1st sheet ( where I want to display the info ) the cell references increment across the row from the 2nd sheet ( which contains the info I want in columns ) rather than down the column.

Does this make sense ?

Can anyone help ?

**Stuart Farr** · 07-19-2007, 01:22 PM

Have you tried Copy Paste Special Transpose?

**Eeeee** · 07-19-2007, 02:26 PM

Thanx for reply.

I tried what you suggest but it didnt do exactly what I want. Looks like I only gave you part of the story for the original problem... I forgot to mention that the info in columns is not complete ie it gets updated daily, so the column size increases by 1 value per day as time goes on. Your suggestion works as a snapshot of that moment but when I add a value tomorrow it doesn't update the row.

Any suggestion would be welcome

**BigBas** · 07-19-2007, 02:39 PM

You can use a formula/setup like this:

Assume the numbers are in column A in Sheet1, and you want them in Row 1, sheet2. This would be the formula:

=IF(COUNT(Sheet1!$A:$A)<COLUMN(),"",INDEX(Sheet1!$A:$A,COLUMN()))

Fill that across as far as necessary.. Let me know if that works.

EDIT TO ADD: Enter that formula into sheet2, cell A1, and fill across

**Eeeee** · 07-19-2007, 03:44 PM

Thanks Big Bas this almost worked

cos I don't understand what this formula is doing can you just help me with one last bit

I want to pick up the data starting at sheet 1 cell M22 ( start of column information) and place it at sheet 2 cell F25 ( start of row)

**BigBas** · 07-19-2007, 03:58 PM

Enter this formula into cell F25 on sheet 2, fill across, and let me know if it works.

=IF(COUNT(Sheet1!$M$22:$M$200)<COLUMN()-5,"",INDEX(Sheet1!$M$22:$M$200,COLUMN()-5))

**Eeeee** · 07-20-2007, 04:14 AM

BigBas........This works fine Thanks very much

I would now like to do similar with other data in same workbook which part of the formula do i change to use other data in other locations ?

**Bonnie45** · 07-20-2007, 12:26 PM

If you go to the new worksheet and create a formula referencing the cell you are trying to copy from the other worksheet. Then copy that formula down the new worksheet assuming they are all together, then you can manipulate the data from there. The beauty in that is if the information in the original worksheet changes, it will automatically update the new worksheet info.

**Eeeee** · 07-25-2007, 06:04 AM

Thanks everyone this worked fine problem resolved

Lookup across sheets

LinkBack

Thread Tools

Rate This Thread

Display

Lookup across sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions