computer/programming/excel wizards! help an idiot!

I think you mean Excel 2008. When I double-click the row or column headers (the silver box with the name of the field) I get a dialog box that lets me select values to hide. Does that work for you?

Excel 2008, sorry yes. Double-clicking the silver bar headings gives me Pivot Table field settings, with the option to 'hide' a set of data but this isn't quite what I'm looking for as there doesn't seem to be an easy way of switching between the filtered data.

Yeah, not ideal. If you figure out a better way I'd be interested to know!

please help!

why sometimes when i put in a formula in a cell, and then i either double click the corner or drag down to repeat cell multiple times, does it not auto calculate the correct results, it just copies the same result from the first cell?

sorted, clue was in the question. for some reason Calculations were set to manual. i didn't even know that was an option

ALERT: IDIOT ASKING A QUESTION:

How can I do a running total, like in a check register? For example:

100
+1=====101
-10====91
+5=====96
and so on.

+1 101
-10 91

assuming this is the top left corner
you would want the 91 cell to be "=B1+A2" then you could copy and drag that it down the column and it would add up the rest

My name is Lex and I approve this message.

My favourite formula of late is like:

{=SUM(IF(F3:F3648=F3651,I3:I3648,0))}*

Which checks F3651 to get the value, looks for all instances of that value in F3:F3648, and then sums up the values in I3:13648 that correspond to matches in the F column. Useful in my case for plotting sales of certain brands - I can say how much Brand A sold, BRand B, etc simply by populating the I column with the sales figures.

*Curiously, you don't type the curly brackets, just the rest of the formula - but if you hit enter to get out of the formula it won't work, you have to hit CTRL + SHIFT + ENTER

And of course, always 'lock' your ranges by clicking F4 while they're selected, or manually entering them. Because when you paste a formula it will move your range relative to how much your pasted data moved.

Locked $A$1
Unlocked $A$1

I always want the row to stay but not column A$1
I always want the column to stay but not the row $A1

well I fucked up unlocked obviously, that's just A1

IF formula's are my favorite!! I thought I had done full on magic when I got my first IF forumula to work!

dlookup is my favorite, but if's good too

--->just typed =dlookup into Excel to see if that was anything other than a typo

I do love my vlookups. The if I just posted is like a vlookup that just sorts shit out for you on the go! It fuels many of my reports!

array formulas :o

=sum(countif(E:E,A2)) gets a lot of love from me.

Thanks, people! I forgot to bookmark this thread so I thought that my question went unanswered and I felt weird!

I have one more question and I PROMISE that's it for a long while: Is there some way to make newer entries push down older ones. As if you are stacking objects and the newest object on the stack is always at eye level? So that there would always be an open row where I could make new entries and the 1st one entered is on the bottom and the latest one entered is on top.

I want to use this for a document log.

Thanks for listening to me. Sorry my questions are so completely elementary!

I don't know a way, unfortunately. Short of adding a new row to the top each time, but that might mess with your headers... Someone else may have a way!

I like that sum/countif one. May implement!

Is there a way to use Custom Lists to sort a column so that the empty cells are on top instead of last?

If the non-empty cells are fixed, then you could reverse sort them in the custom list, and the blank should end up on top.

I've got a list of about 2000 names. Just three columns: ORGANIZATION | CONTACT NAME | EMAIL

There in Column 3 is where I'm having problems. Some of the email addresses are active, I can click on them and my Thunderbird lights up. But some of them just sit there like ordinary text.

I notice in the field for the active emails, there's a bunch of [409tru09uHYPERLINK;;; susie@thejuniorlea✧✧✧.com;"su✧✧✧@thejuniorlea✧✧✧.c✧✧"9823u5. How can I make it where ALL of the email addresses are like that?

I'm doing all of this in Google Drive, btw.

I think you can turn email addresses into links by adding "mailto:" at the beginning.

That worked, albeit the email address column has "mailto:" before every address. I can overlook that.

Now. Is there an easy way to do that 2000 times?

You could use a formula, like "=CONCAT("mailto:",C1)" where "C1" is whichever cell contains the first email address. Then fill that down to all 2000 cells.

I have an excel table with client details in each row. one field is their manager’s name, another is their status in a range from A-F.

can I use excel to run through the data and count and how many each manager has at each status (or betw a range of statuses?) and how would I ‘automate’ this? macros?

I think you could just do that in a pivot table.

pivot table u&k for tasks like that

pivot table is yr friend

I've come unstuck in a pivot table and it's driving me mad. I have two columns, SCHOLARS (a count of names) and EMPLOYED (a count of those who have a work address), with YEAR as rows. I want to add another column to show the percentage of scholars who got a job each year. As both the columns had already been counted, I assumed I'd be able to add a calculated field with the formula =EMPLOYED/SCHOLARS but I get a #div/0 error. What am I doing wrong?

Have you tried pasting the values of the pivot table and adding the calculating column in there?

