Excel Help v. 1,000,000

[JF_Gophers]

Re: Excel Help v. 1,000,000

I would recommend not using Access in a company that's medium to large size. It becomes untenable pretty fast. You will want to go to real a data warehouse solution. Access is fine for smallish companies tho.

[jerphisch]

Re: Excel Help v. 1,000,000

I would recommend not using Access in a company that's medium to large size. It becomes untenable pretty fast. You will want to go to real a data warehouse solution. Access is fine for smallish companies tho.

As the primary database yes, but Access can still be useful for reporting from data warehouses that have horiffic reporting features.

[St. Clown]

Re: Excel Help v. 1,000,000

As the primary database yes, but Access can still be useful for reporting from data warehouses that have horiffic reporting features.

We do this all the time for ad hoc queries, link to SQL tables but write the queries in Access. Our in-house development apps using SQL queries, and for their ad hoc queries. While that'd be nice for we BAs, the bosses are not willing to pay the licensing fees for us to have SQL access.

[bigblue_dl]

Re: Excel Help v. 1,000,000

I would recommend not using Access in a company that's medium to large size. It becomes untenable pretty fast. You will want to go to real a data warehouse solution. Access is fine for smallish companies tho.

Well, I work for a fairly large company. The long-term solution to the problem I'm solving is, hopefully, going to be a database like you're talking about, but the people that would do that work (I'm definitely not qualified), aren't doing anything with it. This is why I'm doing this excel project, to get us by in the meantime until the final project is launched and completed.

[FreshFish]

Re: Excel Help v. 1,000,000

It sounds like you should just use access

why did microsoft invent access?

to make excel look good.

[ExileOnDaytonStreet]

Re: Excel Help v. 1,000,000

Let's suppose I have two columns of data, and each column either has an X or doesn't:

ColA ColB
1. X
2. X X
3.
4. X
5. X X


I want to count how many of my five rows have an X in either column. If I use COUNTIF(range,"X"), I'll return the total number of Xs I see in the entire range. If I use COUNTIFS(ColA,"X",ColB,"X"), then I'm going to return the number of rows that have an X in both columns. Is there a function similar to COUNTIFS that I can use that would have an OR function embedded into it?

I'd prefer to not have to create a third column with IF(OR(ColA="X",ColB="X"),"X","") and run COUNT(ColC,"X").

[FlagDUDE08]

Re: Excel Help v. 1,000,000

Let's suppose I have two columns of data, and each column either has an X or doesn't:

ColA ColB
1. X
2. X X
3.
4. X
5. X X


I want to count how many of my five rows have an X in either column. If I use COUNTIF(range,"X"), I'll return the total number of Xs I see in the entire range. If I use COUNTIFS(ColA,"X",ColB,"X"), then I'm going to return the number of rows that have an X in both columns. Is there a function similar to COUNTIFS that I can use that would have an OR function embedded into it?

I'd prefer to not have to create a third column with IF(OR(ColA="X",ColB="X"),"X","") and run COUNT(ColC,"X").

What about:
SUM(COUNTIFS(ColA,"X"),COUNTIFS(ColB,"X"),(-1*COUNTIFS(ColA,"X",ColB,"X")))

[JF_Gophers]

Re: Excel Help v. 1,000,000

What about:
SUM(COUNTIFS(ColA,"X"),COUNTIFS(ColB,"X"),(-1*COUNTIFS(ColA,"X",ColB,"X")))

Ya, the quick and dirty 1 cell solution is that or this:

=SUM(COUNTIF(A1:B1,"x"),COUNTIF(A2:B2,"x"),COUNTIF(A3:B3,"x"),COUNTIF(A4:B4,"x"),COUNTIF(A5:B5,"x"))

[Ralph Baer]

Re: Excel Help v. 1,000,000

What about:
SUM(COUNTIFS(ColA,"X"),COUNTIFS(ColB,"X"),(-1*COUNTIFS(ColA,"X",ColB,"X")))

Wouldn't =COUNTIFS(ColA,"X")+COUNTIFS(ColB,"X")-COUNTIFS(ColA,"X",ColB,"X") be simpler, or am I missing something?

[joecct]

Re: Excel Help v. 1,000,000

how about ISBLANK?

[FlagDUDE08]

Re: Excel Help v. 1,000,000

Wouldn't =COUNTIFS(ColA,"X")+COUNTIFS(ColB,"X")-COUNTIFS(ColA,"X",ColB,"X") be simpler, or am I missing something?

They're effectively the same.

[ExileOnDaytonStreet]

Re: Excel Help v. 1,000,000

What about:
SUM(COUNTIFS(ColA,"X"),COUNTIFS(ColB,"X"),(-1*COUNTIFS(ColA,"X",ColB,"X")))

Works as well as anything I could think of.

Where it gets really complicated is if I had to do this over a three column spread (and it'd be a lot easier if there was an option within COUNTIFS that allowed for selecting an OR condition)

[jen]

Re: Excel Help v. 1,000,000

I am trying to create hyperlinks to another sheet in my document. I need to copy the same link multiple times, then sort by a different column, then do the same thing for that column, etc.

After I do one column, and sort, the links all get messed up. Anyon e know how to get around this without having to do each cell inidivudally?

I also tried using =HYPERLINK(sheet!cell,"name"), and it only works the first time I click on it. After that, it says cannot open the specified file.

[JF_Gophers]

Re: Excel Help v. 1,000,000

I am trying to create hyperlinks to another sheet in my document. I need to copy the same link multiple times, then sort by a different column, then do the same thing for that column, etc.

After I do one column, and sort, the links all get messed up. Anyon e know how to get around this without having to do each cell inidivudally?

I also tried using =HYPERLINK(sheet!cell,"name"), and it only works the first time I click on it. After that, it says file not found.

My guess is that it gets messed up because you aren't making the cell placement permanent. Try using a dollar sign in front of the cell row and column (i.e. $A$2). Then it will never change.

If you only want one or the other to never change, you can just use the dollar sign in front of one of them (i.e. $A2, A$2).

[jen]

Re: Excel Help v. 1,000,000

My guess is that it gets messed up because you aren't making the cell placement permanent. Try using a dollar sign in front of the cell row and column (i.e. $A$2). Then it will never change.

If you only want one or the other to never change, you can just use the dollar sign in front of one of them (i.e. $A2, A$2).

didn't work. it isn't that the link points to the wrong place - it doesn't point anywhere. it's like it linked that cell, and when I sorted, it didn't sort the link along with the data.

this apparently was a known problem in 2007, but I have 2010 and can't find reference to it still being a problem there.

edit: it appears to be a problem with PASTING the links. anything pasted doesn't stay linked after the sort. if I do them all individually, it appears to work. 300 lines.