Ask the EXCEL guy ...

[intention]

@rbroome

Thanks!
I have a simple problem.
I build tables in excel and would like to have them linked to display tables in my word documents. In principle this is easy, in practice it caused me all sorts of very strange errors. Several years ago I when I first did this, the word document would print with earlier versions of tables-sometimes with earlier versions of the word document. Tables would only partly update-it was a mess. At first I thought it was just that I created the tables on the mac and sent them to a pc for printing (very embarrassing when the PC is owned by one’s boss who doesn’t like Macs), but the problem would occur even when I built everything on the PC.

Anyway, the question is, does this work now? I still use Word and Excel X for the Mac.

Thanks

It works for me. In Excel, copy the area you want to link to. Go to Word, do a “Paste Special”, click the “Paste Link” button, select “Excel Worksheet Object”, click “OK”.

w.

PS - I’m using Excel 2004, not X, so it may be a bit different.

[intention]

@Johnny L.A.

How do I make a histogram? I have dates in column A, several columns of data, and two columns I want to graph along a timeline. First, I can’t get the dates to appear along the X-axis. (I get pseudo dates – they are formatted as dates, but they are not the dates in column A.) Second, the ‘dates’ are equally spaced instead of stretched out as one would expect when there are time intervals between updates.

Not sure I exactly understand. Dates in Excel are handled as the number of days and fractions of a day since a certain starting date.

Sounds like you need to use an XY chart. The dates listed along the bottom will be some interval (which you can adjust) from the start to the end of your data. The data will be along the x-axis of the chart on whatever day it appears on, which may not have a chart title.

But like I said, not sure if I understand.

w.

[intention]

@StGermain

How can I sort by colour, once I’ve colour-coded a spreadsheet? Also, is it possible to do “if-then” type functions? What I mean is “If column B is not Null, enter today’s date in Column A”

Thanks!

StG

Possible only by a macro, and then you’d have to be cute to do it. The difficulty is, which color comes first?

I think I’d handle it from the other end. I’d make a separate column which contained a number (1,2,3,4) to represent the colors I used. Then I’d use a conditional format for my data, to color the cells based on the number.

That way, I could just change the number and the whole row of data would change color, and I could sort on the column containing the color code to arrange the data.

Conditional formatting sets the format of a cell based on a condition. In this case the condition would be something like:

=if($B3=1,TRUE,FALSE)

The downside of this method is that you can only set 3 conditions. Look up “conditional formatting” in the Help file.

w.

[intention]

@rbroome

Thanks again. I have a question about your answer in another thread. I figured since my question is somewhat different from the OP in the other thread, I would move over to here. The other thread asked how to automatically create names for cells.
You said:
Excel has a menu item that automatically names cells in the manner you want, it’s Insert:Name:Create … select both the names and the cells, and use the menu item.

I can’t get it to work, I can’t scope out the steps.

I have a cell that I want to name. I have a source for that name. The source could be the first cell in the column containing the cell to be named, or the first cell in the row, or the name of the worksheet (in case that is possible), or the contents of another cell. How do I use the insert:name:create to accomplish the naming of the cell? I try to select the various combinations, but I get a box asking me to check “top column” left row etc, and always get an error.

There are several ways to create a name. One, using the menu item Insert:Name:Create, uses the cells at the left, or the top, or both, of the range selected to create the names. If you have

|A|B|C|
Row1|length|12||
Row2__|__width _|14|____|

and you select A1:B2 and use Insert:Name:Create, it will name cell B1 “length”, and cell B2 “width”. You can refer to those cells by name.

On the other hand, if you select A1:C2 and use Insert:Name:Create, it will name cells B1:C1 “length”, and cells B2:C2 “width”. Since the name refers to a range rather than a single cell, you cannot refer to those cells by name.

The other option is to select the cell (or the range) you want to name, and then use Insert:Name:Define…, and just type in the name you want.

w.

[intention]

@rbroome

