Free Microsoft Excel 2013 Quick Reference

Different column size in a table Results

Hi,

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

Is it possible to create a page split, or section split, in order to create a new table with different column sizes on the same Excel page? (I do not want to create a page break.)

Thanks in advance for any advice to offer.

Hello all,

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'm trying to create a little coding on excel which will automatically display a paragraph based on the options you choose.

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.

Hi,
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

I have a seemingly simple format question that I cannot seem to get around.
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

This was a snap in Lotus. Unfortunately, the VLOOKUP and HLOOKUP formulas don't translate to Excel on conversion.

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?

Hello all,

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

Hi there,

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

Hi,

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

I'm building a pricing calculator that depends on selections from a list box, one of which I'd like to use in a lookup function (e.g. vlookup as the table_array argument) but I get #value error when I reference the cell containing the listbox. I've tried naming the range and naming a table as well as all lookup and reference functions to no avail. I'm pretty sure I've done this before! Scratching my head...I need this to work so that I can automatically select from dozens of tables (or arrays) of data based on user input.

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

I really got stuck with some problem when I am creating word document (with particular format) through excel macro. I have a button in excel. fter clicking on it a word document will open. In word document text and multiple table will be there. Number of row and column are different in different. I had able to create the same. Now the problem is I have to make different font size and color in same cell for different part of text in the cell. Also I have to create hyperlink in part of text in a cell (not whole cell contain) which will connect to a lotus notes database. For example I am sending a cell contain of my requirement (different line's Values will come from different cell of excel)

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

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

Ok, So I made a table (on worksheet 'Load Chart') that has Crane Sizes going down the left (A Column), and in Row 1 I have radius of the pick, and cells B2:AJ14 are all the weights of the picks that correspond with the size of the crane and the distance(radius) the boom extends. With that said, I need to make some sort of generator on another worksheet that will allow me to input a certain weight of a pick, and the radius that I am picking, and have it spit out the size crane I should use. Now keep in mind the sizes vary and if the table doesnt have an exact match it would have to choose a value higher, as I am guaranteed to pick a crane that can handle a certain load. SO it should reference the column (radius), pick the nearest weight (that is same or greater than) and read the corresponding crane size in column A that matches the row that the size is in. I know its much but I hope someone can help me.

Hi everyone,

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 Sub 

If 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.

I have a large table with various information about 30 (EU) countries.
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:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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.

Hey all!

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

I read the post on conditional drop down lists, and I think my solution is along that order, but I cannot completely figure how. Here is the problem: My spreadsheet has a number of different named tables, with variable numbers of columns (rows are the same number for each). The tables contain wind turbine power curves. There are different tables for different size classes (e.g. less than 5 kW, between 5 and 50 kW, and so on). I want to use the spreadsheet in such a way that a user can choose a power curve to use based on the size class of the turbine (what table to look in) and, within the size class, the name of the turbine (the table header). When the turbine is chosen from the correct table, I want the appropriate power curve to be written in the corresponding location. I thought I would have the drop down list point first at a list of the turbine classes (a list of tables) and then with indirect find the drop down list of all the turbines in that table. But then how would I get the actual values of the corresponding column put into my spreadsheet, to do the calculations I want?

I am attempting to create a custom function that works much as a reverse table lookup. I must find a string value within text field in an existing data table. I currently have 47 different strings in at least 2 possible locations.

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.

Hey, hope all you guys can help me out.

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

firstly i am cross-posting this topic so here is the URL to the same thread in a different forum.

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.