Combine IF with OFFSET

[priyaxm]

Hi I am trying to create a dynamic formula, with a variable you can change.

I have different stages of an employment in my columns e.g. below

Applications

Apps to interviews

Successful at medicals

Successful medicals to training school

Successful trainees at training school

I also have lead times that you can change and I have used the OFFSET formula so the applications to interviews row updates automatically based on whatever you put in the "lead times" section

Lead time and success rates section which can be changed by user


The applications is the ONLY column that is driven by a data source.

I want the remainder 4 rows to be driven by a formula so that when the lead time/success rates assumptions are updated by the user, it automatically updates 'successful at medicals' or 'medicals to training school' row to reflect the dynamic assumptions because at the moment i am using IFERROR but it is static - it wont change.

Dynamic lookup dummy data.xlsx

[Adativs]

Hi priyaxm

Wasn't 100% clear on the request but does the solution in the attached work for you? It's made easier by having a consistent numerical value to represent each gate stage (Column A).

Please Login or Register  to view this content.

Dynamic lookup dummy data_Adativs.xlsx

HTH

[AliGW]

Administrative Note:

Please include formulae used in your post in addition to providing a workbook (for the benefit of members unable to download attachments).

Thanks.

[Adativs]

Hi Ali,

Post updated, although the formula is relatively meaningless out of context but I understand the rationale for doing it, sorry for not adhering to the rules (it's been about 8 years since I was last posting here and I should know better than to not check them again! )

Thanks