Name of feature: automatically fill adjacent formulas?

[anationalacrobat]

Excel 2007. I seem to remember there was a feature in an earlier version of Excel that would let Excel intuit that automatic formulas should be copied down if they were adjacent to manually entered cells on a sheet. So for example, let's assume Column A is titled Length, B is Width, and C is is =A1*B1. Currently row 1 is the only row with values on the sheet. I type in 3 and 3 in columns A and B, hit enter, and then the formula from C1 is intuitively copied down in to C2.

Am I remembering this correctly? The sheet I'm working on is more complicated, obviously, and I'd like to avoid the whole if column this is blank, print blank, else complicated formula thing. This workbook will ultimately be used by non-technical people and and I don't want them to have to think about remembering to copy down the formula another few hundred lines if they so require it.

I have a sneaking suspicion I might be thinking of the feature that works like this for external data ranges and that there's nothing like this for regular situations.

 

[GlennUK]

If I remember rightly there has to be a certain number of "seed" rows before this behaviour begins, and that might be 3,4, or 5, rows of data and formulas ... I won't be able to check until Tuesday, the next time I'm near a machine with 2007 on it.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AnotherHiggins]

In 2007:

Office Button > Excel Options > Advanced > Extend data range formats and formulas.

But, as Glenn said, you need to have a few seed rows. I'm pretty sure that in 2003 it was 4 rows.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[anationalacrobat]

Thanks for the feedback! The button is turned on but I'm simply not seeing the behavior. I guess I'll have to type in "extend data range troubleshooting.

 

[Lilliabeth]

You might find it beneficial to have a look at 'Format as Table' - on Home tab.

--Lilliabeth

 

[anationalacrobat]

Turned on the table formatting. Ginchy keen-looking but it's still not updating.

I remember running into a problem like this when doing data dumps from a database -- I needed to have a column to the left of the export range and the formula updates only worked to the right of the export, not the left. But the formula I need to have carried down is on the right. Hmm. Do I need to convert to a range to make the expansion work properly?

 

[anationalacrobat]

Let me take that back -- I converted to range, it didn't work, hit undo and now it's working. *rolls eyes* I need to figure out how I made it work.

Ok, so I see I need to make it a dynamic range and then I can use it as the source for a pivottable that will expand and contract as the data moves. Let's see if that cooperates now. Fun fun fun.

 

[anationalacrobat]

This is so maddeningly inconsistent. I have two vlookup formulas that are supposed to copy. One does, the other does not. I created a little test set without even formatting it as a table and it will not automatically put the formula in the last cell until the cell immediately before it is filled. On the big formula sheet, it copies the one vlookup cell immediately after I type data into the first cell, even though that's not the particular one it's using as the index for the lookup.

I think Excel shall drive me mad today. Time to crank the Primus.

 

[AnotherHiggins]

Just checking - there aren't any empty columns in your data set, are there?


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[anationalacrobat]

No, no empty columns. An excellent question seeing as Excel is looking three rows behind to pick patterns. My little test block is working like a champ so I'm scratching my head trying to see what I'm doing different with the big block o' data.

 

[AnotherHiggins]

How is the big block being populated?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[anationalacrobat]

I'm tracking event attendees with this workbook. Worksheet 1 is attendee names and contact info. Sheet 2 is the lookups for events, communities, etc -- these values will be tracked in a chart so I want to make sure that there's no chance anyone can type these in incorrectly. So I make the lookup range and use data validation to make sure people only type what's in them. Worksheet 3, the one I'm on now, is the sheet that pairs the name with the event and the date they attended. I use vlookups to drag in a few other items from the contact page since the reporting all depends on that.

Big block has columns A through E:

Column A = date, typed in by hand
Column B = attendee name, that's a data validation lookup based on sheet 1.
Column C = Event, data validation from sheet 2, selects event from dropdown list.
Column D = vlookup of where the person lives. Once the person is entered in column B, Column D should auto-populate.
Column E = vlookup of what the person's age range is, should autopopulate just like D.

When I start a new row, Column E has the formula autocopied and gives me an #NA until I put the person in column B. D remains blank and I have to copy the formula down manually.

I should also note that in the success column, the data validation formatting from column B does copy down successfully as well. I enter the date, dab over and now I have a dropdown to work with whereas beforehand the column was completely empty, no data or validation or anything.

Am I making sense hopefully?

 

[lionelhill]

Isn't this a "list" (mark the area containing data and calculations, use Data-List-Create list)?

 

[anationalacrobat]

Nope, no longer called lists, they're tables now. So many things are unlike what I was used to in 2007. I still feel traumatized.

