Locating a cell in sheet 2, based on 2 variables in sheet 1. Result into sheet 1.

Hi and welcome to the forum

Your formulas in C seem to be referening another workbook. I would suggest that you manually enter a few sample answers so we can see where they come from

Sorry about that. Thanks for the heads up.
I have linked the answers to sheet 2 for the variable "1.4.1*LANDSCAPE ARCHITECT"
Another point is the result is actually 7 rows below not 6 as previously stated.

"An average hourly rate has been calculated by company and service, 6 rows below the service category heading."

C6 =Sheet2!E243 $-
C7 =Sheet2!G243 $-
C8 =Sheet2!H243 $-
C9 =Sheet2!J243 $134.25
C10=Sheet2!L243 $-
C11 =Sheet2!N243 $-
C12 =Sheet2!P243 $-
C13=Sheet2!R243 $131.75

I couldn't work out how to update the file, so I have added the reference instead.
I have now uploaded revised file.

Thanks.

I am thinking the solution may be something like this suggestion of another query
http://forum.chandoo.org/threads/dyn...-lookup.12997/
Hopefully I am hopeful I am not breaking a forum rule by posting a link to another site. It didn't appear to be the case in the forum rules. I am happy to stand corrected if I am wrong.

Thanks

You did not explain how you select the row you wanted (and how this could be repeated for each section). This will pull the data you want, but I had to manually code the row number (7), but from the looks of it, it will not always be the 7th row?...
=OFFSET(INDIRECT("Sheet2!"&ADDRESS(MATCH($B$2,Sheet2!$C:$C,0),3)),7,MATCH($B6,Sheet2!$C$15:$GS$15,0))

If you can help me to understand how you would pick the specific row in a section, we can try and get that part automated too

Thanks for your response.

The row is selected by the variable in sheet 1 at cell B2.
Cell B2 has a drop down list of options (it didn't carry through in the simplified example attached)which are the service on sheet 2.
i.e.
1.1.1 ARCHITECT,
1.1.2 INTERIOR DESIGN,
1.2*BUILDING CERTIFICATION AND AUDIT,
1.3.1 CIVIL ENGINEERING DESIGN,
1.3.2 ELECTRICAL ENGINEERING DESIGN,
1.3.3 FIRE SYSTEMS DESIGN,
1.3.4 HYDRAULIC ENGINEERING DESIGN,
1.3.5*MECHANICAL ENGINEERING DESIGN,
1.3.6*SECURITY/CCTV DESIGN
1.3.7*STRUCTURAL ENGINEERING DESIGN
1.4.1*LANDSCAPE ARCHITECT
1.4.2*LANDSCAPE
1.5*PROJECT MANAGEMENT
1.6*QUANTITY SURVEYING
and this list may be expanded

In sheet 1 the variable is
1.4.1*LANDSCAPE ARCHITECT (at B2)

I hope that is the information you were seeking.

For those services for which the average rate is not 7 rows below the service title (i.e. 1.2 BUILDING CERTIFICATION AND AUDIT) I will modify the format so that all averages will be 7 rows below the service, in sheet 2.

1.1.1 ARCHITECT,
1.1.2 INTERIOR DESIGN,
1.2*BUILDING CERTIFICATION AND AUDIT,
1.3.1 CIVIL ENGINEERING DESIGN,
1.3.2 ELECTRICAL ENGINEERING DESIGN,
1.3.3 FIRE SYSTEMS DESIGN,
1.3.4 HYDRAULIC ENGINEERING DESIGN,
1.3.5*MECHANICAL ENGINEERING DESIGN,
1.3.6*SECURITY/CCTV DESIGN
1.3.7*STRUCTURAL ENGINEERING DESIGN
1.4.1*LANDSCAPE ARCHITECT
1.4.2*LANDSCAPE
1.5*PROJECT MANAGEMENT
1.6*QUANTITY SURVEYING

Yes, I get that, that is for each option in the D/D. But within each of those, you have a further breakdown...

	C
236	1.4.1*LANDSCAPE ARCHITECT
237	Principal
238	Senior
239	Mid-level
240	Graduate
241	Draftsperson
242	Technical Officer
243	Other – Please add details

You have selected the data from row 243, what determines/decides, that that is the row (from that category) to choose. What Im saying is, how would I know that is the row to use (so that I can build a formula that will select that row) - what is the criteria...why did you select "Other – Please add details" and not "Draftsperson", for instance?

Sorry for the confusion. It essentially poor spread sheet design and it was done in a hurry.

A better design would have row 243 titled "average", not other and row 244 titled other. Those in pink format have been provided by tendering companies and are outside the scope of what was requested, but they have been captured as a reference.

My apologise for being tardy.

I have input the formulae and it is appears to be providing the exact result I was seeking.

You are a legend and I thank you greatly for your assistance. I will mark the thread {SOLVED} once I get back to work (tomorrow) and test it on the full version and I will also add to your reputation.

As an aside, how can I best start to give back to this forum that has helped me out on a couple of tough formulas in recent years?

Thanks again
Gezza

Thanks for the kind words, they are always most welcome and appreciated

regarding my formula...
=OFFSET(INDIRECT("Sheet2!"&ADDRESS(MATCH($B$2,Sheet2!$C:$C,0),3)),7,MATCH($B6,Sheet2!$C$15:$GS$15,0))

That will ONLY pull in data/values/answers that are exactly 7 rows down from the "heading", hence my question regarding how you select that specific row. Some of those "sets" have far more rows in them than others, and we need to be able to ID which row to pick...just picking "row 7" will not always work, will it?

(1.4.2*LANDSCAPE is 6 rows, 1.5*PROJECT MANAGEMENT is only 5 rows to that "others" row)

On a side note, "1.4.2*LANDSCAPE " has 5 trailing spaces at the end. This could cause problems if you need to refer to that text string in a formula (dont know if any others have the same problem, just noticed that 1)

Have a magnificent night / morning / day. Is it really )Time in Duncansville, PA, USA - 1:31 AM Sunday, November 3, 2013 (EST), )

