Forum

Notifications
Clear all

Help improving advanced formulas

4 Posts
2 Users
0 Reactions
75 Views
(@jtucher)
Posts: 4
Active Member
Topic starter
 

Thanks in advance for any help or ideas for improving this workbook!  

All information is geared from the "unit types tab". 

My specific request for help is on the "PO" tabs and this formula which is used in the "bordered" section:  =INDEX($A$2:$A$78,MATCH(0,INDEX(COUNTIF($E$18:E20,$A$2:$A$78),0,0),0))&(IF(COUNTBLANK($A$2:$A$78),""))       

The Index is pulled in from the corresponding tab, in the order that the information is entered into each column on that tab (Column A first, then B, C, etc) .  Once the Index information is pulled into the  "PO"  "printable - bordered" section and combined to one listing/number - is there a way to re-order so that like items would be listed in the same area instead of in the order of the columns on the corresponding tab?    For example, I would like all drywall board to appear in the same area of the PO instead of having two or three board types appear 3/4 of the way down the page.  

I'm open to any suggestions to improve this workbook!  

Respectfully,  Julie

 
Posted : 15/09/2021 10:20 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello Julie,

No workbook attached. After selecting/adding the file to upload you need to click the Start Upload button.

Br,
Anders

 
Posted : 15/09/2021 3:39 pm
(@jtucher)
Posts: 4
Active Member
Topic starter
 

Thank you Mr. Sehlstedt!  I have attached the file.  

 
Posted : 16/09/2021 7:23 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello Julie,

Your file contains lots of data and I have not yet fully understood what is happening in the file. I take it that it is kind of an inventory list with purchase orders. With current layout all your lists/tables are static, if you can arrange the data so you can use Pivot Tables to build your "bordered" section you will get dynamic lists without using cumbersome INDEX & MATCH formulas.

In order to get the items of same category listed after each other then you either need to sort your data in the list accordingly or add such category data in your lists. Without Pivot Tables you then also need to add such subheaders in your bordered section. See attached for an example using Pivot Table.

Br,
Anders

 
Posted : 19/09/2021 5:04 am
Share: