Free Microsoft Excel 2013 Quick Reference

- Different column size in a table
- Creating a split in a page to allow for multiple column widths within one column
- Conditional summation of like entries in a table
- Using a drop down box to choose which table to lookup
- Merged Cells when copying a table from Word to Excel
- Format question when in a formula and nested VLOOKUP, OFFSET and HLOOKUP formula
- combining HLOOKUP AND VLOOKUP to find solutions in a matrix using 2 variables
- Analyzing rows and columns to get a price
- Creating a subtotal for a dynamically sized column
- Best practises for dealing with excel charts and tables in Word
- How can I use listbox selection to drive array name in a lookup function?
- Excel Macro for differnt font & add hyperlink to part of text in single table cell in word
- Excel Formula that references a table in a different worksheet
- VBA Sorting Columns by Row Values
- Taking a Worksheet and Repartitioning the Data
- Make a Sort button
- Choosing one column among many in different tables, using dropdown list
- Excel 2010 VBA table references & objects
- Two Parameter Lookup For Multiple Table Ranges
- Inserting Entire Blank Columns In Pre-existing Worksheets

Can I use different column size in upper and lower table?

I want my columns in upper 18 rows differnt than lower row's columns.

Is that possible?

Thansk, Pey

Thanks in advance for any advice to offer.

I would like to ask for your help with the following problem.

I have a table showing the quantity of products that were sold to stores within a particular week. The following data are shown in each column: Distribution centre (DC) ID, Item ID, Item description, Store ID, Day of delivery, Invoice Number, Quantity delivered - see example attached.

I want to sum up the quantities of identical items that were delivered to the same store from the same DC. In other words, the following conditions have to be met: (a) Distribution centre ID has to be identical, (b) Item ID has to be identical, (c) Store ID has to be identical. To give a specific example as per the attached file, instead of having 2 rows for 2 deliveries of 5 + 3 boxes of chocolate to store# 1, I want to have just one row showing that store# 1 had 8 boxes of chocolate delivered, a second row for that store would show that store #1 had 10 boxes of tea delivered to it. I would like the summed up quantity data point along with the Distribution centre ID, Item ID, Description, Store ID, Day of delivery, Invoice Number to be in a new tab, i.e. I want to reduce the table size and do not want the original rows to be in the new table.

Since my table goes all the way down to the last row 65536, I would need the formula/macro to be able to recognise identical DCs/items/stores and then sum up the quantities (there are about 1500 different item IDs). Can you think of a solution??

Many thanks in advance for your help!

Arina

I have several different sheets, each with a table showing the dimensions of different water tanks, where as each sheet defines the type of tank. I wanted it so that they pick from a drop down box the type of tank they want e.g. D Type, M Type. Then, on the next drop down box it will then pick a tank size e.g D10, D12, which will lookup up the dimensions and return them in another box.

I have managed to do the first box, but I'm struggling to do the next one as it doesn't allow for you to put a cell value in place of the location, e.g. say I want a table to use the size in sheet 'Type M'..I would put 'Type M'!B2:B9...but instead I want it to use a cell that picks the sheet, something like '(value shown in this sheet F10)'!B2:B9.

An alternative route I tried was that it would automatically post the table a couple of columns to the right on the same sheet that the drop down box would be on, when a table is chosen by drop down box....but then again I had a similar problem as above.

Any help will be appreciated.

I am using Excel 2008, and I am trying to copy a table from Word to Excel.

The table in Word contains 3 columns and about 3000 rows. In each of the row,

the 3rd column contains a big block of text, with some newline characters in

it.

I want to port this table to excel so that I can do sort, filter and take

counts based on the different values in the first 2 columns.

My problem is that, when I copy this table and paste it in excel, the 3rd

column value actually get pasted as multiple rows, while the corresponding

entries in col1 and col2 get pasted as merged cells. Now, I cannot sort or

filter because the merged cells are of uneven sizes. If I unmerge the cells,

I do not have 1-1 mapping between the entries in col1 and col3.

Is there a way, when I paste these values in excel, the entire block of text

in Col3 get pasted in a single cell. Or is there something that I can do in

Word to remove the newline characters.

Any help will be very much appreciated.

Thanks

Jay

As I work in a scientific discipline (nutritionals and pharmaceuticals) we manipulate a lot of complicated formulations and chemicals and specifications.

If I have a chemical formula such as the following:

Na2B4O7•10H2O (imagine that the 2, 4, 7, and 2 are all subscript characters, like a real chemical formula).

They are formatted as a typical chemical formula would appear, by selecting the 2, 4, 7, and 2 and changing the font style to subscript and then pressing enter.

Excel allows you to format text characters having different font styles and sizes through the data entry bar.

Say this formatted text string appears in cell A1. In another workbook, I wish to add this text cell, complete with it's format to the concatenation of another string.

for example, I would have a formula that says

="Potency test for boron in "&'Sheet1'!$A$1

This works fine, but when Excel concatenates these two strings, it removes all the subscripts and formatting and the resulting cell will display

Potency test for boron in Na2B4O7•10H2O

(all the subscripts appear as regular numbers now)

How would I be able to preserve the exact formatting when I am bring in a multi-character formatted text string when used in a formula?

As a side note, I work with some pretty eye-sore ugly complicated nested functions and I think one of the most powerful functions in Excel is the OFFSET function combined with VLOOKUP and HLOOKUP. They are great when you work with lots of products that have numerous specifications, and the specifications have specifications and test methods within themselves that you are trying to organize. I've mainly learned how to use these by trial and error, but once you have mastered them, you can truly generalize the manipulation of data. It may take several days to construct a very generalized spreadsheet, but once you do you can crunch out data manipulations and calculations in a flash. For example,

=VLOOKUP(item_num,OFFSET(data!$B$4,0,0,data!$A$4+1,data!$A$2+1),HLOOKUP(H9,OFFSET(data!$B$1,0,0,3,da ta!$A$2+1),2,FALSE),FALSE)

This is a lookup function that that uses multiple nested lookups and returns a particular parameter that is found from using a HLOOKUP function within from an OFFSET value.

A much easier way of doing this function would have simply been to say

=VLOOKUP(item_num,$A$5:$R$72,24,FALSE)

But the issue then becomes when you start inserting columns or adding new items that extend beyond row 72, you'd have to manually go back and change the formulas. For example if you inserted a column at column 8, then the old column 24 would now be column 25 and your simplified VLOOKUP function would return the incorrect value. OFFSET allows your table range to be continually changing, since within the OFFSET function you can define how big your table is (how many rows and columns). The data table size can be easily defined with some dummy counters and MAX functions (such as cells having formulas like =if($b5<>"",A4+1,"") and then having a cell calculating the =MAX($A:$A) where one of the OFFSET parameters links to this cell.

In the more general case, the reference for which column to return is in itself a LOOKUP, so it will find the header of that column wherever it is in your lookup table, even if columns are inserted or deleted.

Hope this helps anyone who is wondering how to use VLOOKUP, OFFSET, and HLOOKUP in a combined way.

Anyone who has any ideas about my formatting question would be greatly appreciated.

Regards,

Stanley

Salt Lake City, UT

I need to estimate the weight of reinforcing steel in concrete based on 7 different sizes of rebar and spacing of the bar between 1" and 24".

I have created a matrix(lookup table) with the bar spacing listed in decimal equivilents in feet on the top row.Inthe row below, I have numbered the columns from 1 to 24. the next row is blank. Along the left side of the matrix, I have listed the bar sizes 3 thru 10. Within the table I have listed all the bar weights/SF of concrete. IE if the slab has 1 mat of #5 rebar spaced 6" oc. If you look in row 7, under column 6, the weight of weight of the bar in that sf of concrete is found. In the part of the worksheet where the caculations are performed set up as follows:

Rebar Length Width SF Bar Size Spacing in feet Column Ref

Top mat 5 0.5 6 #N/A

Where B142 is lenght

C142 is width

D142 is =a142*B142

E142 is the bar size

F142 is the spacing of the bar in decimal equivilents of FT in this case .5

