Microsoft Excel - How often do you use it?

[Coach]

For advanced users, tell us some of your tricks in terms of using the formulas and such.

For beginners, ask questions here, hopefully answers could be provided.

As for me, my question to the experts/advanced users,

Let's say if I have a spreadsheet with column A that has names, typically last name, first name and middle initial (example: Smith, Joe Steve). Column B has names that is typically the same as column A, except it would be missing the middle name or a misspelling on the first name (example: Smith, Joe).


Column A would have say 450 names and Column B would have 150. How can a "near" match search be found, after utilizing the conditional formatting that highlights duplicate values?

[duncan_idaho]

Originally Posted by kccrow

You should use Print functions for what you want in my opinion. In Excel you can select Rows to repeat at the top of each page. Create a header in row 1 of your sheet with the date in it and repeat it. Go to Page Layout Tab and then to Print Titles where you'll see "rows to repeat at top"

Detoxing, this is the best/least manual way to do what you want.

I misunderstood your ask a little bit.

You COULD even try using a DATE function in your date cell so it automatically updates to "TODAY" when you open the file.

=today()

[ModSocks]

Originally Posted by duncan_idaho

You COULD even try using a DATE function in your date cell so it automatically updates to "TODAY" when you open the file.

=today()

wait wha? What goes in the parentheses?

[eDave]

Originally Posted by Detoxing

wait wha? What goes in the parentheses?

The date will autofill within the parentheses. Type it as is.

[ModSocks]

Originally Posted by eDave

The date will autofill within the parentheses. Type it as is.

Originally Posted by duncan_idaho

Detoxing, this is the best/least manual way to do what you want.

I misunderstood your ask a little bit.

You COULD even try using a DATE function in your date cell so it automatically updates to "TODAY" when you open the file.

=today()

Ah that is dope. Thanks mah dudes.

[Shaid]

Originally Posted by MMXcalibur

I am managing my online EA leagues using Excel and learning quite a bit. Learned about absolute references too. =$A$1 will make it so you can copy/paste and the pasted entries won't auto-change.

yep, or you can just use a $ in front of the A so it's keeps the A column but will change the row or vice versa. Very useful.

[Shaid]

Another useful little trick is if you have an excel formula and you want it to be the same formula in the column all the way down the sheet, just hover over the bottom right corner of the cell with the formula until you see it change to a + and then click and drag down. It'll copy that formula to all the subsequent cells below it.

[eDave]

Originally Posted by Shaid

Another useful little trick is if you have an excel formula and you want it to be the same formula in the column all the way down the sheet, just hover over the bottom right corner of the cell with the formula until you see it change to a + and then click and drag down. It'll copy that formula to all the subsequent cells below it.

Just click and drag the lower left corner of the formulated cell. Sideways too. Hitting either alt, shift, or control will change how it copies itself. All kinds of tricks.

[Coach]

Originally Posted by Shaid

Another useful little trick is if you have an excel formula and you want it to be the same formula in the column all the way down the sheet, just hover over the bottom right corner of the cell with the formula until you see it change to a + and then click and drag down. It'll copy that formula to all the subsequent cells below it.

Yeah, I do that a lot on my job. I'll have 10 rows that will contain a different set of money amounts, but they all are into one same batch #, so I would just select the first one, and hover it to the bottom right, and click on the mouse when it changes to the + symbol, and drag it down.

It would also go up too as well if I have to start at the bottom of the pile and it's 265 different set of money amounts but all are in a same batch #.

[ModSocks]

Ok, next question:

Lets say i have the same sheet in two different workbooks. Is there a way to make the sheet in workbook B automatically update when i edit it in workbook A?

[duncan_idaho]

Originally Posted by Detoxing

Ok, next question:

Lets say i have the same sheet in two different workbooks. Is there a way to make the sheet in workbook B automatically update when i edit it in workbook A?

Yes, you can do that. This is the way I know to do it. May be a more automated solution.

1) save the two files in the place you always want them to live.

2) create reference formulas in workbook b to the corresponding cells in workbook IE (=WorkbookName!A7)

3) make your updates in workbook a. Save.

4) open work b. When it asks you if you want to update, say OK.

As long as you don’t move Workbook A, workbook b should always update when you open it.

You could probably make it happen without opening workbook B with a fancier approach.

[Buehler445]

Originally Posted by duncan_idaho

Yes, you can do that. This is the way I know to do it. May be a more automated solution.

1) save the two files in the place you always want them to live.

2) create reference formulas in workbook b to the corresponding cells in workbook IE (=WorkbookName!A7)

3) make your updates in workbook a. Save.

4) open work b. When it asks you if you want to update, say OK.

As long as you don’t move Workbook A, workbook b should always update when you open it.

You could probably make it happen without opening workbook B with a fancier approach.

That’s how I know too. If possible I keep all the tabs in one workbook if they use common datasets. I have some giant bastard spreadsheets but that’s my goal.