#1
|
|||
|
|||
Help Reqauired for Excel File of BOQ
Hello Everyone
Hope u all fine. I have problem in my excel file. I make Bill of quantities BOQ. For Different areas.In all those areas the items of boq remains the same but the quantity change according to the total amount.and also the number of item change in each boq.It look like a book type .I create a searchable item list but its not working porpperly and formating is not look official type.I have to make the boq 2 times with different formating.second time i also attach the sheet ms word.i want to integrate into excel sheet with same formatting i attached in word file.so when i make 1 time it can automatically make on the excel differnt sheet. |
#2
|
|||
|
|||
what are your source table?
make 1 time it can automatically make on the excel differnt sheet. what sheets are the expected different sheets? |
#3
|
|||
|
|||
source file is in the excel file sheet in usama para 1 rar.
it should be in the same excel with a new spreadsheet |
#4
|
|||
|
|||
Are you creating a new sheet for every BOQ or what?
You can consider different design instead: As I understood, you have a separate table where all items are registered, and from where you get all item info. In this table, you need a column, which identifies the item (e.g.Par/article number). Define the datarange of this column as dynamic Named Range (a Named range which updates automatically whenever you add items to list or delete tem from there); Then you need another sheet with table, where all your areas are registered. This table has at least one column where area names are entered. And the datarange of column with area names must also defined as dynamic Named Range; As next, you need a sheet with table, where you register your BOQ SetUps for all your ranges. This table contains 2 columns, one for range names, and another for items used in range determined in 1st column. in both columns, you select (working) range or item using Data Validation Lists, which are defined using Named Ranges, you created for tables on Ranges and Items sheets, as source; As next step, on separate sheet (at best as leftmost one), where you register all BOQ's for all ranges over all time you keep the data - a row for every item in particular BOQ. The easiest way is to have at left of table same columns as in BOQ Setup table. Whenever you have to register a BOQ, you set the filter for BOQ Setup table to range, and copy filtered entries to BOQ registry table. After that you fill next data entry columns there (like dates, and quantities). At right of last data entry column, you can have any number of calculated columns, which return any values from range or items tables, you want there to be displayed; Now you have one single and big table instead a lot of small ones. One way to handle this, is to use simply a filter on this big table, to get only rows for certain range at certain date. Another way is to create any number of report sheets of different designs, where at top of sheet you set report conditions (like range name, and report scope like date, or month, or year, or whatever), and all nessessary data are read from BOQ registry sheet, and from ranges and items registry sheets in format, and are displayed in report sheet in format you designed it. The main difference with design where every BOQ has it's own sheet is, that after you designed it once, you can work with it for years. And whenever you need a new report, so long as data needed for it are present in some of tables, all you need is to design a new report sheet. |
#5
|
|||
|
|||
yes but i create a new excel file for every new boq.
well sir i m a new bee in excel and i create this by watching tutorials .do u have have any videos or u can make adjustments in this file ? |
#6
|
|||
|
|||
Quote:
Like this? Split one master sheet to multiple workbooks with child sheet? |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to update source excel file with changes made to insserted excel file in OneNote | winwell | OneNote | 1 | 11-03-2015 12:19 PM |
Macro to highlight repeated words in word file and extract into excel file | aabri | Word VBA | 1 | 06-14-2015 07:20 AM |
sending data from UserForm of existing excel file to a new excel file | saltlakebuffalo | Excel Programming | 0 | 02-13-2014 10:55 PM |
how to copy some information from one excel file to another excel file | tomlam | Excel Programming | 4 | 10-01-2013 03:06 PM |
excel cannot open the file .xlsx because the file format or file extension is not val | teddysika | Excel | 1 | 11-22-2012 06:06 AM |