Building a summary sheet.
I have a workbook that contains about thirty worksheets each providing months information. I have another workbook that selects individual cells from each of the thirty worksheets and provides a summary. So far, each. My problem is that the summary sheet shows historical information-last months info. That info is recorded in the individual sheets, so I simply have to manually go through the summary page and add another line with new references to display the current month. I use names so at least I don’t have to manually select and paste special each cell… But I have to create the names by hand in each individual sheet and type in (or do a global replace) the names in the summary sheet. Each step is slow and error prone.

Is there an easier way?

I want to just fill down in my summary sheet and have all the cells refer to the new locations in the individual sheets. That would be nice-but it doesn’t work of course.
Any help would be appreciated.

In addition to post #17 (thanks for the assistance, TomCat, the thread is more popular than I feared, and anyone’s assistance is welcome) …

I’m not exactly sure what you’re doing here, but there is a little known ability of Excel which might help. It’s kind of a 3d sum, which allows you to sum things that are in the same cell from worksheet “A” to worksheet “B”.

For example, suppose we have a workbook which has five sheets, named Sheet1, Sheet2, Sheet3, Sheet4, Sheet5. If we put the following formula on Sheet1

=SUM(Sheet2:Sheet5!A1)

it will sum all cells A1 on sheets Sheet2 through Sheet5. Cool, or what?

A couple of notes:

1. Which sheets are between Sheet1 and Sheet5 is determined by their position (in the tabs along the bottom of the workbook) and not the name of the sheets. In other words, if the tabs are in order

Sheet1, Sheet2, Sheet3, Sheet5, Sheet4

then the formula above will not include Sheet4 in the sum.

2. The names of the first and last sheets in the formula cannot contain spaces … go figure.

w.

[5_time_champ]

Let me just pop in here to say that I absolutely despise Micro$oft-- but Excel is awesome!

[intention]

I haven’t found much Excel can’t do. I use a whole host of functions and subs that I’ve written to do all kinds of off-the-wall stuff. Anyone who is a serious driver of Excel should take the time to learn Visual Basic, it’s an easy scripting language that will multiply your Excel power immensely.

w.

[Johnny_L.A]

@intention

Not sure I exactly understand. Dates in Excel are handled as the number of days and fractions of a day since a certain starting date.

Sounds like you need to use an XY chart. The dates listed along the bottom will be some interval (which you can adjust) from the start to the end of your data. The data will be along the x-axis of the chart on whatever day it appears on, which may not have a chart title.

But like I said, not sure if I understand.

w.

Let’s say the origin is 1 January 2006, and the data for that date is ‘5’. On 2 January I add data – this time ‘7’. The next ‘tic’ on the X-axis would be 2 January. Now I don’t enter any more data until 7 January, when I enter ‘3’. What I want is to have tic marks for the 3rd, 4th, 5th, 6th and 7th. The graph will start at Y-value of 5, then go up to 7. Then there will be a straight line connecting the 7 point to the 2 point on 7 January.

What I have now is each date is one tic mark from the next – regardless of how many intervening days there are. I thought that Excel should be ‘smart’ enough to say, ‘Oh. He’s defined this column as a date field. I’ll position the data points according to their dates, rather than assigning the dates to equidistant points on the X-axis.’

Let me see if I can draw a picture (making the origin zero for clarity)…

  |
8-
  |
7-          X
  |         .   .
6-        .       .
  |      .           .
5-     X               .
  |                          .
4-                             .
  |                                 .
3-                                    .
  |                                        .
2-                                           X
  |
1-
  |
 +----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|
         1    2    3    4    5    6     7    8    9    10   11  12  13  14  15  16  17   18   19

So I have a column of dates. But I only have three dates and three data in the other column. What I’d like to see is Excel interpolate the dates so that it looks like the graph above instead of this:

  |
8-
  |
7-          X
  |         .  . 
6-        .    .
  |       .      .
5-     X       .
  |               .
4-                .
  |                .
3-                 .
  |                 .
2-                 X
  |
1-
  |
 +----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|
         1    2    7    8    9    10   11  12  13  14  15  16  17   18   19

[Johnny_L.A]

Okay, the dots meant to represent a line joining the points didn’t come out the way they looked when I typed them. So just imagine the data points being connected by straight lines.

