• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel formula help needed

B

brianaclark1967

Member
Hi all,

I have attached a sample sheet.

The 1st sheet is a delivery note, the 2nd sheet is the invoice.

I want to only pull information through to the invoice when the item has a quantity next to it on the delivery note.

I did have a formula for a small sample sheet that worked, but each time I add rows it messes the formula up and doesn't work.

I appreciate any help you can give give me.

Regards

Brian
 

Attachments

  • Template edited.xlsx
    250.9 KB · Views: 0
Since you are using direct cell reference, you can use something like...
For Column A: =IF('Del & Col Note'!A11="","",'Del & Col Note'!A11)
Adjusting reference as needed.

Then all other columns you can reference corresponding cell in column A and return blank if reference cell is blank.

I didn't go through entire sheet, but see A36:C38 for example.
 

Attachments

  • Template edited.xlsx
    252 KB · Views: 0
Chihiro said:
Since you are using direct cell reference, you can use something like...
For Column A: =IF('Del & Col Note'!A11="","",'Del & Col Note'!A11)
Adjusting reference as needed.

Then all other columns you can reference corresponding cell in column A and return blank if reference cell is blank.

I didn't go through entire sheet, but see A36:C38 for example.
Click to expand...


Thank you Chihiro,
I was looking for something that only pulled through rows that had a quantity in them.

Regards

Brian
 
Hi Brain,

Plz see attachment, merged cells in your sample are problematic that led me to make a new file.
 

Attachments

  • Del Note and Invoice.xlsx
    12.1 KB · Views: 0
Hi Faseeh,

Firstly, thank you so much for your help; it is very much appreciated.

So I can tailor your sheet to match my own, could it include all the items shown in sheet 1 (see attached sheet).

Also how can I add 15 rows above each sheet, without spoiling the formulas you have in place.
I've tried it but I lose the formula.

In those 15 inserted rows I can add all my headings and other details then.

Many thanks

Brian
 

Attachments

  • Del Note and Invoice revised.xlsx
    12.9 KB · Views: 0
You are always welcome.

Please see attachment for 15 added rows.
 

Attachments

  • Del Note and Invoice revised (1).xlsx
    14.7 KB · Views: 0
Hi Faseeh,

Me again.

I'm looking for your help again.

I've attached the sample sheet.

Items 1 to 12 on the delivery note, the invoice is not pulling through item 12.

Also regarding the accessories, I've copied them in a list further down the page to avoid searching the two columns in the layout..

Is there any way that the invoice could pull through the main items 1 - 12 (rows 17 to 40), then search the accessories list (rows 130 to 151)

Thank you in advance.

Regards.

Brian
 

Attachments

  • template 20-8.xlsx
    76.1 KB · Views: 0
For item 12. You skipped over one row in your formula
Row($A13) should be Row($A12)

Not sure what you want for accessories list.
 
Chihiro said:
For item 12. You skipped over one row in your formula
Row($A13) should be Row($A12)

Not sure what you want for accessories list.
Click to expand...


Hi Chihiro,

Thanks for getting back to me.

At the minute the accessories are listed in two columns on rows 42 to 52.
I didn't think the formula would work on two columns so I copied them below in 1 column (rows 130 to 151.

This is just so that on the invoice it searches for any of the first 12 items, and copies them through (if there's a quantity chosen)

But then it copies through any of the accessories (if there's a quantity chosen) in numerical order, searching the list in rows 130 to 151.

regards,

Brian
 
Ah that's easy ;)

The formula works for A41:D52 without an issue.

Since formula is written with 'Del Note'!B$41:B$63 as reference range. As you copy over to another column, it will adjust automatically and will return the value of respective column that match the criteria.
 
Hi Chihiro,

I'm nearly there I think.

I've attached the sheet.

On the invoice you will see all the Doors and accessories are copied though to the invoice.

The problem I have, is if for example I delete item 11 and 12 on the doors; when you look at the invoice it seems to mess with the formula that is pulling the accessories through.

It seems that once I delete anything from the items, it ruins the formula for the accessories.

Many thanks.

Brian
 

Attachments

  • template 20-8.xlsx
    76.1 KB · Views: 0
Ah, that's because you have one continuous range reference of A$18:A$156.
Since it's array formula looking for SMALL. This will cause layout issues.

Starting from A43 in the "Invoice". Use below formula and enter as CSE.
=IFERROR(INDEX('Del Note'!A$130:A$151,SMALL(IF('Del Note'!$A$130:$A$151<>0,IF('Del Note'!$A$130:$A$151<>"",-129+ROW('Del Note'!$A$130:$A$151))),ROW(A1))),"")

For A42, You'd want to keep it direct reference to A41 in "Del Note".
 
Chihiro said:
Ah, that's because you have one continuous range reference of A$18:A$156.
Since it's array formula looking for SMALL. This will cause layout issues.

Starting from A43 in the "Invoice". Use below formula and enter as CSE.
=IFERROR(INDEX('Del Note'!A$130:A$151,SMALL(IF('Del Note'!$A$130:$A$151<>0,IF('Del Note'!$A$130:$A$151<>"",-129+ROW('Del Note'!$A$130:$A$151))),ROW(A1))),"")

For A42, You'd want to keep it direct reference to A41 in "Del Note".
Click to expand...


Thank you Chihiro,

That's helped me understand a bit better.

I really appreciate your help.

Regards.

Brian
 
Chihiro said:
Ah, that's because you have one continuous range reference of A$18:A$156.
Since it's array formula looking for SMALL. This will cause layout issues.

Starting from A43 in the "Invoice". Use below formula and enter as CSE.
=IFERROR(INDEX('Del Note'!A$130:A$151,SMALL(IF('Del Note'!$A$130:$A$151<>0,IF('Del Note'!$A$130:$A$151<>"",-129+ROW('Del Note'!$A$130:$A$151))),ROW(A1))),"")

For A42, You'd want to keep it direct reference to A41 in "Del Note".
Click to expand...

Hi Again,

I really seem to be struggling with this one I'm afraid.

I've attached my sheet again.

If I delete for example item 2 from the doors, it messes up the accessories again.

I have also noticed that now I've added some figures in. When I delete an accessory for example, the totals figure gets messed up.

Thanks again.

Regards

Brian
 

Attachments

  • template home rev 1.xlsx
    103.6 KB · Views: 0
Ok, so you need 2 separate formula.

One for Doors portion and another for Accessories.

You cannot do it in single formula.

For Doors portion use range of Row 10 to 33.
For Accessories portion use range of Row 34 to 56 (also need to adjust other parts of formula, see attached).

Total can be simplified as
=SUMPRODUCT(F17:F63,G17:G63)
 

Attachments

  • template home rev 1_S.xlsx
    104.2 KB · Views: 0
Chihiro said:
Ok, so you need 2 separate formula.

One for Doors portion and another for Accessories.

You cannot do it in single formula.

For Doors portion use range of Row 10 to 33.
For Accessories portion use range of Row 34 to 56 (also need to adjust other parts of formula, see attached).

Total can be simplified as
=SUMPRODUCT(F17:F63,G17:G63)
Click to expand...
Hi Chihiro,

Thank you for that.

Is there anyway I can avoid the blank rows that appear on the invoice. I

f for example I delete the qty. for doors; say the last 4 it shows blank rows on the invoice

Regards

Brian
 
I can't think of a way without introducing VBA to do it automatically.

Alternately you can add filter at the top and hide (Blanks).
 
You must log in or register to reply here.
Back
Top