Copy data from other sheets by typing in unique identifier from them?

**Smeghead** · 03-12-2013, 05:56 PM

Hello all,

first off I am not particularly experienced with Excel (2007), so forgive me if this is a newbie question.

Excel_Help.jpg

If you see the attachment, that is the basic setup I have. Sheet 1 is having its data referenced in another program. Sheets 2/3/4/etc. all have the data which needs to be first copied into sheet 1 before it can be referenced.

On sheets 2/3/4/etc, there is one cell of data for each row which is basically a unique identifier, which I can use to easily Ctrl+F that code, and then just copy that row onto sheet 1.

What I want to know is if there is a way for me to be able to type the unique identifier directly in sheet 1, and have the data automatically fill out for the rest of the cells on that row, no matter which sheet it's pulling the data from? Or perhaps if I type the identifier(s) in sheet 1, and then create some kind of button which will pull the data only when pressed (to save from having to pull it all in real-time). Or is this all too much of a hassle for Excel?

Thanks!

**bentleybob** · 03-12-2013, 06:10 PM

??? Why not upload a small sample file of what you're looking for. It's hard to know if all your "cells" in the jpg are supposed to be formulas, addresses, or actual data. My guess, though, is that you want to be able to identify a worksheet in a cell in Sheet 1 and use it to pull data from one sheet to another, but it's hard to figure from a picture. Maybe someone else can decipher this.

**Smeghead** · 03-12-2013, 06:32 PM

Sorry it's such a mess. I'll try and upload a better sample tomorrow, though right now it's late and I'm about to go to sleep.

Sheet 1 is essentially completely blank until I need to use it.

Sheets 2/3/4 all contain just data. Whether it's text, numbers, or a combination, just think of it all as ordinary text with no formulas or anything special whatsoever. The unique identifier basically helps me quickly ctrl+F and find a particular row of data which I require. However, just like everything else in the workbook, it's just pure text/numbers or whatever. It's completely unique for each row, and for each sheet, so there is no chance of duplicates.

What I want to be able to do is simply type in the unique identifier into sheet 1, and have the rest of the data for that row fill in automatically. So say the unique identifier for row 2 of sheet 2 is "02". I want to be able to just type "02" into a particular cell of sheet 1, and then have all the data that corresponds to that row to fill into sheet 1.

I hope that makes sense.

**bentleybob** · 03-12-2013, 06:35 PM

That's much clearer and, if I understand what you're looking for, you can do it using INDIRECT. If you post something (once you're rested), someone can help you out if you need it.

**Smeghead** · 03-12-2013, 07:32 PM

Wanted to get this sorted ASAP, so here's an example book.

example.xls

**bentleybob** · 03-12-2013, 08:23 PM

To keep the formula to a "reasonable" size, since you're using Excel 2007, I converted your file to an xlsx. If you need it to be in the old xls format, the formula gets rather long due to error trapping. I also assumed you wanted both the weight and the price as values so you can use them in calculations. If not, you can pull them over as text by copying the formula from an adjacent column, but you lose formatting (like the pounds sign).