G142 is the is the formula =HLOOKUP(F142,reference,2,TRUE)

H 142 is the formula =VLOOKUP(E142,convert3,G142)

The returned value is 2.472, which is the value for #5 rebar 5" on center,not 6" OC desired

The range "reference" includes the cells at the top of the lookup table U2 thru AR3

The range contained in the "convert3" is cells T2 thru AR12.

I have tried tweaking the rows and column included in the ranges to no avail.

What am I doing wrong?

I am not a novice to excel although it might seem like it with the question I am about to ask. Any input would be much appreciated.

I am trying to see if there is a quick way to do the following:

I have a price grid table spanning about 6 columns x 5 rows :

Along the column headers are different widths

Along the Row headers different lengths

Each cell in this table has a cost for a product that is X width by Y height.

I would like to be able to enter values for width and height in cells C5 and D5 and have the value for those measurements for those sizes be returned in cell B6.

Also, you'll notice that in the price table that I've created I have inserted intervals. Is there a way to do this so that I only have to include the upper values in the cells and still get the correct pricing?

Thanks in advance,

Erik

I am stuck with an issue. I have a sheet where I have extracted specific online ad performance from a DB. This sheet updates automatically every day when I open it, and thus the amount of rows in this sheet updates.

For instance in week 1 I might only have 3 different ads = 3 rows. In week 2 I might do 6 ads and thus have 6 rows. I have a sheet for each of the last 3 weeks (3 sheets with each a weeks data worth). The columns in each sheet are AdName Number of Impressions, number of clicks, clickrate.

Now in another new sheet I want to make this information more edible (pull together the data and style it nicely for a customer). So I have created a nice looking sheet where I want to put the numbers from each sheet in.

Now I have a few challenges. Any help or a nice link to a description would be helpful:

1. I want my new sheet to have only the amount of rows where there is actually data from the other sheets. If there are a maximum of 5 rows in the other sheets (5 ads) then I want 5 rows in the new sheet.

2. I want to have a subtotal in the line just below the last line of the new sheet, so it totals the number of impressions, clicks and clickrates.

So my basic question is: How do I make sure I only get a specific number of rows in a table based on the number of rows in another table AND add a subtotal below those rows.

Any help would be appreciated.

Best Regards

Ulrik

I was wondering if there are any good workflows or best practises for dealing with large numbers of charts and tables in MS Word, presuming most of them derive data from Excel.

For example:

Linked charts, or pictures only? "In Line with text" or wrapped format? Independent charts or nested in tables? Enhanced metafile or simply a picture?

I am having a hard time achieving consistent throughout the whole document. E.g., I have some charts as "in line", and other as wrapped next to them. It would be fine, but when captions come into play, it turns out they are treated differently. In "wrapped" format, Word inserts another textbox, with significant amount of space between the caption and picture. With in "In line" caption is normal text. However, when you have inline, another set of difficulties come into play, e.g. it becomes a lot more difficult to simply move a chart to the next column. Nested tables seem to work best for now, but the trial and error, doing and redoing is simply too long...

Another big problem I have is keeping styles and sizes consistent. In excel its too easy to forget the margins of a printed page, but when it all gets copied, pasted, and resized into Word, captions and labels became of different sizes. I would presume discipline in excel could solve that. But then, there are other questions - e.g. keep data linked or not, if corrections are expected in the future? I tried making excel files with all charts and data for each Word, but it becomes tiresome with different row and column sizes for each table. The best workaround I found was to arrange tables diagonally in excel, or keep them in different worksheets. But too complicated with many charts. Soo you see, I am quite puzzled here

So, I have a dropdown that contains list elements name1, name2, name3 in cell a1 which are named ranges on separate tabs

another dropdown contains list elements size1, size2, size3 in cell a2

another dropdown contains list elements color1, color2, color3 in cell a3

I use match to find the column number for size...match(A2,A1,0) results in b2

I use vlookup to find the cost...vlookup(a3,a1,b2,false)

The trouble seems to be I can't use a1 in the array argument for match or vlookup, but I need to be able to look at different tables based on user input

