?

Log in

No account? Create an account
Microsoft Excel discussion, advice and commentary's Journal
 
[Most Recent Entries] [Calendar View] [Friends]

Below are the 20 most recent journal entries recorded in Microsoft Excel discussion, advice and commentary's LiveJournal:

[ << Previous 20 ]
Thursday, September 13th, 2012
12:51 pm
[janetmiles]
Making the selected cell stand out more?
I was asked yesterday if there was any way in Excel to make the selected cell stand out more -- something more than just the heavy border around the cell, especially when there are already cell borders which may mask that.

The question arose in relation to finding the selected cell when someone searches for information in a spreadsheet.

I thought there might be something in accessibility, but I don't see anything helpful.

The only thing I can think, if the person is using 2007 or 2010 with the ribbon, would be to do the search and click one of the "cell formatting" options in the ribbon to make the cell stand out, then do ctrl-z to undo the formatting. That doesn't seem like a really good workaround, though.

Any ideas?

(crossposted to various places, including my own LJ, my Facebook, and my IAAP chapter listserv)
Wednesday, October 20th, 2010
9:57 am
[fadesdog]
Pop-up memos in Excel?
One of my co-workers bounced this one on me--she wants to have a memo appear when someone opens one of her spreadsheets. This would be an announcement that outlines a couple of critical features, has one or two "Under No Circumstances", and so on, before her clients (sales representatives, who must be carefully controlled because they are dangerous gibbons) start monkeying with forces they cannot understand.

Either that, or a way to force the spreadsheet to open up at cel A1 instead of wherever the last person saved it.

Any thoughts? Much appreciated! All my books are on the other side of town so I can't get a quick answer!

[Edit: We eventually decided just to Freeze Pane the top row, it's simpler, even though that causes its own problems...EZ solutions are happy solutions!]
Monday, August 2nd, 2010
2:55 pm
[maldini_3]
First time poster
Hi all,

I'm looking to expand upon a score sheet used for our states 8ball association sports comp.  The association developed a spreadsheet found here  . This printed out, completed, and signed by each team captain at the end of each game night. 

I'm hoping to be able to enter the scores electronically (be it on the night or afterwards) to begin to calculate statistics for the season for both the team and the individual players. There are separate spreadsheets set up to display some of these stats, however all this data is entered and calculated manually! I'm looking to push a bit of technology use into the association so that at the end of the year the lump sum of data entry and statistical analysis is already done.

I know little about spreadsheets, probably enough to get myself into trouble and thats about it. 

I'm a student with no money to send this to some IT company to develop for me so I'm going to try it out myself but will most definitely require some help along the way.

Would this community be willing to help me in this project?

Cheers

Joe

Current Mood: creative
Thursday, January 21st, 2010
2:10 pm
[dasha]
Dear Community!

you've helped me with functions before, hope I can get some advice this time :)

I need to find a way to produce a functionthat will do something like this:

"If A1 contains a word from the list in column C, then “yes”, if not, then “no”"

Is there any function to help with that? the normal IF function doesn't help here...

explanation...Collapse )

Thank you so much in advance! Sorry if the explanation is confusing...
Tuesday, January 19th, 2010
12:14 pm
[fadesdog]
A journey into madness...
I'm pleased with this one, it stumped all the office Excel gurus.

Here's my challenge: I'm making a summary report of six states in the midwest, tracking expenditures on promotional pieces. The *relevant* variables are: Month (Jan, Feb, Mar), cost, and category (print, e-marketing, other).

Pivot-tables seemed to be a no-go, because this would have required pivot-tables drawing from multiple data ranges, which didn't seem like the best solution. Even armed with Mr. Excel's "Pivot Table Data Crunching" I couldn't have a versatile and easy-to-shuffle pivot table from all these different ranges.

My solution: Given that each state has to have its own tab, identify each expenditure category with a number (print=1, e-marketing=2, other=3), and abbreviate each month as (jan, feb, mar), so that each expenditure has a hidden field marked "1:Jan" or "3:Aug" to represent as a January Print project, or an August: Other project. The summary pages could then be built using =SUMIF formulas. Brilliant!

