Formulas & sorting

Where can I find references to BRG, BRG6, BRG7 and the other one on proposed loading list sheet? if they're not their, how can I (or Excel) tell what information has to go onto which sheet?

The data in proposed loading list is taken from SAP, I have just copied it from SAP and pasted it in proposed loading list.

And I have updated the formulas in other sheets BRG, BRG6, BRG7, Transshipment and 5600(DIS) to get the data from proposed loading list (kindly check the formulas please if its ok).

My concern here is the data are being updated in the above mentioned sheets from proposed loading list sheet, but the thing is they are not sorted.

if the data for BRG is mentioned in row 100 in proposed loading list sheet it comes on the same row in BRG sheet where I need (if possible) to have the data in the first row (after the heading) of the BRG sheet.

Hope I was able to clarify what I need. sorry and thanks.

If I understand correctly sheet BRG column C, should now be displaying the container numbers in the manner you desire.
The array entered formula* used to populate column C is:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

I have done this only for BRG. Please check it and tell me if it's what you wnated. Most of the formulae are array formulae.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Thank you so much. Yes this is what I wanted, but I tried to use the same formula for the next column in BRG sheet but I'm not getting any output. Yes I used CTRL+SHIFT+ENTER.

I tried to get the batch numbers for those containers in BRG sheet from Proposed loading list sheet but the formula didn't work for me. I just changed the column in the formula for batch number which is column E in Proposed loading list sheet.

Kindly advise me please.

Who are you talking to?

You have me confused. I completed ALL of the columns for the BRG sheet. Whay were you copying columns on that sheet??

However, one thing is certain. I cannot tell what you have done incorrectly if you do not post the sheet with the problem!!

I am sorry, my friend.... but you are WRONG. That is NOT my sheet. Go back to Post 5. Open it. You will see that ALL the columns on BRG have been completed. What you have attached is based on JeteMc's sheet from Post 4. He only did the one column.

I do not use this type of construction in my formulae: ...A$7:A$339)-6)),ROW(A1))).

So, please check the attachment at sheet 5.

You selected the whole column and copied it across. You can't do that. It doesn't work. Instead, select the whole ROW and copy/paste it. Modify as needed. set the array and copy down. It works perfectly.

I am away for the night. Look back in the UK morning.

To add reputation, click on the little star labelled Add Reputation at the bottom-left of this Post.

Once again, you are WRONG, I'm afraid. You did NOT use my formulae. You simply used your own ones again. There are NONE of my formulae on the transshipment sheet; so I am not surprised that it did not work!!

You're welcome and thanks for the Rep.

The problem was caused by your use of IF('Proposed Loading List'!$J$7:$J$1454<>10 All the blank rows in column J were included in that. So, adding another IF term: IF('Proposed Loading List'!$J$7:$J$1454<>10,IF('Proposed Loading List'!$J$7:$J$1454<>"" fixed it.

You had also changed my formula in BRG column D, which was working perfectly well, to another one which was not working at all (I have no idea why you felt it necessary to change it!!). I have restored it to my working formula.