Returning Data to Master Worksheet

[Aide]

Hi All

Some help please In the attached I have two worksheets where I'd like to return data to the tube master from the ForkBom1.

I would like to return the exact match from L9 (Length) and L8 (ID) plus 3 below the ID number, so if 64 is the exact number it needs to also return 63, 62, 61.

The finished workbook will contain about 850 rows which is why I'd like a formula.

Thanks

[avk]

Input real data. (In ID OD Lenght Wall Thickness)
L9 (Length) and L8 (ID) : Those cell is part number. Where as i think that is column "I"
Even part number 7300000 not found in sheet name "FORKBOM1"
You say if 64 is the exact number then needs to also return 63,62,61 explain about criteria.

[Aide]

Hi Avk

Thanks for the reply. I've laid out the worksheet with how the returned data should look like in the Tube Master using the required formula.

Criteria is

Length - Exact match (150)
ID - Exact match (64) and 3 number lower (63,62,61)

However, the formula needs to work with all numbers.

Thanks

[Aide]

Hi All

I've been trying to find a suitable formula all morning but unfortunately my excel expertise doesn't expand to this.....

In the last file there is just enough data to demonstrate my needs, I've mocked the Master Tube worksheet manually to give the desired results.

Any help is deeply appreciated.

Thanks
Aide

[shukla.ankur281190]

Try

L3

Please Login or Register  to view this content.

Drag towards the cell
...confirmed 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.

[FlameRetired]

Similar to Ankur's formula. I just tried to emulate the sort order you indicated. Also array entered.

Please Login or Register  to view this content.

[shukla.ankur281190]

Similar to Ankur's formula. I just tried to emulate the sort order you indicated. Also array entered.[/FORMULA]

Oho I forget to make them in short order thanks for my notice too,

[Aide]

Hi FlameRetained

Thanks for the formula, I've tried to copy and past it, but I can't get it to work. Would you be so kind to install the formula into Master Tube - Sample I've attached please.

Sorry to be a pain.

Thanks

[FlameRetired]

No problem. Installed and attached. By the way did you array enter it?

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[Aide]

Thanks so much for your help...........am assuming if I need to change so its an exact number and 3 above I change the formula to include the plus symbol instead of the minus?

Also when using the formula it leaves #NUM! when it returns less than in the spread sheet is there away that the cells can be blank?

Thanks

[FlameRetired]

Thanks so much for your help...........am assuming if I need to change so its an exact number and 3 above I change the formula to include the plus symbol instead of the minus?

Also when using the formula it leaves #NUM! when it returns less than in the spread sheet is there away that the cells can be blank?

Thanks

Yes just change that number to meet the new condition(s).

The #NUM! error. Yes. That was an oversight on my part. My apologies. To cover the #NUM! error wrap an IFERROR function around the formula like this.

Please Login or Register  to view this content.

[Aide]

Hi FlameRetired

I've been using the spread sheet today with some colleagues and one of them said is it possible to filter the data to include the ID, so it works if you input one or all of either ID, OD and length?

Thanks
Aide

[FlameRetired]

Yes.

Array enter again.

Please Login or Register  to view this content.

The blue indicates what the change is.