| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need urgent help creating a nested if/lookup problem or other solution
Hi Guys I have a table Carpet m2 4 6 8 Golden Berber Twist 22.99 26.99 28.99 Eco Blue 14.99 18.99 21.99 Royal Crown 19.99 21.99 24.99 Southern Stripes 12.99 14.99 18.99 Black Widow 18.99 20.99 22.99 Play Town 19.99 21.99 24.99 Royal Keshan 14.99 18.99 21.99 Motet Grey 14.99 18.99 22.99 weave crown 21.99 24.99 26.99 Wild Flower 19.99 21.99 23.99 Classic Floral 26.99 28.99 34.99 The table starts in A73 and goes down to A84 as i need to have it all on the same sheet just hidden. In cell d30 i have the name of a carpet for example Motet Grey In cell D31 i have the size roll needed for example 4 in cell D32 i want the price of the roll to show up automatically Any Ideas? Thanks Gina x -- gini76 ------------------------------------------------------------------------ gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or other solution
vlookup wont work as it needs to come back with a result depending on the name and size of carpet there are 3 dif sizez 4 6 and 8 and need a price depending on carpet so it wont be a simple vlookup needs to include an if function and also needs to lookup the name of the carpet with it -- gini76 ------------------------------------------------------------------------ gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or other solution
On Sun, 8 Feb 2009 22:15:00 +0000, gini76
wrote: Hi Guys I have a table Carpet m2 4 6 8 Golden Berber Twist 22.99 26.99 28.99 Eco Blue 14.99 18.99 21.99 Royal Crown 19.99 21.99 24.99 Southern Stripes 12.99 14.99 18.99 Black Widow 18.99 20.99 22.99 Play Town 19.99 21.99 24.99 Royal Keshan 14.99 18.99 21.99 Motet Grey 14.99 18.99 22.99 weave crown 21.99 24.99 26.99 Wild Flower 19.99 21.99 23.99 Classic Floral 26.99 28.99 34.99 The table starts in A73 and goes down to A84 as i need to have it all on the same sheet just hidden. In cell d30 i have the name of a carpet for example Motet Grey In cell D31 i have the size roll needed for example 4 in cell D32 i want the price of the roll to show up automatically Any Ideas? Thanks Gina x Try this formula in cell D32: =INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) Hope this helps / Lars-Åke |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or other solution
Carpet m2 4 6 8
Golden Berber Twist 22.99 26.99 28.99 Eco Blue 14.99 18.99 21.99 Royal Crown 19.99 21.99 24.99 Southern Stripes 12.99 14.99 18.99 Black Widow 18.99 20.99 22.99 Play Town 19.99 21.99 24.99 Royal Keshan 14.99 18.99 21.99 Motet Grey 14.99 18.99 22.99 weave crown 21.99 24.99 26.99 Wild Flower 19.99 21.99 23.99 Classic Floral 26.99 28.99 34.99 The table starts in A73 and goes down to A84 as i need to have it all on the same sheet just hidden. In cell d30 i have the name of a carpet for example Motet Grey In cell D31 i have the size roll needed for example 4 in cell D32 i want the price of the roll to show up automatically Perhaps something like this would help: =VLOOKUP(A30,A74:D84,A31/2,TRUE) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or other
Hi,
Well normally you couldn't use a simple VLOOKUP but since your data just happens to be in sizes 4,6,8 you can use the following =VLOOKUP(A30,A74:D84,A31/2,) -- If this helps, please click the Yes button Cheers, Shane Devenshire "gini76" wrote: vlookup wont work as it needs to come back with a result depending on the name and size of carpet there are 3 dif sizez 4 6 and 8 and need a price depending on carpet so it wont be a simple vlookup needs to include an if function and also needs to lookup the name of the carpet with it -- gini76 ------------------------------------------------------------------------ gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or other solution
thanks to all... Lars i used yours and it works.. ur a genious thanks there go my sleepness nights... till the next section of the coursework thanks a billion xxaha! -- gini76 ------------------------------------------------------------------------ gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or other solu
Hi,
Your basic formula is =VLOOKUP(AP4,A4:Z40,26,) As far as making 28 copies of the sheet: 1. Press and hold the Shift+F11 key until you have 28 copies (pretty quickly) 2. Move to the sheet with the data. 3. Click the top left corner of the sheet to select the whole thing and press Ctrl+C (copy) 4. Click the sheet tab for the first of the new sheets, hold down the Shift key and click on the last sheet of the 28 you just added, this will select from the active sheet to the last sheet. 5. Press Ctrl+V (paste). 6. Right click on the active sheet's tab and choose Ungroup sheets. -- If this helps, please click the Yes button Cheers, Shane Devenshire "gini76" wrote: Hi Guys I have a table Carpet m2 4 6 8 Golden Berber Twist 22.99 26.99 28.99 Eco Blue 14.99 18.99 21.99 Royal Crown 19.99 21.99 24.99 Southern Stripes 12.99 14.99 18.99 Black Widow 18.99 20.99 22.99 Play Town 19.99 21.99 24.99 Royal Keshan 14.99 18.99 21.99 Motet Grey 14.99 18.99 22.99 weave crown 21.99 24.99 26.99 Wild Flower 19.99 21.99 23.99 Classic Floral 26.99 28.99 34.99 The table starts in A73 and goes down to A84 as i need to have it all on the same sheet just hidden. In cell d30 i have the name of a carpet for example Motet Grey In cell D31 i have the size roll needed for example 4 in cell D32 i want the price of the roll to show up automatically Any Ideas? Thanks Gina x -- gini76 ------------------------------------------------------------------------ gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or other solution
Lars-Åke Aspelin;219918 Wrote: On Sun, 8 Feb 2009 22:15:00 +0000, gini76 wrote: Hi Guys I have a table Carpet m2 4 6 8 Golden Berber Twist 22.99 26.99 28.99 Eco Blue 14.99 18.99 21.99 Royal Crown 19.99 21.99 24.99 Southern Stripes 12.99 14.99 18.99 Black Widow 18.99 20.99 22.99 Play Town 19.99 21.99 24.99 Royal Keshan 14.99 18.99 21.99 Motet Grey 14.99 18.99 22.99 weave crown 21.99 24.99 26.99 Wild Flower 19.99 21.99 23.99 Classic Floral 26.99 28.99 34.99 The table starts in A73 and goes down to A84 as i need to have it all on the same sheet just hidden. In cell d30 i have the name of a carpet for example Motet Grey In cell D31 i have the size roll needed for example 4 in cell D32 i want the price of the roll to show up automatically Any Ideas? Thanks Gina x Try this formula in cell D32: =INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) Hope this helps / Lars-Åke Can i ask you to explain this formula as i like to know how things work so can do them again :) thanks for the help -- gini76 ------------------------------------------------------------------------ gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or other solution
You may feel better about it if you explain it to yourself by looking in the
help index for MATCH and then for INDEX. -- Don Guillett Microsoft MVP Excel SalesAid Software "gini76" wrote in message ... Lars-Åke Aspelin;219918 Wrote: On Sun, 8 Feb 2009 22:15:00 +0000, gini76 wrote: Hi Guys I have a table Carpet m2 4 6 8 Golden Berber Twist 22.99 26.99 28.99 Eco Blue 14.99 18.99 21.99 Royal Crown 19.99 21.99 24.99 Southern Stripes 12.99 14.99 18.99 Black Widow 18.99 20.99 22.99 Play Town 19.99 21.99 24.99 Royal Keshan 14.99 18.99 21.99 Motet Grey 14.99 18.99 22.99 weave crown 21.99 24.99 26.99 Wild Flower 19.99 21.99 23.99 Classic Floral 26.99 28.99 34.99 The table starts in A73 and goes down to A84 as i need to have it all on the same sheet just hidden. In cell d30 i have the name of a carpet for example Motet Grey In cell D31 i have the size roll needed for example 4 in cell D32 i want the price of the roll to show up automatically Any Ideas? Thanks Gina x Try this formula in cell D32: =INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) Hope this helps / Lars-Åke Can i ask you to explain this formula as i like to know how things work so can do them again :) thanks for the help -- gini76 ------------------------------------------------------------------------ gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or other solu
Do believe your post was meant for the thread just below (OP = barry), not
here -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or other solution
In d31 i have an if function IF(D24<=4,"4",IF(D24<=6,"6",IF(D24<=8,"8"))) so that returns the size of the carpet now if i use that function in d31 and have the =INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) in cell d32 it doesnt work if i simply type in a 4 or 6 or 8 it does but part of my qustion is to automaticaly have the 4 6 or 8 calculated... any ideas. Lars-Åke Aspelin;219918 Wrote: On Sun, 8 Feb 2009 22:15:00 +0000, gini76 wrote: Hi Guys I have a table Carpet m2 4 6 8 Golden Berber Twist 22.99 26.99 28.99 Eco Blue 14.99 18.99 21.99 Royal Crown 19.99 21.99 24.99 Southern Stripes 12.99 14.99 18.99 Black Widow 18.99 20.99 22.99 Play Town 19.99 21.99 24.99 Royal Keshan 14.99 18.99 21.99 Motet Grey 14.99 18.99 22.99 weave crown 21.99 24.99 26.99 Wild Flower 19.99 21.99 23.99 Classic Floral 26.99 28.99 34.99 The table starts in A73 and goes down to A84 as i need to have it all on the same sheet just hidden. In cell d30 i have the name of a carpet for example Motet Grey In cell D31 i have the size roll needed for example 4 in cell D32 i want the price of the roll to show up automatically Any Ideas? Thanks Gina x Try this formula in cell D32: =INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) Hope this helps / Lars-Åke -- gini76 ------------------------------------------------------------------------ gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need urgent help creating a nested if/lookup problem or othersolution
Ah, you've answered my earlier question from another thread. Your IF
function is returning text values, not real numbers. All you need in D31 is: =D24 Hope this helps. Pete On Feb 9, 4:42*pm, gini76 wrote: In d31 i have an if function IF(D24<=4,"4",IF(D24<=6,"6",IF(D24<=8,"8"))) * * so that returns the size of the carpet now if i use that function in d31 and have the =INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) in cell d32 it doesnt work if i simply type in a 4 or 6 or 8 it does but part of my qustion is to automaticaly have the 4 6 or 8 calculated... any ideas. Lars-Åke Aspelin;219918 Wrote: On Sun, 8 Feb 2009 22:15:00 +0000, gini76 wrote: Hi Guys I have a table Carpet m2 * * *4 * * * 6 * * * 8 Golden Berber Twist * *22.99 * 26.99 * 28.99 Eco Blue * * * 14.99 * 18.99 * 21.99 Royal Crown * *19.99 * 21.99 * 24.99 Southern Stripes * * * 12.99 * 14.99 * 18.99 Black Widow * *18.99 * 20.99 * 22.99 Play Town * * *19.99 * 21.99 * 24.99 Royal Keshan * 14.99 * 18.99 * 21.99 Motet Grey * * 14.99 * 18.99 * 22.99 weave crown * *21.99 * 24.99 * 26.99 Wild Flower * *19.99 * 21.99 * 23.99 Classic Floral 26.99 * 28.99 * 34.99 The table starts in A73 and goes down to A84 as i need to have it all on the same sheet just hidden. In cell d30 i have the name of a carpet for example Motet Grey In cell D31 i have the size roll needed for example 4 in cell D32 i want the price of the roll to show up automatically Any Ideas? Thanks Gina x Try this formula in cell D32: =INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) Hope this helps / Lars-Åke -- gini76 ------------------------------------------------------------------------ gini76's Profile:http://www.thecodecage.com/forumz/member.php?userid=118 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=60469- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need Urgent Help Creating a Formula | Excel Discussion (Misc queries) | |||
| Need A Solution To A Problem | Excel Discussion (Misc queries) | |||
| Urgent help..nested functions.. | Excel Discussion (Misc queries) | |||
| urgent solution needed to 'custom view' re-calculation problem! | Excel Discussion (Misc queries) | |||
| Nested Subtotals in Excel 2003 -Solution | Excel Discussion (Misc queries) | |||
Linear Mode