==========================================================

INVESTMENTS: PAM US Pervasive to Oracle <link>

New SDLC Process: Yes

Impl Date: May 5, 2012 Budget: $101,000 (L1)

% Budget Spent: 16% % Duration Complete: 15%

Prj Sponsors: Exec: Jeff Spann, Proj: Maria Springer

IT Delivery: AM: Tony Perry PM: Dale Smith

Detailed Status: Link

======================================

Can anyone please help me to come out with some solution, how to do the same? Also please advise if there is any resource available who can help us if we face any issue to develop our current project? If anyone have any supporting document please send.

Thanks in advance. Sukanta

I have the following problem that I hope somebody can help me with.

I have a table, that I want to sort in terms of row values (making the existing columns corresponding with the new row order). Essentially, I want to be able to select a row name from a dropdown list and have it automatically sort the columns by the values in that row. Doing the dropdown list is straight forward enough, and I can make the code for sorting rows based on column values, but not the other way around , as shown below:

For example:

X Y Z

Age 74 53 62

Size 11 5 13

Becomes the following after sorting by age, for example: where Age is defined in a dropdown list cell.

Y Z X

Age 53 62 74

Size 5 13 11

I've tried using the following code:, where A1 in the case above, would be Age and the Range defines the table (including the row headings and column headings), but I just get a runtime error 1004. If I do the equivalent thing for sorting column values (change the data range to fit just the column and change to xlSortColumns/xlToptoBottom) it works for columns, but I can't get it to work for rows.

VB:Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then myKey = Range("A1") Range("G1:I4").Sort Key1:=myKey, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlSortRows, _ DataOption1:=xlSortNormal End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks a lot for the help. I've asked on a few other forums and I've not managed to get this sorted (!) so I hope this time will be different.

My Excel workbook features tables for each of these countries (30 separate sheets) with a lot of different places for information - not all of which are contained in the large table mentioned.

I want to take the relevant information from the large table and repartition it into each sheet for each country in the appropriate place.

All of the country's data sheets are the same format, but this large table has columns of different sizes and doesn't have data for every country.

I'm trying to write a macro that:

Finds the name of each element from the individual country sheetLooks for this element in the large tableFinds the corresponding set of data in the large table, i.e. "Primary production" (This is often not in the same place every time and can be of different sizes)Extract the name the value associated with the proper countryPlaces this value into the appropriate sheet for each countryMy problem is with the find function. It seems to find improper instances in the large table which messes up the index and causes values to get thrown in to cells where they should not be.

Is there a way to use Cells.Find to look only across a particular row (where the headers are)? How do I prevent it from jumping somewhere else in the table causing a bad reference index for VLOOKUP?

Here's what I have so far - sorry for any bad coding practices, I'm not a very good programmer:

VB:Thanks in advance for any help! I'm sorry I cannot attach the document - it's too large and Excel is determined not to let me save my macros into my documents.SearchTest() Dim i, j As Integer Dim cellRow, cellColumn As Integer Dim dataCount Dim source, currentCountry As String Dim EnergyValues, lastSearched As Range Dim countrySheet As Worksheet Sheets("Eurostat Data").Select Set lastSearched = Range("A8") 'This is just to line up the row for the search function lastSearched.Select For j = 23 To 34 source = Sheets("EU27_H").Range("A" & CStr(j)).Value 'Exclude instances for which there is no data If source = "HYDRO" Then Goto b: ElseIf source = "AMBIENT HEAT" Then Goto b: ElseIf source = "BIOMASS - Solid" Then Goto b: ElseIf source = "WASTE HEAT" Then Goto b: Else 'Finds the value for the energy resource from a given sheet in the master sheet 'Then finds the first instance of Primary production associated with that resource [B]Set lastSearched = Cells.Find(What:=source, After:=ActiveCell, LookIn:=xlValues,_ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,_ SearchFormat:=False)[/B] lastSearched.Activate [B]Cells.Find(What:="Primary production", After:=ActiveCell, _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlDown, MatchCase:=False,_ SearchFormat:=False).Activate[/B] cellColumn = ActiveCell.Column cellRow = ActiveCell.Row dataCount = Application.WorksheetFunction.CountIf(Range(Cells(cellRow, cellColumn),_ Cells(cellRow + 10000, cellColumn)), "Primary production") Set EnergyValues = Sheets("Eurostat Data").Range(Cells(cellRow, cellColumn + 1),_ Cells(cellRow + dataCount - 1, cellColumn + 2)) End If For i = 2 To 31 '2 is used here instead of 1 because EU 27+ is not included Set countrySheet = Sheets(Sheets("Names").Range("A" & CStr(i)).Value & "_H") currentCountry = countrySheet.Range("A1").Value If Application.WorksheetFunction.CountIf(EnergyValues, currentCountry) = 0 Then Goto a: countrySheet.Range("C" & CStr(j)) = Application.WorksheetFunction.VLookup_ (currentCountry, EnergyValues, 2, False) On Error Resume Next a: Next i b: Next j End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Im fairly new to excel/VBA - and im not to sure how to do the following

