Referencing Data in a different location

[dleckie]

Hi All,

I am trying to figure a way to reference data at a cell when the data is at a different location on a worksheet.

Ex. C5 = States, D5 = State XX, M5-M10 = Ma, CT, VT, NH, RI
And D5 is a link to that Cell some way.

Is this done through VBA or can it be done by a function? any help would be great.

Thanks
Dave

[Oorang]

Hi Dave,
Could you be more specific than "Reference Data":) What are you trying to do, Sum, VLookup, etc?

[dleckie]

I will try. I want to click on a cell (D5 lets say) that will go to a location in the same workbook to a specific cell where there is a list of data. This data would be on a different worksheet.

At that list of data I would copy then paste back to the originating cell (D5).

I am trying to make this worksheet mobile so I can bring it with me to different location within my company.

Does that help?

[p45cal]

I think you're looking at Data Validation. Do this on a new workbook to get the hang of it:
1. Go to the sheet whetre you want your list to be and create that list in a vertical block of cells, 1 column wide.
2. Select the whole list.
3. While the whole list is selected go to the extreme left of the formula bar (to the left of the fx symbol where there's normally the cell address of the active cell displayed) and type 'TheList' (without the quote marks) and press Enter.
4. You've just created a Named Range.
5. Go to cell D5 on the sheet where you want the data to appear and select it.
6. From the dropdown menus at the top choose Data|Validation... and in the subsequent dialogue box on the Settings tab, ensure that 'List' is in the 'Allow:' field, and in the 'Source:' field type the following:
=TheList
and click OK.

Now when you select cell D5 you should see a dropdown arrow to the right of it which when clicked allows you to select from your list on the other sheet.

[Oorang]

Hi Dave,
My apologies, but I'm still not clear on what you are asking. It sounds like maybe you might be talking about a simple VLookup using an external reference: =VLOOKUP("test",[deleteme.xls]Sheet1!A$1:B$16,2,FALSE) ?