[intention]

@Johnny L.A.

Let’s say the origin is 1 January 2006, and the data for that date is ‘5’. On 2 January I add data – this time ‘7’. The next ‘tic’ on the X-axis would be 2 January. Now I don’t enter any more data until 7 January, when I enter ‘3’. What I want is to have tic marks for the 3rd, 4th, 5th, 6th and 7th. The graph will start at Y-value of 5, then go up to 7. Then there will be a straight line connecting the 7 point to the 2 point on 7 January.

What I have now is each date is one tic mark from the next – regardless of how many intervening days there are. I thought that Excel should be ‘smart’ enough to say, ‘Oh. He’s defined this column as a date field. I’ll position the data points according to their dates, rather than assigning the dates to equidistant points on the X-axis.’

Let me see if I can draw a picture (making the origin zero for clarity)…

  |
8-
  |
7-          X
  |         .   .
6-        .       .
  |      .           .
5-     X               .
  |                          .
4-                             .
  |                                 .
3-                                    .
  |                                        .
2-                                           X
  |
1-
  |
 +----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|
         1    2    3    4    5    6     7    8    9    10   11  12  13  14  15  16  17   18   19

So I have a column of dates. But I only have three dates and three data in the other column. What I’d like to see is Excel interpolate the dates so that it looks like the graph above instead of this:

  |
8-
  |
7-          X
  |         .  . 
6-        .    .
  |       .      .
5-     X       .
  |               .
4-                .
  |                .
3-                 .
  |                 .
2-                 X
  |
1-
  |
 +----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|----|
         1    2    7    8    9    10   11  12  13  14  15  16  17   18   19

Johnny, select the two columns containing the dates and the data. The dates should be in the first column, and the data in the second column. Then create the chart.

Make sure the chart type is XY, and from the sub-charts menu, select the one with the lines between the points.

I’ve posted a sample chart for you here to download and play with.

w.

[ParentalAdvisory]

How do I copy raw data into a merged cell without getting the errors:
Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway?

OK Cancel

<thinking Excel is clever enough to resolve it, I click OK.>

…and then:

Cannot change part of a merged cell. (Seriously, why give me the option if it cannot be done MS?)

I get this annoying error with anything I try to copy and paste into a merged cell.

[Johnny_L.A]

intention: Choosing the date column plus the two data columns I want to use, and then clicking on the line chart, I was able to create the chart. Only the chart is on the same page as the data. How do I make the chart on a different tab?

[rbroome]

@intention

It works for me. In Excel, copy the area you want to link to. Go to Word, do a “Paste Special”, click the “Paste Link” button, select “Excel Worksheet Object”, click “OK”.

w.

PS - I’m using Excel 2004, not X, so it may be a bit different.

I will try that. I just laborously pasted special individual cells into a word doc, and opening the word doc was very painful. Each cell had to confirm the virus macro protection, then the linked excel sheet was itself linked to another sheet, which required 3 OKs to open one cell in the word doc. Since I would have had hundreds, it wasn’t the way to go.

I tried turning off all checks and that helped, but I still have to have both spreadsheets open in advance in order to effectively open the word doc.

[rbroome]

@intention

There are several ways to create a name. One, using the menu item Insert:Name:Create, uses the cells at the left, or the top, or both, of the range selected to create the names. If you have

|A|B|C|
Row1|length|12||
Row2__|__width _|14|____|

and you select A1:B2 and use Insert:Name:Create, it will name cell B1 “length”, and cell B2 “width”. You can refer to those cells by name.

On the other hand, if you select A1:C2 and use Insert:Name:Create, it will name cells B1:C1 “length”, and cells B2:C2 “width”. Since the name refers to a range rather than a single cell, you cannot refer to those cells by name.

The other option is to select the cell (or the range) you want to name, and then use Insert:Name:Define…, and just type in the name you want.

w.

Thanks!
That worked as you describe and will help me.
A related question: if I want to use the text in Col A to create a label in Col C, is there a way to do that?

[rbroome]

@Tomcat

