Can't build correct formulae to fill in an order form drawing from another tab

Morning

Once again, for the life of me I cannot figure out how to build an IF formula even when I think replacing the cells in the formulae and the column number is enough!

Attached is a mock-up of original live sheet i have duplicated and removed personal data.

"FIRM" tab has the data where the form in Sheet 1 will pull from. The Firm tab has formulae also but this works fine in the real sheet.

1) when I type in an "order number" into C4 i'm trying to have the other data filled in based on that cell (all the cells filled in blue) - the data for these are all in "FIRM" and i have put in what I am expecting. They are all related to the same "order number". i.e. on the same row.

I'm not sure if it possible for example to have a formula return in row 24, sheet 1, the first result related to the order number, and then the 2nd result in row 25 as two products have the same order number, which would be the case 'in real life'?

I can't even start building the correct formula to get either one of these cells populated... please help?

___

2) d24, d25 is based on f24, f25 where for example with product code 360-25, the "25" means 25kg. So if the code is 360-25 then d24 needs to multiply F24 by N30 (there is a key/legend table), same with codes with "16" and "10" in it...

I plan to drag the formula down too.

Obviously we don't have software that does this for us which is why I am here pleading for help!

Please ask any questions as looking back at this, it looks complex but broken down looks doable...

thank you in advance!

For the Product Code you could try this array formula, i.e. entered with CTRL+SHIFT+ENTER, in B24 and copy it down.

=IF(ROWS($B$24:B24)<=COUNTIF(FIRM!$G$5:$G$33, $C$4),INDEX(FIRM!$P$5:$P$33,AGGREGATE(15,3,((FIRM!$G$5:$G$33=$C$4)/(FIRM!$G$5:$G$33=$C$4)*ROW(FIRM!$G$5:$G$33))-ROW($G$5),ROWS($B$24:B24))),"")

Similarly for Product Description.

=IF(ROWS($B$24:B24)<=COUNTIF(FIRM!$G$5:$G$33, $C$4),INDEX(FIRM!$O$5:$O$33,AGGREGATE(15,3,((FIRM!$G$5:$G$33=$C$4)/(FIRM!$G$5:$G$33=$C$4)*ROW(FIRM!$G$5:$G$33))-ROW($G$5),ROWS($B$24:B24))),"")

Sheet with formula is attached herewith.
Hope this is what you require.

---

Shashank

Originally Posted by shank_mis

Sheet with formula is attached herewith.
Hope this is what you require.

---

Shashank

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

Originally Posted by shank_mis

Just drag the formula downwards....

What is the formula and where should it go? You need to give proper details in your posts, please, not just in the attachment.

Thank you for your help all

I now have a form which is filling itself in.

I just need now D24, 25 etc to return (F24, 25 etc) x M20, 21 or 22 depending on whether B24, 25, 26 etc has "-10", "-25", "-16"

The answers are already there its just getting the formular to read the "-16" bit and using the table to multiply

Personally for tasks like this that are essenially outputting a varying number of rows from a database to a pre-defined form as you have with sheet1 I use an Advanced Data Filter which is run by a simple two line VBA macro.

The requirement to allow you to do this is that the labels in B25:G25 MUST MATCH EXACTLY the column labels in your database (i.e. the Firm sheet). Where you have labels like Item Qty (which you presumably fill in after data has been extracted) then you should include that as a column header in your data eveb though the column will be blank.

For any task like this it's always useful to apply a dynamic range name to your data. In this case I've created a name callled 'DATA' and defined it as

Formula:

Please Login or Register  to view this content.

and that name is used in the formula below.

The stuff at the top of Sheet1 that's dependent on the order number can be populated with an INDEX(MATCH()) combination of formulae. For instance
With 'ORNo.' in A4 and 'Customer Ref' in A6 then the C6 formula would be

Formula:

Please Login or Register  to view this content.

The other fields at the top can be populated in a similar way.

I've made your Data column labels match your sheet1 labels, named B23:G23 'DataOut' and A4:A5 'Crit'.

Now when you select or enter an order number in C4 the folloiwng macro will run and populate your form.

Please Login or Register  to view this content.

Originally Posted by ZMAFC94

Thank you for this

This works well because it brings up the other product codes under the same order number

Just need a way to multiply f24, f25 etc by 98, 65 or 40 depending on whether b24, 25 etc has "-10", "-16" or "-25" as part of the text

To whom is this addressed?

Would you give an example using Product Code 360-25 in B24

Are you saying that the Code suffixes are ALWAYS either 10, 16 or 25 and these need to be translated to 98, 65 and 40 respectively and multiplied by F24?

i.e. 25 means 40, and the result required is 40*10 (in F24) = 400. Where does this result go, presumably G24?

Hi,

Try the attached.

I've changed the Worksheet_Change event procedure to:

Please Login or Register  to view this content.

Shucks

I released I meant column D not G

Now there is a bug, how can i go and fix this?
I changed the part of the code from G to D and it was working and now its not

I've attached the sheet again and now the total weight is gone; it should correspond to the values in column N in "firm" tab

Be patient! It's only been fifteen minutes since you posted before ...

Somehow you've deleted the column label from D23

It should be
Item Qty

i.e. exactly the same as AB4 on the Data sheet

Another option of course is to put the LOOKUP formula (suitably adjusted) now in column D of Sheet 1 in column AB of the Database 'Firm' Sheet.
Then you wouldn't need to add the formula to Sheet1 as part of the Sheet Change event macro so the macro could revert to just the original code I gave you and would finish with the .....Advanced Filter...blah blah bit of code.

Just change the penultimate line in the Worksheet Change event to

Please Login or Register  to view this content.

That looks like what I did! I just changed the letter...

