Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!

    Thanks for all your help!

Posting Permissions

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