Yep, that'd do the job. It was just biting at me that I couldn't work out a way to do it in the pivot, but I guess why make things any harder for myself.

Thx!

pivots have halved the time I spend on certain things and filled that time with other things

It's swings and roundabouts.

otm, this is totally one of them. Annual statistics exercise, so will be much easier next year- but setting it up's been a fucker

I can't remember if I was ever able to add columns into PTs to do things like that - it's been a while. Wish they were just a touch more malleable!

Hi there sorcerers, I have a dilemma.

I'm working with a new ordering system at work and it's slowing me down. I need some excel magic.

Every week I am going to receive an order spreadsheet.
The order spreadsheet is set up so that each row is a unique order for an account.
The items needed are listed on the header row. This week, there are 9 items for each order
And column A lists the account numbers
each row of cells under the header row contain the order qty's needed for each account.

for example:
Row 2: A2 = acct #, b2 = qty for item 1, c3 qty for item 2, d4 = qty for item 3

My dilemma is that for the orders to be loaded into our system, the data needs to be put into a template which requires the following setup:

Column A: account number
Column B: item number
Column C: quantity

So using this week's order as an example, since there are 9 items, the first account number will have to repeat 9 times in column a; the item numbers go in column b as a set of 9, and they have to repeat for every new account number. And then the order quantities go in column c.

(NB: as far as this weekly order goes, the number of items changes each week, it could be 4 items one week and 10 items the next week, there's no fixed number).

This whole template thing is a new process for me, and everyone's kind of backwards in handling it. And I didn't know enough about excel to come up with an easy way of manipulating the data, so I was reduced to a whole afternoon of cutting and pasting 4000 lines. Which sucks because I know that there is a way to shape the data into the format I need! I just don't know how.

Do any of you have any ideas? This is way out of my depth, but what I'm hoping for is something that I can use each week to rearrange the data.

If you need I can try to create some dummy screenshot examples of what I need when I get home...I know it's kind of hard to visualise.

oh and my primary reason for needing help is that in my role as data-handler, my aim is to touch the source data as little as possible to generate the orders. The less I have to manually manipulate it, the less chance there are of me creating user-errors, and mindless cutting and pasting for 4 hours is a surefire way to get all kinds of wacky errors.

This miiiight be something a pivot table can do, I'm having a hard time visualizing though. If you can email it to me I could take a look..?

:D

vlookup ftw

Sounds like a job for SUMIFS

got sum new ifs in dis week if yr innerested

Did you get it sorted? It looks like ffm has sorted it out, in which case :D

My feeling is that if the number of items changes each week you probably need a VB macro, but if it's the same (or you know there'll always be at most 20 so you can pretend there are always exactly 20 and just make the extra item quantities 0 and have a lot of 0 rows and maybe filter them out again later), or if you don't mind some manual work each week as long as it's not an entire afternoon of cutting and pasting, you could use worksheet functions

but I have not thought about it super deeply, so, hoping you've already found a better way!

I sent her a file with a vlookup programmed into it, and instructions on how it works so that she can hopefully modify the range if needed.. I did think about a macro but I figured everyone should know how to do a sweet vlookup :)

there has been a ticker tape parade, ffm presented with keys to the city, not a dry eye in the place

It's true! I felt like a modern day Mother Teresa.

Oh man I love a good vlookup

i am a fan now

Just remember to never ever merge cells. They will just screw everything up. AVOID AT ALL COSTS

yessir

My biggest gripe with Excel is that I have to use the F2 key to get to the formula bar. Like, who the hell finds it comfortable to use the Function keys?!

That’s actually quicker than what I do. I always use the mouse to click in the formula bar. I’ll have to remember F2.

OK, I have a problem that's been driving me nuts for a while. I have two columns of data, and the rows are groups. It's like this:

Col A. Col B.
person 1 | keyword 1
person 1 | keyword 2
person 1 | keyword 3
person 2 | keyword 1
person 3 | keyword 1
person 3 | keyword 3

I want to eliminate the duplicate rows and put each value in column 2 in it's own column, like this:

Person 1 | keyword 1 | keyword 2 | keyword 3
Person 2 | keyword 1 | |
Person 3 | keyword 1 | keyword 3 |

There's too much data for me to just copy/paste groups of rows and transpose them.

I've tried a pivot table, but that doesn't get me the result I need. It puts all the values of column B into a single row of labels at the top. I've tried various arcane formulae, but nothing seems to work.

fffffffffuuuuuuuuuccccccccccccckkkkkkkkkkkkk

Some clever solutions here: superuser.com/questions/453041

the excel answers I’m getting from google are bad but what you’re looking for is denormalizing into lists

or? if you search for 'vlookup that returns multiple values' there are some different methods that returns exact match results in its own cell.

'vlookup that returns multiple values' was definitely the right thing to search! Many thanks, Yerac. Tinkering with the formula here did the trick.

Good!

oh was going to suggest INDEX but couldn't remember the exact way I used it