Vlookup Help

[Etruscan2004]

I am just came into a situation where the current report has multiple Vlookup formula's that look like this

=VLOOKUP($A7,'[Bob Service Report MTD.xls]Fee Reversals'!$A:$AA,6,FALSE)

I am trying to get to the point where I can have the name be a field location, like D53. This way I can change the name in D53 without having to go into each field.

Any help would be appreciated.

 

[SkipVought]

Hi,

Name?

Do you mean the Lookup Range?

You want to replace '[Bob Service Report MTD.xls]Fee Reversals'!$A:$AA with a range reference like D53?

I must not really understand your question.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Etruscan2004]

I am trying to get it so that in field D53 would be the persons name, such as Susan or Rick.

Then try to get the Vlookup to show something like this

=VLOOKUP($A7,'[D53 Service Report MTD.xls]Fee Reversals'!$A:$AA,6,FALSE)

That way I only have to change one field instead of 40.

 

[AnotherHiggins]

Not sure I understand the question.

Are you talking about making a change to the formula in one cell, then have it take affect in all the other cells?

If so, there are a couple of ways:

1) you can select more than one cell, type in a formula, then press [Ctrl]+[Enter]. The formula will be entered into all cells. Make sure you use relative references ([blue]=A2[/blue] or [blue]=$A2[/blue] instead of [blue]=$A$2[/blue]) so the formula in each cell will be row-specific.

2) Change the top-most formula in the column. Double click the Fill Handle to propagate the changes to all used rows (down to the first empty cell in the column to the left). See Excel's help file for Auto Fill for more help on this method.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

I posted before I saw your reply.

Do you understand what each argument in the formula is and does?

The arguments are:
=Vlookup(lookup_value, table_array, col_index_num, [range_lookup])

See Excel's help file for descriptions of each of the arguments.

Do you want the value that you are looking up to be in cell D53? That would look like

=VLOOKUP($D$53,'[Bob Service Report MTD.xls]Fee Reversals'!$A:$AA,6,FALSE)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

"Susan or Rick" would be what you supply to the FIRST argument, thru a reference.

As John suggested, check out HELP and take the time to use the posted examples.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Etruscan2004]

We have a number of documents such as Bob Service Report MTD.xls or Susan Service Report MTD.xls or Steve Service Report MTD.xls, that feed into one document. The original Vlookup looks like this

=VLOOKUP($A7,'[Bob Service Report MTD.xls]Fee Reversals'!$A:$AA,6,FALSE)

Occasionally $A7 changes from being in the Bob Service Report MTD.xls to being in the Susan Service Report MTD.xls

I would like for either the name of the person or the .xls document itself to be something that can be referenced from another cell.

Let me know if that makes more sense.

 

[SkipVought]

Check out the INDIRECT function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, NAME is something very specific in Excel.

"I am trying to get to the point where I can have the namefirst word in the workbook reference be substituted by a field locationreference, like D53"



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Also, your life would be much (much, much) simpler in the long run if you kept the data for all employees in a central location, then had reports for each employee get data from the master report.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

AMEN and mega ditto to that, John!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}