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!

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