| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference Calculations
This question is long winded so forgive me.
I need to develop a function that takes an identifer matches that id with an id on a subsequent worksheet and returns a value for that id which is then used in a math function to calculate forecast. i.e. Worksheet 1 ID Item Budget Forecasted Amount 3 Oranges $300 ? 1 Apples $32 ? 3 Oranges $212 ? 5 Pears $198 ? Worksheet 2 ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 So basically the function i need is that if the client on the first line item has $300 to spend on oranges and oranges cost $0.50 each based off Worksheet 2 then how many oranges can the client expect? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference Calculations
Simplified, all of one worksheet
ID Item Budget Forecasted 3 Oranges $300 600 1 Apples $32 46 3 Oranges $212 424 5 Pears $198 220 =C5/VLOOKUP(A5,$A$8:$C$13,3,FALSE) ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 "DH487" wrote: This question is long winded so forgive me. I need to develop a function that takes an identifer matches that id with an id on a subsequent worksheet and returns a value for that id which is then used in a math function to calculate forecast. i.e. Worksheet 1 ID Item Budget Forecasted Amount 3 Oranges $300 ? 1 Apples $32 ? 3 Oranges $212 ? 5 Pears $198 ? Worksheet 2 ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 So basically the function i need is that if the client on the first line item has $300 to spend on oranges and oranges cost $0.50 each based off Worksheet 2 then how many oranges can the client expect? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference Calculations
One note: VLOOKUP will only work if the ID is sorted
"David" wrote: Simplified, all of one worksheet ID Item Budget Forecasted 3 Oranges $300 600 1 Apples $32 46 3 Oranges $212 424 5 Pears $198 220 =C5/VLOOKUP(A5,$A$8:$C$13,3,FALSE) ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 "DH487" wrote: This question is long winded so forgive me. I need to develop a function that takes an identifer matches that id with an id on a subsequent worksheet and returns a value for that id which is then used in a math function to calculate forecast. i.e. Worksheet 1 ID Item Budget Forecasted Amount 3 Oranges $300 ? 1 Apples $32 ? 3 Oranges $212 ? 5 Pears $198 ? Worksheet 2 ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 So basically the function i need is that if the client on the first line item has $300 to spend on oranges and oranges cost $0.50 each based off Worksheet 2 then how many oranges can the client expect? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference Calculations
Hi Again,
What was outlined worked for me. The lookup table is sorted, but the lookup value does not need to be sorted. Thanks, "K Dales" wrote: One note: VLOOKUP will only work if the ID is sorted "David" wrote: Simplified, all of one worksheet ID Item Budget Forecasted 3 Oranges $300 600 1 Apples $32 46 3 Oranges $212 424 5 Pears $198 220 =C5/VLOOKUP(A5,$A$8:$C$13,3,FALSE) ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 "DH487" wrote: This question is long winded so forgive me. I need to develop a function that takes an identifer matches that id with an id on a subsequent worksheet and returns a value for that id which is then used in a math function to calculate forecast. i.e. Worksheet 1 ID Item Budget Forecasted Amount 3 Oranges $300 ? 1 Apples $32 ? 3 Oranges $212 ? 5 Pears $198 ? Worksheet 2 ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 So basically the function i need is that if the client on the first line item has $300 to spend on oranges and oranges cost $0.50 each based off Worksheet 2 then how many oranges can the client expect? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference Calculations
That does the trick. Thank you very much David!
"David" wrote: Simplified, all of one worksheet ID Item Budget Forecasted 3 Oranges $300 600 1 Apples $32 46 3 Oranges $212 424 5 Pears $198 220 =C5/VLOOKUP(A5,$A$8:$C$13,3,FALSE) ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 "DH487" wrote: This question is long winded so forgive me. I need to develop a function that takes an identifer matches that id with an id on a subsequent worksheet and returns a value for that id which is then used in a math function to calculate forecast. i.e. Worksheet 1 ID Item Budget Forecasted Amount 3 Oranges $300 ? 1 Apples $32 ? 3 Oranges $212 ? 5 Pears $198 ? Worksheet 2 ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 So basically the function i need is that if the client on the first line item has $300 to spend on oranges and oranges cost $0.50 each based off Worksheet 2 then how many oranges can the client expect? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference Calculations
Vlookup only needs to be sorted if you do not supply the 4th argument (false,
the default is true looking for the closest match). If that is left out then the lookup range absolutely needs to be sorted. "David" wrote: Hi Again, What was outlined worked for me. The lookup table is sorted, but the lookup value does not need to be sorted. Thanks, "K Dales" wrote: One note: VLOOKUP will only work if the ID is sorted "David" wrote: Simplified, all of one worksheet ID Item Budget Forecasted 3 Oranges $300 600 1 Apples $32 46 3 Oranges $212 424 5 Pears $198 220 =C5/VLOOKUP(A5,$A$8:$C$13,3,FALSE) ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 "DH487" wrote: This question is long winded so forgive me. I need to develop a function that takes an identifer matches that id with an id on a subsequent worksheet and returns a value for that id which is then used in a math function to calculate forecast. i.e. Worksheet 1 ID Item Budget Forecasted Amount 3 Oranges $300 ? 1 Apples $32 ? 3 Oranges $212 ? 5 Pears $198 ? Worksheet 2 ID Item Cost Per 1 Grapes $0.70 2 Berries $0.25 3 Oranges $0.50 4 Bananas $0.55 5 Pears $0.90 So basically the function i need is that if the client on the first line item has $300 to spend on oranges and oranges cost $0.50 each based off Worksheet 2 then how many oranges can the client expect? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
| Changing sheet reference to cell reference | Excel Worksheet Functions | |||
| Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
| Row reference increment but preserve column reference | Excel Worksheet Functions | |||
| Macro to Reference Column Next to Current Reference | Excel Discussion (Misc queries) | |||
Linear Mode
