Lookup Field

[Legacy 436357]

Hello,

Is it possible to have a field on a worksheet that can serve as a lookup for an ID that I can pull the related data from another sheet? If so can that data be protected when the data is completed like in an invoice situation?

I appreciate any advice or help,
XJ

 

[Legacy 436357]

bump

 

[Gerald Higgins]

Hi there, sorry I've not had time to look at this today but will look again this evening (UK time).

 

[Legacy 436357]

Thank you I appreciate any help you can offer. Have a great day!

 

[Gerald Higgins]

OK so if you have an AFR number, and want to populate lots of other fields based on that AFR number, then that's your basic LOOKUP function, or possibly an INDEX/MATCH situation.

You'll need to have some kind of master table of data that contains all your AFR numbers, and also all your other fields of data.

Here's a simplified version of what it could look like.

Column A....Col B.....Col C
AFR...........Field 1...Field 2
AFR001......F1.1......F2.1
AFR002......F1.2......F2.2

So your column headers are in row 1, and your data is in rows 2 and downwards.

Let's say your target AFR is in cell A1.

And let's say your master data table is in the range B1:D100.

The basic VLOOKUP formula would be

=VLOOKUP(A1,$B$1:$D$100,2,false)

This should return the value of Field 1 for the relevant AFR.

For Field 2, replace the 2 in the above formula with a 3.

Obviously your actual cell references will be different, and you may need to make the formula refer to different sheets, but that can be done.
So adapt the above formulas to suit the exact details of your situation.