No more automatic reference formatting? (excel 2002)

Topic

New Reply

Excel used to be able to figure out that when you had a formula for a caluculation and you filled down that you wanted to reference the cell at the very top and the cell to the imediate side. Then you could go sideways as weill and fill out a table. It does’t seem to work like that anymore and it seems I have to tell it what to do, which a real pain in the butt. I looked at several threads but it seems everyone is using the clunky f4 key or VBA to do it. I don’t want to do that, so am I missing some way to turn on the old way to do it automatically? i can’t find such a swithc anywhere and the Help is useless. It seems that MS apps are always trying to do something for you but in this case they seem to have taken away a very valuable feature! Anyway, I’d appreciate being clued in if the feature is there and I am missing it.

Reply | Quote

Replies

Unless you use DATA | TABLES (or macro’s), there is no other automatic way of creating tables that reference “variables” on the top and side of the table. Are you aware of data tables? To use it, specify a formula in a cell, and then specify any variables across the same row and column of the formula. Then go the Data | Table and specify a cell that can represent the row variables, and a cell that can represent the column variables, (these are known as Input Cells). Choose OK, and the table is created automatically… without you having to use F4 to determine the absolute/relative references!

Reply | Quote

Is Microsoft really so stupid they would make it HARDER than it used to be? Come on, this used to be EASY to do. I used to make tables like this in four easy steps: series fill one row across, series fill one column down. Input formula in top left cell and then fill down, formula fill across. Voila, a nice table. Now I have to go through either a complicated F4 rigamarole, or the complicated data table to do something that used to be EASY. An to boot I have to custom fill EACH COLUMN?!?!?! That is completley nuts!!! Please folks, tell me that I’m missing something here, Even MS in it’s infinite mediocrity, wouldn’t get rid of something THAT easy!!

Reply | Quote

Ranting won’t help you. As far as I know, the basic capabilities of Excel have not changed from several versions ago. What you describe is still possible in Excel 2002. Can you describe precisely where you have trouble making it work?

Reply | Quote

I am simply trying to fill a table off a multiplication formula. One row of numbers across the top. one row down the side. Put a simple formula multiplying the top row against the column. Very common use of excel. I used to do this all the time and Excel automatically changed the references to accomodate the change in position of the formula. Now it keeps it all relative and so the values are all off. I don’t want to use the F4 option because is very a very clunky way to do it for multiple columns and rows. The data table is also a clunky way to do it. I’m just asking if there is some setting somewhere that will turn on the old automatic referencing that used to make Excel so easy to use. By the way, ranting against MS is the national sport!

Reply | Quote

I have used Excel since version 1.00 for the Macintosh in 1985, and the behavior of filling across and down has always been more or less the same. References are relative unless you change them explicitly.

Reply | Quote

Something like:

=$A2*B$1

will create a mult table assuning col A has 1 value and row 1 has the other. This will determine the product of the intersection…

Steve

Reply | Quote

It works for me in XL2K. If I put this formula in B2:

=$A2*B$1

and copy it across to J2, then copy B2:J2 down to B10:J10, I get exactly what you describe. What do you get.

Reply | Quote

It sounds like you just have to use relative and absolute cell referencing in your formulas.

To reference and lock a row add a dollar sign before the row reference. If you want to lock on a column add a dollar sign before the column. To lock on both, add dollar signs before both. No dollar signs and it is completely relative.

If you are selecting cells while creating a formula, will toggle thru the 4 types of formulas.

If you give a specific question we could explain in more detail.

Steve

Reply | Quote

OK. I attached the exact example I am working on, but here is an even simpler example: One cell at top with a number in it. The one column to the left and down one row, a column of numbers, say 10 rows worth. Put in a formula in the first intersecting cell. Fill down. Excel used to figure out that you wanted to multiply the top number by each of the column numbers and so Excell AUTOMATICALLY adjusted the relative/absolute references to make that happen. I never had to change the relative/absolute references when doing this and I have done this hundreds of times. Now, I have not used Excel much in the last few years, so I was a bit perplexed when I had to do this very exercise this week and it wouldn’t work. Instead it keeps the relative value all the way through and so multiplies the immediate above cell against the immediate left cell, so becomes exponential. Now, I could use the F4 route, but is it far more trouble than the old automatic way of doing it. So, I want to to do it without the F4 nonsense. Like I said, this used to be a very simple operation.

BTW< cycling the F4 key on the when the entire formula is selected will not bring up the correct relative/absolute reference combintation to fill in the table. Only by cycling on each half of the formula can you make it show correctly. Typing it is faster. But having Excel do it for you is even faster. If excel has really dropped this feature I would consider that a major defect of the application.

Reply | Quote

Your formula is doing exactly what you are telling it to do. You have both the column and row references in both cell references entered as relative. Therefore, if you copy that formula, both the column and the row are going to be relative references and it will not do what you want. If you change the formula to:

=$B1*A$2

then it will do what you want. This is the way Excel has worked since I first used version 1.

Reply | Quote

Yes, you’re right, the example I sent does not have the correct formula for the entire table to fill in correctly. I know how to type in the correct formula to get what I want, but that is far slower than the old way Excel used to work when it would automatically figure out what you wanted to do. I guess they took that feature out (I admit that sometimes I curse MS apps for trying to do something for me that I don’t want to do, so maybe this was a case of people complaining about that feature). So I guess the autofill feature is gone and Excel is less user freindly than it used to be. Thanks for nothing Bill!

Reply | Quote

As far as I know, Excel has NEVER worked the way you describe, and it would be incorrect if it did. If it did work the way you describe, then there would be no way to get the result you are getting. If I wanted the result you are getting, then there would be no way for me to enter a formula that would produce that result, and two ways to enter a formula to get the result that you want.

Reply | Quote

“As far as I know, Excel has NEVER worked the way you describe,”

sorry, I disagree, I used to do this as a common exercise.

Anyway, I’m going to leave this now. No use hashing over something that is obviously not going to change anything.

Reply | Quote

Well, as I said, if it ever did work that way, it would be a bug because of what I described.

Reply | Quote