Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help... almost done with this project
Steve,
Admit I'm still guessing here.. Earlier I couldn't figure out the significance of your input in E16: B, so I simply omitted that from my suggestion. Here's what I assume now, working in the input in E16. In sheet: Granite-Wholesale you have the phrases in: E61 €śTotal A€ť F61 €śTotal B€ť G61 €śTotal C€ť H61 €śTotal D€ť and I'll presume that you want to extract the corresponding figure just below the phrase (within E62:H62) based on your input in E16 in sheet: Info Sheet. So if E16 contains B, then you want to return the figure in F62 (just below €śTotal B€ť in F61) If so, then try this instead .. In sheet: Info Sheet, Array-enter (press CTRL+SHIFT+ENTER) into M14: =IF(OR($E$10="",$B$22="",$E$16=""),"",INDEX(INDIRE CT("'"&$E$10&"-"&$B$22&"'!E62:H62"),,MATCH(TRUE,ISNUMBER(SEARCH(E 16,INDIRECT("'"&$E$10&"-"&$B$22&"'!E61:H61"))),0))) The above will return the required result in M14 from the correct sheet based on inputs in E10, E16 and B22. It will work provided all your source sheets, eg: Granite-Wholesale, Granite-Retail, etc are identically structured. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "S Willingham" wrote: Max, Thanks for your response. Could you elaborate on what you have done. I assume I need to add info between the quotes but I am not sure what to enter where. Basically I am trying to analyze 3 seperate cells to bring info from a 4th cell (in a seperate worksheet) Thanks for your help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help... almost done with this project
OK That is COOL. can you explain how it works. I might want to duplicate it
in another application. Thanks again for all the help! "Max" wrote: Steve, This revision should do it. In M14 (normal ENTER will do): =IF(OR(E10="",B22="",E16=""),"",INDEX(INDIRECT("'" &E10&"-"&B22&"'!E61:H61"),,MATCH(E16,{"A";"B";"C";"D"},0) )) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "S Willingham" wrote: Thanks Max E61 to H61 contain the info needed. That is, they contain the total of the quote for the color group A-D I entered the formula and it returned a #N/A |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert MS Project to MS Excel. I don't have MS Project. | Excel Discussion (Misc queries) | |||
Help with a project | Excel Discussion (Misc queries) | |||
Lock Project | Excel Discussion (Misc queries) | |||
Need a project. | New Users to Excel | |||
I pay $ for this project | Charts and Charting in Excel |