There is a free add-in out there that has a way to do this, I think. Anyways, it is a good thing to promote - free and cool. It is called ASAP Utilities and can probably do/fix all of the questions asked so far.

A work-around that I have done in a similar situation like you describe is to use a big =IF() formula in the column next to the info that gets updated. Let’s say the info you have is in column B. Last month is in B20, this month in B21, next month will be put in B22 but right now B22 is blank. In Column C I would put a big =IF formula to only show the latest value in column B, like so: in C21 “=IF(B22=”",B21,"")" which says “If cell B22 is empty, then show me B21, but if B22 has any value in it, then show me nothing” which if copied into the whole range of associated cells in Column C would effectively only show you the bottom/latest value (B21). Then in, say, cell D1 I would put “=MAX(C1:C9999)”. So column C has a formula that returns a bunch of empty values, except in the one cell that is next to the latest information (now in B21)…thus it will be the maximum value in the column. Now you can point to D1 from anywhere else and it will always show the latest value.

Long, involved, inelegant, but it works.

-Tcat

Thanks

I think I understand that. I have another way to display the last value in a column, which since it is one of the few tricks in Excel I figured out on my own I am inordinately proud of:

INDEX(F10:AZ10,COUNT(F10:AZ10))

This displays the last nonzero number in a row of numbers. Works equally well for columns. The only limitation is that it has to start with a non-blank number in the first cell (f10 in this case) and will work up to the first blank cell.

Probably obvious to all the excel mavins out there, but I like it.

[Heffalump_and_Roo]

intention,

Thank you for this thread. It got me interested in learning more about Excel. It’s a good thing.

[JCorre]

How can I have a pivot table count only distinct fields of a given variable?

[Joey_P]

@Tomcat

More info needed for the first, but you might want to try CTRL-1 while highlighting the cell in question to change it from text to number or general. The #value means there is something not quite right with the formula or calculation or reference or ???, not necessarilly that the value itself is wrong.

You can easily copy cells from other spreadsheets. Go to the cell you want, hit CTRL-C (for Copy), then go to the new file and find the cell where you want it and go Edit > Paste Special > click Past Link. It will enter a formula that will look like “=[Book1]Sheet1!$A$1” or something similar. Although normally automatic, ‘Update Remote References’ needs to be clicked under Tools > Options > Calculations tab. When you open the new file, it might ask to update the remote refs. Look in Help for Paste Link for more info and examples of uses (you can do whole regions of cells from one to the other, plus from Word or Access to Excel, too). It also works across networks and other computers. So if your colleague has a spreadsheet they update, and you need the latest calculation from them automatically, you can use this functionality.

-Tcat

I’m not sure what CTRL-1 does, but I’ll try it. About that though, it’s pretty strightfoward, I’ve got two cells, let’s say B1 and B2 both are set to text, B2 contains the function VALUE(B1). If B1 contains “43” so will B2, if it contains “102,3,4” B2 will give me an error. If I think of it, I’ll try putting plain old text (ie “HELLO WORLD”) and we’ll see what happens.

As for the other part…That would probably work, except, I’ve got over a thousand (and counting) files. One or two per day for the last several years. If I could start over (and I’m still debating changing over to this) I would have had one file for each month, with one tab for each day. That way I could have a final tab with totals and/or averages on it. So what I was hoping for is a method, like your’s, but that I could tell excel to find every excel file in a given directory and extract a specific cell out of it.

[intention]

@ParentalAdvisory

How do I copy raw data into a merged cell without getting the errors:
Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway?

OK Cancel

<thinking Excel is clever enough to resolve it, I click OK.>

…and then:

Cannot change part of a merged cell. (Seriously, why give me the option if it cannot be done MS?)

I get this annoying error with anything I try to copy and paste into a merged cell.

Excel treats say three merged cells as a single cell. Thus, the only thing that you can past into it is a single cell.

w.

[intention]

@Johnny L.A.

intention: Choosing the date column plus the two data columns I want to use, and then clicking on the line chart, I was able to create the chart. Only the chart is on the same page as the data. How do I make the chart on a different tab?

Right-click (control-click on the Mac) on the chart, and choose “Move”.

w.