albertof91
New Member
- Joined
- Jul 12, 2022
- Messages
- 5
- Office Version
- 2019
- Platform
- Windows
Hello everyone!
I have an excel file with >300 sheets which are purchasing orders from different materials, so, each sheet has different numbers of rows under certain specific titles i.e. "Quantity", "Description" or "Price".
I think it's important to mention that this purchasing order's template, includes in the first rows the name of the company, address, etc, so I would have to skip those rows until getting to the one with the titles mentioned before.
I already have a single worksheet which is getting the Suppliers name, Date and Total by using a simple VLOOKUP together with INDIRECT to obtain the sheet name from column B automatically and just pull the formula down:
Now as I have explained in the beginning, I need to obtain the details of each purchasing order to create one data base consolidating all purchases, but the problem I'm facing is that each of the sheets has different number of rows and purchased materials, which makes it hard to obtain by a VLOOKUP or FIND formula. Also considering I have to "skip" all the rows above the required info.
I want to have a single worksheet that pulls together the required rows from all of these worksheets, and that automatically update when there is any change. Ideally I would like to maintain the order of the rows being pulled in, i.e. the rows from Sheet1 would be followed by the rows from Sheet 2, etc.
I was trying to replicate some formulas from another post which was using INDEX, MATCH, SUBTOTAL and OFFSET which obtained the the number of rows in the multiple sheets and then get the info, but since in my case the information is not in cell A1, I'm not sure on how to proceed.
www.mrexcel.com
Does anyone have any recommendation on how to proceed?
I would like to keep it within excel formulas and avoid MACROS or PowerQuery.
Thank you!
I have an excel file with >300 sheets which are purchasing orders from different materials, so, each sheet has different numbers of rows under certain specific titles i.e. "Quantity", "Description" or "Price".
I think it's important to mention that this purchasing order's template, includes in the first rows the name of the company, address, etc, so I would have to skip those rows until getting to the one with the titles mentioned before.
I already have a single worksheet which is getting the Suppliers name, Date and Total by using a simple VLOOKUP together with INDIRECT to obtain the sheet name from column B automatically and just pull the formula down:
Now as I have explained in the beginning, I need to obtain the details of each purchasing order to create one data base consolidating all purchases, but the problem I'm facing is that each of the sheets has different number of rows and purchased materials, which makes it hard to obtain by a VLOOKUP or FIND formula. Also considering I have to "skip" all the rows above the required info.
I want to have a single worksheet that pulls together the required rows from all of these worksheets, and that automatically update when there is any change. Ideally I would like to maintain the order of the rows being pulled in, i.e. the rows from Sheet1 would be followed by the rows from Sheet 2, etc.
I was trying to replicate some formulas from another post which was using INDEX, MATCH, SUBTOTAL and OFFSET which obtained the the number of rows in the multiple sheets and then get the info, but since in my case the information is not in cell A1, I'm not sure on how to proceed.
Extract multiple rows from another sheet based on cell value
Can someone please assist me with a vba code for the following: I want to assign a code to the load button in sheet2 to extract rows from "Sheet1" into "Sheet 2" based on the the date in cell Sheet2!C2. Example:(image) Unsuccessful in modifying the below code to suit my requirement: The...
Does anyone have any recommendation on how to proceed?
I would like to keep it within excel formulas and avoid MACROS or PowerQuery.
Thank you!