yes that is probably what the time was then, I work 2nd shift, so dont go to bed at normal human hours lol


Again, thanks for the kind words, and I was happy to be able to help

Hello once again.

Unfortunately I have used the formulae in the main workbook and I am now getting a #value! error.

Formulae entered is
=OFFSET(INDIRECT("Hourly Rates!"&ADDRESS(MATCH($B$2,Hourly Rates!$C:$C,0),3)),7,MATCH($B6,Hourly Rates!$C$15:$GS$15,0))

but when going back to edit the formulae I have discovered [Rates] has appeared in the formulae.

=OFFSET(INDIRECT("Hourly Rates!"&ADDRESS(MATCH($B$12,Hourly[Rates]Rates!$C:$C,0),3)),7,MATCH($B16,Hourly [Rates]Rates!$C$15:$GS$15,0))

Is there some hidden code? Why would the[Rates] text appear in the formulae?

When I go to update the formulae with [Rates] in it I get prompted to "Update Values:Rates".

In the simplified workbook the formulae works fine. Any help would be appreciated.

Hard to say without seeing what the exact error message looks like/refers to, but check to make sure that the sheet names match exactly what you have in the formula.

Also, my formula was...
=OFFSET(INDIRECT("Sheet2!"&ADDRESS(MATCH($B$2,Sheet2!$C:$C,0),3)),7,MATCH($B6,Sheet2!$C$15:$GS$15,0))
you have changed it to...
=OFFSET(INDIRECT("Bus & AH Hourly Rates!"&ADDRESS(MATCH($B$12,Bus & AH Hourly Rates!$C:$C,0),3)),7,MATCH(B16,Bus & AH Hourly Rates!D15:GS15,0))

If you have spaces in your sheet name, you need to change the...
INDIRECT("Bus & AH Hourly Rates!"&
to...
INDIRECT("'Bus & AH Hourly Rates'!"& (not the extra ' before Bus and before !)

Sorry I didn't see page 2 of the thread before sending my reply.

The issue has been resolved for the moment, but I am not sure exactly what is/was occuring, but this was the issue identified / solution.

The worksheet was previously labelled "Bus & AH Hourly Rates" and the formulae wouldn't work, as written below.
=OFFSET(INDIRECT("Bus & AH Hourly Rates!"&ADDRESS(MATCH($B$12,Bus & AH Hourly Rates!$C:$C,0),3)),7,MATCH($B16,Bus & AH Hourly Rates!$C$15:$GS$15,0))

Change the name to HourlyRates (no spaces) and it does work. (but the formulae had to be manually edited)
=OFFSET(INDIRECT("HourlyRates!"&ADDRESS(MATCH($B$12,HourlyRates!$C:$C,0),3)),7,MATCH($B16,HourlyRates!$C$15:$GS$15,0))

An issue arises when the name of the worksheet titled HOURLYRATES is changed to any name with a space, but only for the first part of the formulae
i.e. =OFFSET(INDIRECT("HourlyRates!"&ADDRESS. It seems to be the part of the formulae enclosed in double quotation marks is having some impact.

If the name of the worksheet tab is edited ( to say Hourly Fees) this "HourlyRates" reference in the formulae doesn't change, but the subsequent references (remaining part of formulae) do.
i.e. (MATCH($B$12,Hourly Fees!$C:$C,0),3)),7,MATCH($B16,Hourly Fees!$C$15:$GS$15,0))

The formulae then reads =OFFSET(INDIRECT("HourlyRates!"&ADDRESS(MATCH($B$12,Hourly Fees!$C:$C,0),3)),7,MATCH($B16,Hourly Fees!$C$15:$GS$15,0))

I have also discovered that the inclusion of "&" in the worksheet name also has a negative impact. i.e Bus&AHHourlyRates (even with no spaces)

So I can close this thread, can anyone explain why this behaviour with the formulae is occurring????

Specifically why doesn't the reference to the worksheet name in the first part of the formulae update when the worksheet name is changed/modified.

Thanks

with this formula...
=OFFSET(INDIRECT("HourlyRates!"&ADDRESS(MATCH($B$12,Hourly Fees!$C:$C,0),3)),7,MATCH($B16,Hourly Fees!$C$15:$GS$15,0))...
this part...
=INDIRECT("HourlyRates!"&ADDRESS(MATCH($B$12,Hourly Fees!$C:$C,0),3))
in this part, "HourlyRates" is text, not a reference. INDIRECT is a function that converts text into a reference that excel can use in a formula. I need to enter the sheet name "manually" so that I can "add" it to the discipline to find its location.

as far as the rest of the formula is concerned, that worked because I was referencing the sheet name directly, so when the sheet name changed, so did the references to it

Regarding the spaces in the sheet name. If there are spaces in the name, the name needs to be wrapped in '...' so sheet1!a1 becomes "sheet 1'!a1. Try it yourself. On sheet1, reference sheet1 A1, look at the formula, then change the sheet name to sheet 2, and look at the formula again

It was a pleasure being able to help you, and thank you for the kind words and feedback