Cell reference / /another sheet //

[Yury1979]

Hi guys,

Dropdown list created on A15 (screen 1, sheet "Price Quote")
The price should reflect in H15 (screen 1, sheet "Price Quote")
All the data, screen 2, sheet 'Settings"

Can the data from screen 2, be linked to F8 (international of DOmestic flight, screen 3, sheet "Price Quote") and display the price in H15?

Thank you.

 

[Yury1979]

If i'm not mistaken you want the number of hours put into C9 to automatically change the selection in A15?
Or is it the other way around? or neither?
Trying to understand how you want the value in H15 calculated.

Actually, I want the H15 to be dependant on C9. If I change the number of hours in C9, H15 should calculate according to the frame of hours described on A15. FOr instance, if the parking is 10 hours in C9, H15 should calculate 18 * 10 for international flights or 18 * 20 for domestic.

 

[Yury1979]

Anyone can assist, please?

 

[Sufiyan97]

Nope, it shows 0,00. I guess it's a good thing, at least it doesn't know error, lol

in the above case what happens if you put International instead of Domestic in F8?

 

[Yury1979]

in the above case what happens if you put International instead of Domestic in F8?

The amount in H15 changes, but I need it to change also when I change C9...

 

[Sufiyan97]

The amount in H15 changes, but I need it to change also when I change C9...

What you want to change inH15?

Like what will you put in C9
And what result you want when you put that amount in C9

 

[Yury1979]

What you want to change inH15?

Like what will you put in C9
And what result you want when you put that amount in C9

In C9 I want to change the hours lets say I want to put 9 hours.

In F8 I switch Domestic or International, let say I chose Domestic (=20 per hour)

In F7, for example, let's say the weight will be 12 Tons

In A15 I chose from the drop-down list the option "Parking: VARZ-400 All other turboprop and pistols jets < 10 t. - Up to 11 hours"

the result on H15 - should calculate more than 10 Tones (in F7 we have 12, which is good, falls into the range), and we have in A15 description up to 11 hours
in C9 we put 9 hours, which also falls into the hour's range, as in the description we chose the option - Up to 11 hours"

So basically H15 should take 9 hours from C9, multiplied by the value that corresponds to the dropdown list option "Parking: VARZ-400 All other turboprop and pistols jets < 10 t. - Up to 11 hours" (per each hour in domestic flight = 20)

 

[bstory84]

I believe the original solution * C9 , is what was previously recommended by @Sufiyan97

=VLOOKUP($A15,Settings!$A$385:$C$399,IF($F$8="Domestic",2,3),0))*$C$9

This would multiply the dropdown selection (Either Domestic or international) by the value in C9.
It would then be dependent on the correct dropdown selection.

However I believe the real issue is that the domestic rates are not stored as numbers in your reference. they are most likely stored as text ("35.00/hr").

You would need to either remove the " /hr" to perform calculations against them.
or
Use an additional step in the formula to remove the text and convert to number.

=VALUE(SUBSTITUTE(VLOOKUP($A15,Settings!$A$385:$C$399,IF($F$8="Domestic",2,3),0),"/hr",""))*$C$9

 

[Yury1979]

I believe the original solution * C9 , is what was previously recommended by @Sufiyan97

=VLOOKUP($A15,Settings!$A$385:$C$399,IF($F$8="Domestic",2,3),0))*$C$9

This would multiply the dropdown selection (Either Domestic or international) by the value in C9.
It would then be dependent on the correct dropdown selection.

However I believe the real issue is that the domestic rates are not stored as numbers in your reference. they are most likely stored as text ("35.00/hr").

You would need to either remove the " /hr" to perform calculations against them.
or
Use an additional step in the formula to remove the text and convert to number.

=VALUE(SUBSTITUTE(VLOOKUP($A15,Settings!$A$385:$C$399,IF($F$8="Domestic",2,3),0),"/hr",""))*$C$9

Nope, I deleted the hrs word already, now its only numbers

 

[bstory84]

Is the correct value showing for the dropdown selection when you remove the "*C9" from the formula?

 

[Yury1979]

I believe the original solution * C9 , is what was previously recommended by @Sufiyan97

=VLOOKUP($A15,Settings!$A$385:$C$399,IF($F$8="Domestic",2,3),0))*$C$9

This would multiply the dropdown selection (Either Domestic or international) by the value in C9.
It would then be dependent on the correct dropdown selection.

However I believe the real issue is that the domestic rates are not stored as numbers in your reference. they are most likely stored as text ("35.00/hr").

You would need to either remove the " /hr" to perform calculations against them.
or
Use an additional step in the formula to remove the text and convert to number.

=VALUE(SUBSTITUTE(VLOOKUP($A15,Settings!$A$385:$C$399,IF($F$8="Domestic",2,3),0),"/hr",""))*$C$9

This is the error I'm getting...