Excel for n00bs

[korme]

I think this is a simple answer, but I don't have it.

Trying to do some fantasy record keeping:

Column A is overall record. In column B I have (x) wins and column C I have (x) losses.

If Column B is 6, and Column C is 4, how do I get Column A to show 6-4, instead of 2?

[Draft Dodger]

=CONCATENATE(A1, " - ", B1)

[SteveMax58]

or...

=B1&"-"&C1

[korme]

Ahh, Google has been my friend tonight.

Here's another one I don't know how to Google.

I want to calculate average finish.
So in a column I have
8
5
4
To get that answer, I would do =sum(B2:B4)/(??)

How do I get it to count the number of columns?

[henry296]

Originally Posted by korme

Ahh, Google has been my friend tonight.

Here's another one I don't know how to Google.

I want to calculate average finish.
So in a column I have
8
5
4
To get that answer, I would do =sum(B2:B4)/(??)

How do I get it to count the number of columns?

Just use the average function instead of sum.

[korme]

So simple. Heh. Thanks.

[Bobble]

But if you ever have a need, there are several COUNT functions that count number of cells with numbers, non-blank cells, blank cells...

[korme]

Actually one of these next requests is app specific, so it's necessary to divulge that I'm using Google Sheets and not Excel (though all of the functions are the same)

I think I only need to learn 2 more things.

OffConstantly

First, how to link to sheets within sheets. For example on that first page, I want all of those names to be clickable and linked to their profile pages.

And secondly, on that second leaders tab, I need to learn how to make all of those columns sortable without messing things up

[Bobble]

Originally Posted by korme

Actually one of these next requests is app specific, so it's necessary to divulge that I'm using Google Sheets and not Excel (though all of the functions are the same)

I think I only need to learn 2 more things.

OffConstantly

First, how to link to sheets within sheets. For example on that first page, I want all of those names to be clickable and linked to their profile pages.

And secondly, on that second leaders tab, I need to learn how to make all of those columns sortable without messing things up

1. Excel has an insert hyperlink button in the button bar. I presume Google has something similar especially since it's Google.

2. With Excel, you'd select the cells with your data and go to the data tab and choose Filter. The top cells in each column would have dropdowns that would let you sort and filter the data.

[korme]

Originally Posted by Bobble

1. Excel has an insert hyperlink button in the button bar. I presume Google has something similar especially since it's Google.

2. With Excel, you'd select the cells with your data and go to the data tab and choose Filter. The top cells in each column would have dropdowns that would let you sort and filter the data.

You can't hyperlink a sheet within a sheet, though. I found some way to do by writing custom scripts, but I haven't dug my teeth into it yet.

As for the sorting, I was hoping I'd be able to make it where the headers are clickable and easily sortable.

[Bobble]

Originally Posted by korme

You can't hyperlink a sheet within a sheet, though. I found some way to do by writing custom scripts, but I haven't dug my teeth into it yet.

You mean hyperlink to another tab, right? You can do that in Excel. See the first part of this link: Create hyperlink in a cell to another sheet in the same workbook

Originally Posted by korme

As for the sorting, I was hoping I'd be able to make it where the headers are clickable and easily sortable.

Filter (in Excel again, I don't know how Google Sheets works) should give you a dropdown that easily lets you select sort a-z, sort z-a, as well as filter out the results. That's not what you're looking for?

[korme]

Originally Posted by Bobble

You mean hyperlink to another tab, right? You can do that in Excel. See the first part of this link: Create hyperlink in a cell to another sheet in the same workbook




Filter (in Excel again, I don't know how Google Sheets works) should give you a dropdown that easily lets you select sort a-z, sort z-a, as well as filter out the results. That's not what you're looking for?

That first option just doesn't work in Sheets.

2ndly, about filter, that gets the results I want for one column, but I really just wanted to be able to make all of the columns clickable and easily sortable. I suppose I'll just leave it sorted by wins.

[Bobble]

Originally Posted by korme

2ndly, about filter, that gets the results I want for one column, but I really just wanted to be able to make all of the columns clickable and easily sortable. I suppose I'll just leave it sorted by wins.

I think you can do that in Google Sheets. Just highlight all the data that you want to be sorted together and go to Data/Filter. That will give you dropdowns in your top cells that will let you select A-Z or Z-A sorting. See the attached pic.

[SteveMax58]

Originally Posted by korme

That first option just doesn't work in Sheets.

2ndly, about filter, that gets the results I want for one column, but I really just wanted to be able to make all of the columns clickable and easily sortable. I suppose I'll just leave it sorted by wins.

On the filter...you need to first highlight ALL of the cells (or the entire row) you want to have that filter, then apply the filter (i.e. you cant highlight 1 cell, apply filter, highlight another then apply again). The only other catch is that they need to be contiguous in a database header style and you can only apply 1 filter range per worksheet.

On the issue with linking...maybe try making a macro for each owner's worksheet & apply the macro to an owner-specific button (i.e. the button click event). You could place a button for each of the owners on the worksheet (i.e. where the owner names are) and make them transparent if you dont like the look of the button. Assuming thats supported in sheets anyway (not certain but I think it is).

Excel has an easy way to insert hyperlink into a cell's contents so I find it hard to believe sheets doesn't do this. But I havent really used it so could be mistaken.

[korme]

Originally Posted by SteveMax58

On the issue with linking...maybe try making a macro for each owner's worksheet & apply the macro to an owner-specific button (i.e. the button click event). You could place a button for each of the owners on the worksheet (i.e. where the owner names are) and make them transparent if you dont like the look of the button. Assuming thats supported in sheets anyway (not certain but I think it is).

Excel has an easy way to insert hyperlink into a cell's contents so I find it hard to believe sheets doesn't do this. But I havent really used it so could be mistaken.

I'll try that this weekend. I googled some help about it, and yes, it's true, sheets cannot currently do such an easy task.