• Welcome to OGBoards 10.0, keep in mind that we will be making LOTS of changes to smooth out the experience here and make it as close as possible functionally to the old software, but feel free to drop suggestions or requests in the Tech Support subforum!

The Excel Help Thread

timdunkandthefunk said:
Doctors, apparently.

Thanks for the help, I guess it is a multi-step process. I couldn't get the pivot table to do what I needed so I'll go with the countif.
Click to expand...

just curious, what was not working with the pivot table?
 
Pivot table should work..they're just a bit tricky to use at first if you aren't familiar.

For future reference, what you would want to do is put your column header in both the Row Value and Values boxes (if it sums instead of counts then left click on the value field one and then click "Value Field settings" where you can change it to a count).
 
TBR said:
Pivot table should work..they're just a bit tricky to use at first if you aren't familiar.

For future reference, what you would want to do is put your column header in both the Row Value and Values boxes (if it sums instead of counts then left click on the value field one and then click "Value Field settings" where you can change it to a count).
Click to expand...

yeah, this is where I was going. Pivot table definitely should have worked, and then after you get the pivot showing your counts of each, you just need to copy/paste as values somewhere else and build the pie chart from the values, not the straight pivot table.
 
Word List to Excel Conversion

Trying to help my sister out. Her predecessor at her job is a moron and saved their e-mail list in a word document and not Excel. She has a 20 page word document filled with this: Tim Dunk <timdunk@ogboards.com>, Tonya Dunk <tonya....> etc etc. Is there an easy way to get all of these names and e-mail address into rows of an excel? Hell, even just the e-mail addresses without the corresponding names would be fine.

Google has been surprisingly unhelpful in this venture. Thanks.
 
Copy/paste into excel. Use Text To Columns (using delimiter) to separate them out. Copy/paste special (transpose) to make it a vertical list instead of horizontal. May need to use a few other tricks to manipulate it to get exactly what you want but that's the gist of it.
 
timdunkandthefunk said:
Trying to help my sister out. Her predecessor at her job is a moron and saved their e-mail list in a word document and not Excel. She has a 20 page word document filled with this: Tim Dunk <timdunk@ogboards.com>, Tonya Dunk <tonya....> etc etc. Is there an easy way to get all of these names and e-mail address into rows of an excel? Hell, even just the e-mail addresses without the corresponding names would be fine.

Google has been surprisingly unhelpful in this venture. Thanks.
Click to expand...

You should be able to do it with a data import. Looks like the records are separated by commas and the names by spaces. If you do a global replace of the spaces with some character like * then import and use the * as the separator, you should get what you want.
 
timdunkandthefunk said:
Trying to help my sister out. Her predecessor at her job is a moron and saved their e-mail list in a word document and not Excel. She has a 20 page word document filled with this: Tim Dunk <timdunk@ogboards.com>, Tonya Dunk <tonya....> etc etc. Is there an easy way to get all of these names and e-mail address into rows of an excel? Hell, even just the e-mail addresses without the corresponding names would be fine.

Google has been surprisingly unhelpful in this venture. Thanks.
Click to expand...

You should be able to do it with a data import. Looks like the records are separated by commas and the names by spaces. If you do a global replace of the spaces with some character like * then import and use the * as the separator, you should get what you want.
 
Copy the list into Excel, then try the find / replace function.

1) Find *<, replace with a blank, then 2) Find >*, replace with a blank.
 
Posted on the chat thread, bringing it to a different thread for visibility...

Columns A and B are employee numbers and names; I want them the same across all worksheets (currently have one worksheet for every month, as well as a YTD sheet)
Columns C-EM are daily and weekly summary cells that tie to individual employees.

How can I make it so I can add a new employee in, say, March and have it add that line and keep everyone's data with them across all the other sheets? As of right now I can get it to add the employee (I made columns A and B "table1" in January, and have all the rest of the month sheets reference =table1 in columns A and B) but can't get it to move people's data with them. The other ways I tried brought ALL the data, not just the formulas, which screwed up each individual months' entries.
 
lbE08 said:
Posted on the chat thread, bringing it to a different thread for visibility...

Columns A and B are employee numbers and names; I want them the same across all worksheets (currently have one worksheet for every month, as well as a YTD sheet)
Columns C-EM are daily and weekly summary cells that tie to individual employees.

How can I make it so I can add a new employee in, say, March and have it add that line and keep everyone's data with them across all the other sheets? As of right now I can get it to add the employee (I made columns A and B "table1" in January, and have all the rest of the month sheets reference =table1 in columns A and B) but can't get it to move people's data with them. The other ways I tried brought ALL the data, not just the formulas, which screwed up each individual months' entries.
Click to expand...

Sounds like some creative use of vlookups would do the trick. I'd have to see your data to tell for sure, though.
 
TWDeac said:
Sounds like some creative use of vlookups would do the trick. I'd have to see your data to tell for sure, though.
Click to expand...

yeahhhh. I figured this was the case, but it was giving me a headache to try and figure out based on the other formulas already in play; this being one of them:

=SUM(IF(I6>0,I6,0),IF(L6>0,L6,0),IF(O6>0,O6,0),IF(R6>0,R6,0),IF(U6>0,U6,0),IF(X6>0,X6,0),IF(AA6>0,AA6,0))
 
lbE08 said:
Posted on the chat thread, bringing it to a different thread for visibility...

Columns A and B are employee numbers and names; I want them the same across all worksheets (currently have one worksheet for every month, as well as a YTD sheet)
Columns C-EM are daily and weekly summary cells that tie to individual employees.

How can I make it so I can add a new employee in, say, March and have it add that line and keep everyone's data with them across all the other sheets? As of right now I can get it to add the employee (I made columns A and B "table1" in January, and have all the rest of the month sheets reference =table1 in columns A and B) but can't get it to move people's data with them. The other ways I tried brought ALL the data, not just the formulas, which screwed up each individual months' entries.
Click to expand...

What do you mean by moving everyone's data with them? Are you entering new people at the bottom of the list, or do you sometimes insert a new person in the middle of the list, so you want other tabs to shift as well?
 
I'd like to create an excel file that has the same custom template (from Excel's template gallery) on multiple sheets. I can't figure out how to do this on Excel for Mac, do I need a windows computer to do it? Tried copying/pasting, but then each subsequent sheet draws its numbers from the original sheet instead of the numbers I'm entering on that one.

Any help would be great!
 
can you do a "Paste Special" and do only formats/formulas?
I've never tried that on a mac, though...
 
on a pc anyway there is an option for "formulas and number formats."
 
Question for an Excel guru. This is probably a very simple solution, but the directions I've found online dont pinpoint exactly what I'm looking for and I'm having a brain fart trying to come up with something.


I have a =count formula in cell A6 that is calculating the number of cells that contain a value and multiplying by another number. I want to be able to enter the text "Total:" into the cell, so the final cell will read "Total: 182354". How I do dis?
 
tsywake said:
Question for an Excel guru. This is probably a very simple solution, but the directions I've found online dont pinpoint exactly what I'm looking for and I'm having a brain fart trying to come up with something.


I have a =count formula in cell A6 that is calculating the number of cells that contain a value and multiplying by another number. I want to be able to enter the text "Total:" into the cell, so the final cell will read "Total: 182354". How I do dis?
Click to expand...

="Total : "&A6 should work.
 
You must log in or register to reply here.
Back
Top