VLOOKUP Circular Reference

[kayceekat77]

Hello-<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am trying to create a price list that can be used on a timesheet and invoice sheet that resides on additional tabs in my workbook.<o></o>
<o></o>
So far I have created a table that includes the list of services in the first column and the price for each service per hour in the second column.<o></o>
<o></o>
On my timesheet I have created a VLOOKUP that automatically pulls the price based on the service selected from a drop down list:<o></o>
<o></o>
For Example:<o></o>
<o></o>
QTY Service Drop Down List $Price<o></o>
<o></o>
The problem I am having now, is that I would like to enter the quantity of hours and have that multiply by the price for an extended price total, however since the price is actually a formula due to the VLOOKUP, it is creating a circular reference instead of adjusting the extended price... Is there perhaps another way to do this that I am not thinking of? I thought there might be a formula that would automatically convert formulas to values without doing it manually with paste special.<o></o>
<o></o>
Thanks so much in advance for any help!<o></o>
<o></o>
KayCee<o></o>

 

[roentgen]

Try this formula :

=IF(ISBLANK(cell where hours entered),VLOOKUP(your look up),VLOOKUP(your look up again)*cell where hours entered)

e.g.

=IF(ISBLANK(H13),VLOOKUP(D13,range,2,FALSE),VLOOKUP(D13,range,2,FALSE)*H13)

If no hours are entered, then the base price will be visible. If a duration is entered, then the value updates.

Hope this helps?