Perhaps you're after something along these lines ..

In sheet: Info Sheet

Inputs
In E10: Granite
In B22: Wholesale

Then place in M14:
=IF(OR($E$10="",$B$22=""),"",INDEX(INDIRECT("'"&$E $10&"-"&$B$22&"'!E61:H61"),ROW(A1)))
Copy M14 down to M17. M14:M17 will retrieve whats within E61:H61 in sheet:
Granite-Wholesale.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"S Willingham" wrote:
Thanks in advance€¦ I am almost done with this project and have one more thing
that I need help with.

I have a Workbook called €˜Quote TemplateBeta ver2 within the workbook I
have multiple worksheets The first sheet €˜Info Sheet contains various
information for pricing countertops. I enter the information and it populates
the various quote sheets that correspond to the appropriate type of job.

On the €˜Info Sheet I have the following cells

B22 (Type of job)
E10 (Material)
E16 (Price Group)

And I have quote sheets (within the same workbook) that correspond to the
various types of Jobs (wholesale, retail) and material (Granite, Silestone)

For this example lets say I have entered the following

B22 €œWholesale€�
E10 €œGranite€�
E16 €œB€�

I need a formula that will return the amount from the appropriate cell
within the €˜Granite-Wholesale worksheet to the €˜Info Sheet M14

E61 €œTotal A€�
F61 €œTotal B€�
G61 €œTotal C€�
H61 €œTotal D€�

I have 4 types of material and 3 different job types. I think I can modify
the formula once somebody gets me on the right track.

Thanks
Steve

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


"Max" wrote:

Since it's a single cell formula in M14 (not to be filled down/across), think
we could safely remove all the dollar signs from the cell refs for E10, E16
and B22, viz just place in M14, array-entered:

=IF(OR(E10="",B22="",E16=""),"",INDEX(INDIRECT("'" &E10&"-"&B22&"'!E62:H62"),,MATCH(TRUE,ISNUMBER(SEARCH(E16 ,INDIRECT("'"&E10&"-"&B22&"'!E61:H61"))),0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Glad we got it resolved finally <g

=IF(OR(E10="",B22="",E16=""),"", ..
This front part of it simply checks that all 3 input cells contain "inputs"
(viz. something) before evaluating any further

... INDEX(INDIRECT("'"&E10&"-"&B22&"'!E61:H61"),, ...)
The INDIRECT will resolve the concat string composed from the inputs in E10
& B22 to return the correct sheetname for the indexed range. This is the main
flexibility provided.

.... INDEX(... ,,MATCH(E16,{"A";"B";"C";"D"},0)))
The matching of the input in E16 against the constant array: {"A";"B";"C";"D"}
returns the correct col number for the INDEX to return from the particular
sheet's indexed range "E61:H61"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"S Willingham" wrote:
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!

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