The first time it worked, then i changed some values in the database to test it further e.g. adding more "orders"
Then i got this error message
bug.JPG

And then I see that this overwrote column D with formulae and i can't undo it.
B2.JPG

Am i not to make changes? Do i need to close and open again each time?

Thanks for coming back to me

EDIT: The overwriting of cells in D happen when i delete C4 to replace it with a new order number

I hadn't expected you to delete the order number making the cell blank before adding a new one. I'd expected someone just to overtype the value. Since no results were filtered the result was that the formula was added to D23.

Substitute the following WOrksheet change event macro

Please Login or Register  to view this content.

Hi Richard thanks for this, all working well now on the example sheet.

I tried copying this over to the live sheet by changing bits of the formula to relink the data.
Formulae works fine

Can't get the macro to work.
I tried naming all the ranges just like you did, still doesn't work

I copied the 'module' from the example sheet to the live sheet, still doesn't work.
All i get is:
c2.JPG

It highlights this line
c3.JPG

Which is this bit of the code

Please Login or Register  to view this content.

Hope this helps narrow down the issue

Hi,

It's likely that one of the three range names "DataOut", "Data" & "crit" have not been copied over correctly. Pkease check those first. And please confirm that the VBA code name for the sheet which contains the macro is still 'Sheet2'. To be clear I don't mean the sheet tab name that you see in Excel, I mean the Code name that you see in the Properties pane of the VBE when the sheet is selected in the VBA Project pane.

I really need to have the workbook you're using. Experience tells me that in cases like this where mine works and yours doesn't, then your version is obviously different somewhere, or you use it in a way that I hadn't thought of.

So please upload the workbook and tell me exactly what keys you are pressing etc.

Not sure what the attachments were since they don't appear.

However since you confirm that the range name 'data' is missing, that is why the macro fails.

In the original it is defined as

Please Login or Register  to view this content.

So unless you've changed the layout of the data on the 'Firm' sheet where the data is in columns F:G strting with the column labels on row 4, if you reinstate the name and the other two named ranges exist 'dataout' and 'crit' then the macro should work.

Hi Richard,

Thanks for sticking along for this solution, nearly there, victory in sight.

I'm not sure why the attachments didn't work. It just shows the error messages "1004" Method 'Range' of Object' _worksheet' failed

And the other one was the list of named ranges, of which doesn't include 'data'

Apologies but that code

Please Login or Register  to view this content.

I cannot find in the last sheet you attached here (if that was the original you was referring to) - where was it mean't to be?

And is this the code that the named range "data" is looking for?
If so i'll create a named range "data"

EDIT: I did some googling - i opened the name manager and put it in there and addressed it to sheet 1 (the first tab equivalent) and still gives me the same error, highlighting the same line

As you have identified. It was not VBA code it was a range name.

What's the name of your first sheet. If it's got a space in it, e.g. "First Sheet' then the reference in the OFFSET definition needs to me modified slightly.
Instead of

Formula:

Please Login or Register  to view this content.

you should define it as

Formula:

Please Login or Register  to view this content.

note the single inverted commas around the sheet name.
And if you've changed the top left cell of your data table from F4 then you need to adjust the definition accordingly.

Hi,

Note cells A4:A5 on the Order Request form. These carry the range name 'crit'. i.e they define the criteria to use for extracting records from the data to the 'dataout' range. Since you've changed G4 in the data sheet from 'ORNo.' to 'AML No' you'll need to change A4 on the request form tp 'AML No'

The filtering functionality requires that all column labels in the data, dataout and crit ranges match exactly. You may remember when I first created the workbook I had to change one of the labels in the data sheet to match the labels in row 23 of the request form. If you change A4 to 'AML No' you should be OK

Literally - its saying the extract range has missing or invalid field name

Now, the one that was working isn't working either

But here are the ranges
Attachment 658113

Criteria =

Please Login or Register  to view this content.

Extract =

Please Login or Register  to view this content.

AMLNo =

Please Login or Register  to view this content.

Crit =

Please Login or Register  to view this content.

Data =

Please Login or Register  to view this content.

Data out =

Please Login or Register  to view this content.

Showing formula of sheet 2 ("sheet 1")
show formula.JPG

AML in sheet 1
aml.JPG

What's in A4 of the order request form?

So what's in G4 of the Data sheet?

The "AML No" that you indicated in post #32 or the "AMLNO" (without a space) that you show in A4 of the request form.

The two need to be spelled EXACTLY the same.

Just to confirm. C4 on the request form previously had the NAME "OrdNo". Presumably you have changed this to have the NAME "AMLNo"?

Clearly there is something wrong somewhere with the Names of cells/ranges and/or the labels for the fields.

Is it not possible to send me a copy of the workbook you have? Delete all the data if its sensitive, I'll then add a couple of lines of dummy data to test it. This would be the easiest way of resolving this.

Hi,

As I mentioned earlier the column labels in the 'Data' range and the 'DataOut' range MUST be spelled EXACTLY the same.

Currently you have the following, Tilbury sheet row 4 first, then Sheet1 row 23

P4 - Code : B23 - Product Code
O4 - Product : C23 - Product Description
AB4 - blank : D23 - Qty
AC4 - blank : E23 - Batch (if reqd.)
M4 - Pallets : F23 -Total Pallets
N4 - Kgs : G23 - Total Weight Kg


Remember early on in post 11 I mentioned that the Filtering functionality that is used here requires that the labels in an ouput range MUST exist and be spelled EXACTLY the same as the labels in the Data range

If you make these labels agree then the macro will be able to extract them.

Although AB4 & AC4 don't currently exist in your data, since these are items on your request form (where in column F you manually enter a number and column D is populated with a formula) the column labels MUST nevertheless exist in your Data.