<i>Symptoms
You cannot find the Excel List feature on the Ribbon, which is a component of the Microsoft Office Fluent user interface.

Cause
The Excel List feature still exists, but it has been renamed. Excel lists are now called Excel tables.
</i>

What's highly annoying is you can't share a workbook that has a table in it, it must be converted to a range. Completely ruins the utility of sharing the workbooks amongst multiple simultaneous users which I thought was so completely awesome. Also annoying is that google docs spreadsheets don't play nice with 2007, or more likely 2007 doesn't play nice with google. I can copy and paste between google and excel 2000 and the data is perfectly pretty. Try pasting into 2007 and it becomes an awful mess. I guess that's something google will have to fix eventually.

 

[lionelhill]

Thanks for explanation. I knew there was a reason for sticking to older version of Excel. Luddite that I am.

 

[anationalacrobat]

Excel 2007 is really a mixed bag. Charts look prettier than they have before but Excel charts have been notoriously, hideously ugly for far longer than the technology warrants -- they should have been looking pretty as far back as Office 95. Having more than 65k rows is nice but again, that's a strange limitation to have existed all these years. I've been waiting for the ribbons to grow on me but they just haven't convinced me of their utility yet -- I'm still stuck looking for things where I was used to finding them. The old menus method could be arcane and wasn't exactly intuitive to the new user but they were like QWERTY keyboards, a sub-optimal solution you could live with. The ribbons seem like a sub-optimal solution of another flavor. The whole XLSX thing is another pain as well. So we had an extension that worked well enough for as long as I used the product and now arbitrary changes are made to muck it up.

This makes me sad because Excel was the only Microsoft product I was an unabashed fan of.

 

[lionelhill]

a bit off-topic, but I'm also a fan of non-optimal but working solutions, and a fan of Excel(2003!). The first spreadsheet I ever used was 20-20, back in the days of DOS. It was, I think, a poor-man's Lotus123, and worked in much the same way. This being DOS, there were no drop-down menus, but everything was accessed by keyboard codes after a slash, so to delete something it would be something like /ed (edit, delete: you didn't see it on the screen, it just happened).

One day, a year or two ago, using Excel, my brain spontaneously reverted to 1990, and I typed a 20-20 keyboard instruction. HUGE shufflings of hard-disk, a great thinking pause, and a window popped up: "You just entered a Lotus123 keyboard code. Would you like to activate this feature of Excel?"

Now that, in my view, is superb IT design: thinking what the user wants, and offering it.

 

[anationalacrobat]

It's nice that they're keeping the old codes available in the current version but back when they were wooing Lotus users away, it was absolutely and utterly necessary. It was simply intolerable to ask people to give up everything they were proficient with to use this brand new spreadsheet application. Spreadsheet jockeys are usually similar to secretaries and Word Perfect -- they're absolute wizards of the shortcuts and know the program backwards and forwards but might not be all that familiar with computers in general so they're like someone who memorizes phrases in a foreign language phonetically without actually understanding where the words are in that phrase or what they mean. They can memorize how to ask for a beer or a glass of wine but can't quite make the intuitive leap that the identical part of the phrase is saying "pardon me, but could I order" and the different part must be the word to describe the beverage.

People like to resort to car analogies when talking about computers and I'd say the modern car is where the computer should be. A driver should have a basic understanding of physics and some common sense. Don't take a sub-compact out mudding, realize an SUV is top-heavy and being in a rolling tank doesn't except you from the laws of physics, remember to put gas in when it's low and take it in for scheduled servicing. Follow those rules and you don't have to be an auto mechanic to drive a car, you don't have to get out and fiddle under the hood every 50 miles, it just works. Computers have gotten a lot better than they have been but there's still times when a geek is needed to straighten things out.

That being said, there's still a basic level of knowledge that should be mandatory for people who are going to use computers. Last week I had to spent ten minutes explaining to someone why you don't open pdf's with Word. "But I open every other document I need with Word!" I put that one right up there with someone manually tallying a column on a printing calculator and handing in the printout of the spreadsheet with the tally tape stapled to it. Yes, I've actually seen that done.

 

[AnotherHiggins]

FWIW: Excel 2007 supports Excel 2003 keyboard shortcuts. So [Alt] [D] [P] will still get you to the Pivot Table wizard, even though there is no [Data] menu.

In fact,that particular shortcut is especially handy because it takes you to the old-style Pivot Table Wizard -complete with Multiple Consolidation Ranges - which doesn't appear when accessing PTs through 2007's interface.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.