cell referenceing

**demuro1** · 09-22-2008, 04:42 PM

ok so in a nut shell I want to pull data out of worksheet 1 which may be located in another workbook. I want the data displayed in worksheet 2. The problem is that I want the user to enter data on worksheet 2 and then have worksheet 2 populate from worksheet 1 based on what the user entered. I'm currently having the spreadsheet look at value in a cell and then concatenate it with another cell value, it won't recognize the data in the new cell as a cell reference though. below is an example. if anyone can hep me out I'd appreciate it.

example

value in cell A1 is (4)
value in cell A2 is (C)

value in cell C4 is (apple)

value in cell A3 is (=A2&A1)
cell A3 shows (C4)

value in cell A4 is (=A3)
expected view result of cell A4 is (apple)
However
cell A4 shows (C4)

Does what I'm trying to do make sense?
can anyone help me out
Thanks again

**NBVC** · 09-22-2008, 04:45 PM

I think you need:

=Indirect(A2&A1)

**rwgrietveld** · 09-22-2008, 04:47 PM

Use A4=INDIRECT(A3) instead

**demuro1** · 09-22-2008, 04:59 PM

I'll try it thanks so much

edit:

Worked perfectly. Thanks so much. did exactly what I wanted it to do.

can you guys recommend a good tutorial for programming in excel? I'm pretty good when it comes to to excel but I'd always love to be able to do more

**demuro1** · 09-22-2008, 07:13 PM

ok now the next problem, I don't think this works if I try and pull the data from another workbook. So if the information is in workbook1 and I'm trying to draw the info into workbook2 are there potentially any issues there? I can't seem to get this to work.

Thanks

**NBVC** · 09-23-2008, 07:51 AM

Indirect() function does not work, unfortunately, with closed workbooks...

Your alternative might be to download and install the free Morefunc.xll addin http://xcell05.free.fr/morefunc/english/ and use the Indirect.Ext() function instead.

**demuro1** · 09-25-2008, 11:20 PM

is there another solution. I'm trying to create this worksheet and distribute it to several people.

we all have 4 massive excel workbooks with our individual data mixed in on each of the 4. What I'm trying to do is allow each user to open a fifth, much smaller workbook. When they open the fifth workbook they will enter their name in a given cell. in the 4 massive workbook our data is spread across several worksheets but identified with our names, the fifth workbook would filter our personal data out of each workbook and consolidate it on the fifth workbook.

is this something that's possible to do without having to open each workbook, and if so how would I do it. Do I need VB to accomplish this?

thanks again

**sglife** · 09-26-2008, 01:03 AM

Originally Posted by demuro1

is there another solution. I'm trying to create this worksheet and distribute it to several people.

we all have 4 massive excel workbooks with our individual data mixed in on each of the 4. What I'm trying to do is allow each user to open a fifth, much smaller workbook. When they open the fifth workbook they will enter their name in a given cell. in the 4 massive workbook our data is spread across several worksheets but identified with our names, the fifth workbook would filter our personal data out of each workbook and consolidate it on the fifth workbook.

is this something that's possible to do without having to open each workbook, and if so how would I do it. Do I need VB to accomplish this?

thanks again

It depends on your data structure
I think both formula and VBA can help you.
But the size of the file may be smaller with VBA

**demuro1** · 09-26-2008, 04:10 AM

Well what's the formula method, I don't know VB but will learn as much as I need to if required. I'd hate to have to go that route though if I didn't have to.