VLOOOKUP Please help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brian.baker13@googlemail.com - 08 Nov 2007 18:59 GMT

Hi I have this formula

=-(D18-(VLOOKUP(C18,'Data Table'!$B3:$G12,5,0)))*(VLOOKUP(C18,'Data
Table'!$B3:$G12,3,0)*E18/1000)/((VLOOKUP(C18,'Data Table'!
$B3:$G12,3,0)*E18/1000)*(VLOOKUP(C18,'Data Table'!$B3:$G12,5,0)))

but when i try copying down a sheet the data table references keep
changing as I copy. How can I keep them the same?

Thanks

Brian

Reply to this Message

Pete_UK - 08 Nov 2007 19:05 GMT

You need to make them fully absolute, like this:

=-(D18-(VLOOKUP(C18,'Data Table'!$B$3:$G$12,5,0)))*(VLOOKUP(C18,'Data
Table'!$B$3:$G$12,3,0)*E18/1000)/((VLOOKUP(C18,'Data Table'! $B$3:$G
$12,3,0)*E18/1000)*(VLOOKUP(C18,'Data Table'!$B$3:$G$12,5,0)))

Or, you could define a named range (call it table) to cover 'Data
Table'!$B$3:$G$12, and then your formula would become:

=-(D18-(VLOOKUP(C18,table,5,0)))*(VLOOKUP(C18,table,3,0)*E18/1000)/
((VLOOKUP(C18,table,3,0)*E18/1000)*(VLOOKUP(C18,table,5,0)))

Then you can copy the formula down.

Hope this helps.

Pete

On Nov 8, 6:59 pm, "brian.bake...@googlemail.com"
<brian.bake...@googlemail.com> wrote:

> Hi I have this formula
>
[quoted text clipped - 8 lines]
>
> Brian

Reply to this Message

Peo Sjoblom - 08 Nov 2007 19:10 GMT

Add a dollar sign to the rows as well

change

$B3:$G12

to

$B$3:$G$12

Signature

Regards,

Peo Sjoblom

> Hi I have this formula
>
[quoted text clipped - 8 lines]
>
> Brian

Reply to this Message