# Thread: conditional range within a formula (2003)

1. ## conditional range within a formula (2003)

I have a spreadsheet that I am trying to automate for someone. Basically, it has a staff allocation sheet to assign staff to a project on a monthly basis, and then creates monthly project resource expenditure report using a lookup table to find the correct monthly salary. I am trying to use the offset function to select the correct range in the monthly allocation sheet, but I can't get it to work. Can anyone help?

For a single person, this formula works:
=IF(MATCH(A7, 'Staff Project Allocation'!B14:B16), VLOOKUP(A7,'Staff Project Allocation'!B14:C16, 2)*VLOOKUP(VLOOKUP('Rolling Grant'!B7, 'Staff Information'!B2:E38, 4, FALSE), SalaryLookUp, 2), "")

In the formula above, A7 is the project name on the project budget sheet and basically B14:C16 has selectable project names in the first column and percentage time allocation in the second column. What I want to do is replace the range B14:B16 (and similarly B14:c16) with: OFFSET('Staff Project Allocation'!\$A\$1, MATCH('Rolling Grant'!B7, 'Staff Project Allocation'!\$A\$1:\$A\$100, 0), 1, 3, 2). I thought this would give me the project allocation range that is associated with the person's neame, but it doesn't seem to work.

Any suggestions? I attach a stripped down version of the file.

Thanks.

2. ## Re: conditional range within a formula (2003)

At first glance, I would guess that it wouldn't work because the person's name (iss1) isn't copied in Col A next to the data you want (only above it) in the project allocation sheet.

Good luck

3. ## Re: conditional range within a formula (2003)

Note:
You can replace the B14:C16 with your offset, but the B14:B16 needs a different one since match uses only a single column.(note thered in the formula, below

Does this work?
=IF(MATCH(A7, OFFSET('Staff Project Allocation'!\$A\$1, MATCH('Rolling Grant'!B7, 'Staff Project Allocation'!\$A\$1:\$A\$22, 0), 1, 3, <font color=red>1</font color=red>),0), VLOOKUP(A7,OFFSET('Staff Project Allocation'!\$A\$1, MATCH('Rolling Grant'!B7, 'Staff Project Allocation'!\$A\$1:\$A\$22, 0), 1, 3, <font color=red>2</font color=red>), 2)*VLOOKUP(VLOOKUP(B7, 'Staff Information'!B2:E38, 4, FALSE), SalaryLookUp, 2), "")

Steve

4. ## Re: conditional range within a formula (2003)

This seems to work now! Thanks.

I'm sure an access database working with queries would be a much simpler way to implement this, but the person I am doing it for refuses to try and go near access!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•