Using tables to create dynamic data validation lists
One of the useful features introduced in Excel 2007 was Tables. The most obvious benefit of these new Tables is that they auto expand. Once you have created a table and you input data in the row immediately below the last row of the table, the table automatically “grows” to include it. The same happens if you input data in the column immediately to the right of the table. It is this auto expanding property of tables that we want to exploit, and the first application I want to look at is to create a dynamic data validation list. As their...
read moreEmbedding a spreadsheet in a web page
Microsoft recently introduced a neat feature that can easily achieve this. In a nutshell you need to upload the file to Skydrive and use the facility provided there to generate HTML code that needs to be inserted in your blog or website. Here are the details: Step 1: Create the file in Excel Save the workbook somewhere in a local or network folder. There are certain limitations that apply to the kind of workbook you can embed, for example it can not contain macros or data validation. Step 2: Create a Skydrive account If you don’t already...
read moreSumming across multiple sheets
Often it is necessary to aggregate (sum, average, count etc) values across multiple sheets. For example you may have sales data that is captured in a separate sheet for each region or month or product, and so forth. Let’s say you have monthly sales data in a table as below, with a separate sheet for each location: If your sales data is in a table with the same layout in each of the sheets, then a simple formula can be used to calculate the total for any cell in the table. For the sample above: =SUM(‘Boston:Tokyo’!C3). This...
read moreExpert Excel Solutions
Excel Developers provide expertise in spreadsheet development, automation, modelling, optimization and maintenance. Giving personal attention to each client means we provide the solution you need, and our fixed fee approach gives you the assurance of knowing exactly how much it will cost.
read more