I want a table (of data) to be able to be sorted in asscending or desscending order when the heading of the appropriate coloumn is selected.

Example;Say i had different size timbre - and the different properties are length,weight,cost,strength. Can i make these titles into 'buttons', so when the user clicks a desired column (say strength) button, the data is sorted into Asscending or Desscending order.

Hope i made sense, and would appreciate any help

Cheers pix

Table1 contains the report output. Two columns of the table are labeled "Notes" and "Comments"

Table2 contains three columns "String," "Location," and "Output"

Where [String] is found within [Notes], the function should return [Output] in a column labeled [Part] on Table1.To do this, I have loaded Table2 into a two-dimensional variable array named strPartArray.I have established a loop for each row of Table2 (within the array) to be processed.My intention is to take the contents of "String" to search within "Location" (either Notes or Comments or some future column reference to Table1) until a match is found, at which time the loop exits and "Output" would be the result of the fuction.I am attempting to write this, and have written the variable array to key off the size of Table2 so that as more criteria is added, any number of Locations can be used to find specific strings. It is for this reason that I do not add "Notes" and "Comments" to the input fields of my function.

What I need is some method of determining, via VBA, what =[Table1[Notes][#This Row]] or =[Table1[@Notes]] would return. I realize I could use either a R1C1 reference or Offset reference to get this as well; however, I am also trying to avoid that as this is based from a report from a system whose column count varies enough to have me doing maintainence on this code too often for comfort. By utilizing the Tables' named Columns, I can limit maintainence time in the future.

I hope I've been thorough and thank you for any assistance.

What I have in cell K13 is a function to lookup a certain cell using a column reference and a row reference. The rows/columns represents a measurement and the numbers in the table represent a cost for that certain dimension of, what will be, Conservatory Roofs.

This works fine but there is many other tables which i need to look up as there are many different styles of roof, some costing less, some costing more. This is why i need help, the function in cell K13 only works for one table (currently Table 1) and i have had no success in trying to make a formula for more than one table. On the real spreadsheet they're is about 15 different tables so this needs to be rock solid. All the tables have the same dimensions (ie, all the tables are the same size). I was thinking perhaps an Indirect function will help, but i am un familiar with them.

Hope you can help

http://www.excelforum.com/showthread.php?t=606367

whats up everybody? i have to use two excel worksheets that someone else already created to make CSV (comma seperated value) files. these CSV files are to be dumped into a database. the problem is that the table has more fields than the spreadsheet does (the spreadsheet has 3 fields - network, mask, size - and the table has 10 fields). i can insert one entire column before (to the left of) network (for the ID field in the table) without a problem, but when i try to insert entire blank columns after size (to the right of the pre-existing columns) it only makes columns for the first 14 rows. i know this because when i save it as a CSV file there are only 6 extra commas for the first 14 rows. is there a way to insert an entire blank column for the entire spreadsheet (as in all the way to the bottom)? i dont want to have to manually type in all those commas. i am using excel 2002. any help is greatly appreciated! and please ask me to clarify or post some of the spreadsheet if you need me to.