January worked perfectly.
February, not so much.

After twiddling and fiddling and simplifying, it seemed that if there were 20 entries, and three of them were identified as January, all the other months would count 17 entries. If there were 19 Januaries, all the other months would count 1 entries. All with the same total.

I started replacing the concatenate strings with the actual value, to see if I could understand exactly what was going on. No change. I added an apostrophe before them, to force them into text strings. The first one I did that to replaced "1:Feb" with "620384."

Aha.

Somehow, it decided that everything except "1:Jan" looked enough like a time entry that I really *meant* it was a time entry.

Solution: Replace "1, 2, 3" with "A, B, C". No problem now. I write here because nobody understands what I'm saying at the office.

(on another note, I'm the community moderator after a name-change and a year of unemployment-induced melencholia. I'll be a little more active now that things are looking up--sorry for the absense!)
Tuesday, December 22nd, 2009
11:26 am
[wringham]
How do I delete entries from one column IF they appear in another?
In a spreadsheet, is it possible to delete entries from Column A if they also appear in Column B?

In other words, I want to 'dovetail' two columns so that no entry is represented in both columns.

Any help will be met with gratitude.
Thursday, November 26th, 2009
4:43 pm
[evil_admiral]
Complicated charting
Ok. So I'm working on a project were I have pivot charts in Excel 2007. I have these 3-D bar charts, where I have a dates on one x-axis, the average test value on each date on the other x-axis, and the y-value is the test value. What I want to do is add a thresh-hold line for each test, (a horizontal line for each test type), which can or can not intersect each bar. Since I have five test types, I will have five thresh-hold lines on the graph, which will be placed according to the business rules in the spreadsheet. The problem is that I don't know how to do this or even if this is possible. Help!?
Example chart:



Thursday, August 27th, 2009
12:18 pm
[janetmiles]
Finding an entry from one list in another list, and then matching additional data
This is a solution, not a problem. *grin*

Setup: I have a list of 1,700 serial numbers, each of which has an activation code associated with it. I have another list of 3,000 serial numbers, some of which are on the short list. No activation codes are given in the second list.

Problem: Associate activation codes, where possible, with the long list of serial numbers.

Solution: (I probably could have done this with a reference to the second spreadsheet, but to keep it simple, I posted the short list next to the long list. Once I was sure everything worked, I copied the column and pasted it back as values.)

=IF(ISNA(MATCH(B2,$J$2:$J$1669,-1)),"",VLOOKUP(B2,$J$2:$K$1669,2))


How it works:
  1. Match the serial number in the current row against the short list, and return #N/A if it isn't there.
  2. If the MATCH returns #N/A, leave the code cell blank.
  3. Otherwise, LOOKUP the serial number in the short list and return the corresponding activation code.
Monday, July 6th, 2009
12:51 pm
[spottylogic]
Happy 40K!
This morning, the (Windows) Excel date count ticked over to 40,000.

*uncorks a bottle of v-e-r-y cheap champaigne*
Thursday, June 11th, 2009
3:25 pm
[cabinetsanchez]
I'm trying to think of a function that I am fairly sure exists. Probably if I could think of the mathematical term that would answer the question too.

You either specify a number and it gives you the sum of all numbers leading up to that number, or you specify two numbers and it adds up the numbers between those numbers.

For instance,

=ADDALL(4) would produce 10 (1+2+3+4).

I know FACT produces the factorial of a number, which is all numbers leading up to that number multiplied together, but I need addition.
9:43 am
[janetmiles]
Date oddities in Excel 2007
I have no idea what just happened here. I just watched a co-worker, using Excel 2003 Mac, copy about 100 rows from one workbook to another. In the new workbook, every single date was increased by four years and one day (that is, February 1, 2000 became February 2, 2004, and so on).

There were no formulas involved that I could see (that is, nothing in the formula bar). The cells in the original sheet were formatted as Custom: mm/dd/yyyy.

