numerical value generates linked filei into perscribed cells

[gocolonel77]

What I am trying to do is this

Cell B1 can have a numerical value of 1-6

When a certain number is entered I want excel to have the ability to fill in certain cells with lnked files.

For instance

B1 = 6


Cell D1 will have this in the formula line ='[CELINA Numbers P1012.xls]DOC'!B5

Another example

B1 = 2

Then cell D1 would have this in the formula line.


='[PIQUA Numbers P1012.xls]DOC'!$B$5


Cell B1 will always be the key to tell the othere cells what to do.

Thanks in advance

[K m]

Use the VLOOKUP function

Make a Table,
- Lets assume it is in cells F1:G6
- In cells F1:F6, enter values 1,2,3,4,5,6
- In cells G1:G6 enter the appropriate values. For example, cell G2= '[PIQUA Numbers P1012.xls]DOC'!$B$5 and cell G6 = '[CELINA Numbers P1012.xls]DOC'!B5
You would need to add the other values for cells G1, G2, G3, G4 and G5

Enter this formula in cell D1 =VLOOKUP(B1,F1:G6,2)
Now whenever B1 changes the results change

[gocolonel77]

Thanks K m

I seem to be getting an error message #n/a

I put this into D1

All necessary cells are formatted to text

=VLOOKUP(B1,AK4:AK8,2)

AK4:AK8 is my store numbers 1,2,3,4,6

Not sure if it matters but I am working with Excel 2000

Any thoughts

Thanks

[K m]

In my answer =VLOOKUP(B1,F1:G6,2), my table has two columns and 6 rows. In your formula, VLOOKUP(B1,AK4:AK8,2) has one column and 6 rows.
The formula is looking for two columns =VLOOKUP(B1,F1:G6,2)

Change your formula to VLOOKUP(B1,AK4:AL8,2). In column AL put the lookup values, ie '[PIQUA Numbers P1012.xls]DOC'!$B$5, etc.

[gocolonel77]

Can't seem to get it worked out K m.

I attached a file so you can see whats going on.

Thanks for you help with this

[K m]

You have cell B1 formatted as text but cells aK4:AK8 are numeric.

If you format B1 as numeric, it will work or you could leave B1 as text and use this formula: =VLOOKUP(VALUE(B1),AK4:AL8,2)
=VALUE(B1) forces B1 to be numeric regardless of how it was formatted.

You could also enter this formula to eliminate erroneous data: =VLOOKUP(value(B1),AK4:AL8,2,FALSE). This formula would give you an error if B1 did not match values in cells AK4:AK8

[gocolonel77]

Km I went with the latter and it worked as it should

You have been a big help. Once I am done this will save me a bunch of time

Thank You very much