Search code examples
excelvbatemplatesindexingoffset

Excel - If + Index Match + Offset -- VBA or something else?

I made a dummy version (fake names and extremely shortened) of the 2 spreadsheets I'm working with this spread sheets

Background:

I'm automating the data between Contracts and our Accounting Team's template. Note that neither of these spreadsheet's formats can budge so that why I'm stuck. It's a clunky process that I am trying to automate. The main source of data is the "Contracts" tab. Let's say out of the 300 subcontractors projects, in the week of 1/24/2019 my coworker approved 130 of the projects. The logic of what I am trying to accomplish:

  • In the Contracts tab, if Column R is "Yes"--
  • In the "Accounting Template" tab (the one with formulas) Column B, pull all the cells of Contracts!A of the vendors we are set to pay.
  • The same applies to Template! (a nickname for the path) Column M, pull the specific contract ID's of the approved Contract ID's from Contracts!C.

Note I intentionally showed that my fake Puppies program is NOT approved to get their payment, this will help demonstrate how to resolve my issue

My key issue is that the Accounting Template skips every 3 rows for the Project, and the Contracts row has Project day every single row. So, for Template!A5, I am pulling data from Contracts!A2, and Template!A8 I am pulling data from Contracts!A3, etc.

I was able to (sort of) make this work with an offset, row and index match:

=OFFSET(INDEX(Contracts!$C$2:$C$167,MATCH(ROWS(Contracts!$A$2:A17),Contracts!$AB$2:$AB$167,0)),-10,0)

See that negative -10? For each new 3rd row I am starting at template, I'm manually changing it to -10, -12, -14, etc etc. Not exactly sophisticated.

Looking at how offset and row work, it looks as though they heavily rely on the coordinates of cells in the Contracts workbook. However, I ideally am looking to do this:

=IF(Contracts!R2="Yes",OFFSET(INDEX(Contracts!$C$2:$C$167,MATCH(ROWS(Contracts!$A$2:A5),Contracts!$AB$2:$AB$167,0)),-2,0))

However, once I throw a conditional (IF) in the mix, that reorientating the rows of my offset match. Are there better formulas for what I am trying to accomplish? A VBA script that could accomplish this IF, INDEX, MATCH, OFFSET, ROW dream of mine? I'm not married to either of these formulas. I've perused a few VBAs but nothing seems to have a conditional like IF as a component.

EDIT:

Per a request, adding screenshots. There's also a Google Sheet link: Contracts tab, purposely hiding irrelevant columns:

enter image description here

Accounting Template Tab: enter image description here

Solution

  • I'd do it with VBA, but this formula example might help you start. If your issue is basically turning horizontal data into vertical data and you have a fixed interval of 3 rows. You will need to adapt the formulae for your actual set up.

    The formulas used are:

    F1 and down =IF(MOD(ROW()-1,3)=0,INDEX($A$1:$A$3,(ROW()+2)/3),"")

    G1 and down =INDEX($B$1:$D$3,CEILING(ROW(),3)/3,1+MOD(ROWS($G$1:G1)-1,3))

    enter image description here

    I'm sure there are better ways ...