I'm using Excel 2003 on Windows, and when I copied the rows, the dates did not change. Any idea what could have caused this and how to prevent it from happening again?

OH WAIT. I wonder if he has "1904 date system" set. I'll ask him when he gets back from his meeting, but in the meantime, I'm open to any other suggestions.

On second thought, maybe not: I just tried setting my own copy of Excel to use the 1904 date system, and I did not get the same result. Never mind.
Thursday, April 2nd, 2009
8:17 am
[cabinetsanchez]
Argh
Argh argh argh argh.

I’m trying to do a mode formula in Excel, but I only need the mode of pieces for material matching the current line. For example, in the following spreadsheet:

A
B
C
1 Item Pcs Standard
2 Bunnies 20 .
3 Bunnies 20 .
4 Bunnies 14 .
5 Chicks 14 .
6 Chicks 14 .
7 Bunnies 18 .


Essentially, in C2:C4 (and C7) I need it to return 20, which is the mode of the pieces of all items matching the current row. In C5:C6 the answer should be 14. I’ve been tinkering with array formulas to try to get the answer I need, but no luck. Any ideas?
Tuesday, March 31st, 2009
11:07 am
[cabinetsanchez]
Password creator
I've been playing with the CHAR function a bit, and I have come up with a formula that will generate a "strong" 8-character password for you. Just plop this into a spreadsheet and use F9 to re-generate. My advice is to use a font like Courier New for it, though, so that the special characters are easier to read.

Note: This formula uses the RANDBETWEEN function, which is only enabled after going to Tools->AddIns and enabling the Analysis Toolpak.

If the system is fine with accepting special characters and spaces (like Windows), use this one:
=char(randbetween(33,126))&char(randbetween(32,126))&char(randbetween(32,126))&char(randbetween(32,126))&char(randbetween(32,126))&char(randbetween(32,126))&char(randbetween(32,126))&char(randbetween(33,126))

No spaces:
=char(randbetween(33,126))&char(randbetween(33,126))&char(randbetween(33,126))&char(randbetween(33,126))&char(randbetween(33,126))&char(randbetween(33,126))&char(randbetween(33,126))&char(randbetween(33,126))

No special characters or spaces:
=choose((randbetween(1,3)),randbetween(0,9),char(randbetween(65,90)),char(randbetween(97,122)))&choose((randbetween(1,3)),randbetween(0,9),char(randbetween(65,90)),char(randbetween(97,122)))&choose((randbetween(1,3)),randbetween(0,9),char(randbetween(65,90)),char(randbetween(97,122)))&choose((randbetween(1,3)),randbetween(0,9),char(randbetween(65,90)),char(randbetween(97,122)))&choose((randbetween(1,3)),randbetween(0,9),char(randbetween(65,90)),char(randbetween(97,122)))&choose((randbetween(1,3)),randbetween(0,9),char(randbetween(65,90)),char(randbetween(97,122)))&choose((randbetween(1,3)),randbetween(0,9),char(randbetween(65,90)),char(randbetween(97,122)))&choose((randbetween(1,3)),randbetween(0,9),char(randbetween(65,90)),char(randbetween(97,122)))

(Yes, the last one is a little lengthy. You can blame whoever came up with the character library and decided that the capital letters and lowercase letters should be seperated by a handful of special characters.)
Wednesday, March 11th, 2009
9:21 pm
[janetmiles]
Lookup functions
Okay, all of you who've been singing the praises of VLOOKUP?

You're right.

I still don't quite know how to set up the formula on my own, but the lookup wizard in Excel 2003 is wonderful. I finally got to a point where I needed that functionality, and now that I have a vague idea how it works, I suspect I'll be using it more often.

Thanks!
10:01 am
[cabinetsanchez]
Progress bar
Wrote this bit of code to show a visual progress bar somewhere on a spreadsheet.

