0

I'm trying to make a printable template. Here is the simple example.

So basically what I want is to automatically move up/down the blue cells in the "Invoice" sheet immediately below the red cells above without empty rows in between. For example, if the customer ID is changed from 2 to 1, there will be only two red filled rows and therefore the blue cells will move up by 2. Similarly, if the customer ID is changed from 1 to 2, two additional red rows will be added and the blue cells will be pushed down by 2.

Is there any way to automate this, preferably without Apps Script? I would if it helps but I struggled so much bc I don't have any programming background.

It takes me hours just to do simple things without any excel/sheets experience and any tips or pointing me to the right direction would be much appreciated.

1

1 Answer 1

0

Use an { array expression } to build the whole invoice in one go, like this:

={ 
  query( 
    Log!A2:D7, 
    "select B, C, D 
     where A = " & 'Invoice Trigger'!B1, 
    0 
  ); 
  "Previous Balance", "", 'Invoice Trigger'!F3; 
  "Charged", "", 'Invoice Trigger'!E3; 
  "Due", "", 'Invoice Trigger'!F3 + 'Invoice Trigger'!E3 
}

Clear all red and blue cells in the 'Invoice' sheet and put the formula in cell A2.

The formula expects to find the Previous Balance figure in cell 'Invoice Trigger'!F3.

If cell fill colors are of importance, use a conditional formatting custom formula rules like =isdate($A2) and =istext($A2) to paint the rows the way you want.

1
  • thank you. it's been great help
    – Harry
    Commented Dec 18, 2021 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.