="["&REPT("l",IF(O1*100>50,50,O1*100))&REPT(".",IF(50-(O1*100)<0,0,50-(O1*100)))&" ("&ROUND(O1*100,0)&"%) "&REPT("l",IF(O1*100>50,(O1*100)-50,0))&REPT(".",50-IF(O1*100>50,(O1*100)-50,0))&"]"

Here, O1 contains a percentage. Change the references to whatever cell you want, obviously (highlighted for visibility). The readout looks something like this:
Friday, February 20th, 2009
7:55 pm
[minmin1988]
find slope
Is there anyway to display the slope values of a line without a trendline?

When I delete the trendline the slope eqn and R^2 also disappear.  I don't want to trendline on my graph just the equation


thanks.,
Monday, February 2nd, 2009
9:18 pm
[spottylogic]
Worst Excel nightmare...
Hoboy. This was worse than the one where you're going to visit your future in-laws, but aren't wearing any pants.

Setting: Have somehow, after a month of unemployment, landed job interview with prestigious university. Am told that most of my potential job duties--90% or so--are Excel reports. "No problem," I say. "I'm wise in the ways of Excel. Do your worst."

Interviewers lead me into a small room with a computer for some demonstrations of Excel prowess. I crack my knuckles, sit down. They leave the room for me to work.

Open the program...it's Excel 2007. I've never even *seen* 2007.

Suddenly, after five years of skilled use, I'm reduced to a sobbing N00b who can't even find "Save As."

Really, if this hadn't actually happened to me this afternoon, it'd be pretty typical of the way my dreams work. Aigh! 2007 is quite, quite hostile if you're used to XP...
[edit: I *did* muddle through it, though, I think I did okay, so still in the running!]
Wednesday, January 21st, 2009
6:40 pm
[prophetsurfer]
Returning a value based on a value
Hey there Excel fans!

I am trying to return the value in the cell to the right of the matching number in a list with another table. So, example:

1Joe11
2Bob15
3Dick17
4Harry10
5Jimmy1
6Sally9
7Jessie8
8Matt2
9Tim12
10Farty14
11Pewpy3
12Hairy16
13Nasty7
14McFunk4
15McNasty13
16Stanks6
17Fatzo5

Let's pretend that's 3 columns, A, B and C. In column D I want to have Excel figure out who 11 is and return the value automatically, which would be Pewpy in this case. D2 would return McNasty, D3 would return Fatzo, etc.

Can anyone suggest some super formula magic which might accomplish this heavy burden? I will love you forever.

Current Mood: hopeful
Sunday, January 4th, 2009
2:00 am
[newdadin09]
Dates
I remember that there is a way to do this, but not the way itself.

I want to enter 12/26 into a cell and have Excel know I mean /2008 and not /2009.

How?
Tuesday, December 16th, 2008
12:46 pm
[cabinetsanchez]
Paste Special macro
Thought I'd share this with the group since I love love LOVE this macro. I found myself doing "right-click->Paste Special->Values" so much I simplified it as a macro. Here is the easiest way to set this up for both those familiar with macros and completely lost when it comes to macros.

1) Open up a blank spreadsheet.
2) Go to Tools->Macro->Record New Macro
3) Assign Ctrl-Shift-V as the keyboard shortcut and select the option to store macro in Personal Workbook
4) Call the Macro "PasteValues".
5) Click OK
6) Click Stop Recording from the toolbar that appears.
7) Go to Tools->Macro->Macros and select the entry for PERSONAL.XLS!PasteValues
8) Click the Step Into button.
9) Select all of the text in the window that appears and hit the Delete key on your keyboard. At this point it will warn you that this will reset your project. Click OK on that window
10) Paste the following into the window:

Sub PasteValues()
'
' PasteValues Macro
' Macro recorded 10/3/2008 by Micah Dyer. Same as Paste Special-->Values
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

11) Close the Microsoft Visual Basic Editor window.

To use the macro, select a range of cells with Ctrl-C, go to where you want to paste the values and use Ctrl-Shift-V (easy to remember since Ctrl-V pastes)

Hope this helps!
[ << Previous 20 ]
About LiveJournal.com