Re: VLOOKUP vs Macros
2013/10/1 John Meyer johnme...@pueblocomputing.com Okay, I've put up two screenshots. Where? You forgot the link to them. Or did you attach them? You can't do that, they will be stripped off. Upload them somewhere and give us the link. Johnny Rosenberg the first contains the formula and what is listed in C1. The next is the names of the individual sheets. On 10/1/2013 8:21 AM, John Meyer wrote: Same Err:501. I'll post up more in the office. On 10/1/2013 8:11 AM, Johnny Rosenberg wrote: 2013/9/30 John Meyer johnme...@pueblocomputing.com Still doesn't work. Back to the drawing board. Exactly what does your current formula look like and what error message do you get? Still 501? Johnny Rosenberg On Mon, Sep 30, 2013 at 2:32 PM, John Meyer johnme...@pueblocomputing.comwrote: Derp, just saw that. Thanks. On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg gurus.knu...@gmail.comwrote: 2013/9/30 John Meyer johnme...@pueblocomputing.com Sorry, that e-mail got sent off too quickly. I'm trying this formula =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) C1 contains the name of the worksheet (09-27-13) and I'm trying to use it. However, it keeps giving me an err:501. Hint 1: Use the builtin help and search for 501. It will take you to the error codes. 501 means something like ”invalid character”. In this case it seems like you use ”,” instead of ”;” as parameter separators. I thought that there was a setting for that somewhere, but now I can't find it, so I guess that is a LibreOffice feature, but I'm not sure. I used LibreOffice for a couple of years but I am back with Apache OpenOffice again, since LibreOffice was way too unstable for me. It actually destroyed one of my spreadsheets but Apache OpenOffice fixed it for me, that's why I'm back… :D Anyway, replace those commas with semi-colons and I think it will work. Hint 2: If you are working with a big formula and it doesn't work, hit Ctrl+F2 (select the cell that you are working with first) and you are able to study your formula a little better. For instance you can see sub values by placing the cursor on different places in the formula. That way it's a little easier to find WHERE the error is. Regards Johnny Rosenberg On Mon, Sep 30, 2013 at 1:22 PM, John Meyer johnme...@pueblocomputing.comwrote: =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) On Mon, Sep 30, 2013 at 1:18 PM, John Meyer johnme...@pueblocomputing.com wrote: So I guess I'm doing something wrong here. =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) Returns an Err:501. On Sun, Sep 29, 2013 at 10:59 PM, Coreurus coreu...@aol.com wrote: - Original Message --(Start-looking-by-there) From: John Meyer To: users@openoffice.apache.org Sent: Saturday, 28 September, 2013 04:40 PM Subject: Re: VLOOKUP vs Macros I sent that a little too soon. The title refers to the fact that I was looking into another solution. the sales sheet for each day is broken down with the following information: Date, EmpID Currently, what I am doing is separating them by date. However, I was thinking a much less cluttered solution would involve pulling the date from the bonus calculation field and then doing either a VLOOKUP with a count or Macro. Where would I start looking if I wanted to do either one of those solutions? Back to searching for the answer. - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: VLOOKUP vs Macros
2013/9/30 John Meyer johnme...@pueblocomputing.com Still doesn't work. Back to the drawing board. Exactly what does your current formula look like and what error message do you get? Still 501? Johnny Rosenberg On Mon, Sep 30, 2013 at 2:32 PM, John Meyer johnme...@pueblocomputing.comwrote: Derp, just saw that. Thanks. On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg gurus.knu...@gmail.comwrote: 2013/9/30 John Meyer johnme...@pueblocomputing.com Sorry, that e-mail got sent off too quickly. I'm trying this formula =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) C1 contains the name of the worksheet (09-27-13) and I'm trying to use it. However, it keeps giving me an err:501. Hint 1: Use the builtin help and search for 501. It will take you to the error codes. 501 means something like ”invalid character”. In this case it seems like you use ”,” instead of ”;” as parameter separators. I thought that there was a setting for that somewhere, but now I can't find it, so I guess that is a LibreOffice feature, but I'm not sure. I used LibreOffice for a couple of years but I am back with Apache OpenOffice again, since LibreOffice was way too unstable for me. It actually destroyed one of my spreadsheets but Apache OpenOffice fixed it for me, that's why I'm back… :D Anyway, replace those commas with semi-colons and I think it will work. Hint 2: If you are working with a big formula and it doesn't work, hit Ctrl+F2 (select the cell that you are working with first) and you are able to study your formula a little better. For instance you can see sub values by placing the cursor on different places in the formula. That way it's a little easier to find WHERE the error is. Regards Johnny Rosenberg On Mon, Sep 30, 2013 at 1:22 PM, John Meyer johnme...@pueblocomputing.comwrote: =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) On Mon, Sep 30, 2013 at 1:18 PM, John Meyer johnme...@pueblocomputing.com wrote: So I guess I'm doing something wrong here. =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) Returns an Err:501. On Sun, Sep 29, 2013 at 10:59 PM, Coreurus coreu...@aol.com wrote: - Original Message --(Start-looking-by-there) From: John Meyer To: users@openoffice.apache.org Sent: Saturday, 28 September, 2013 04:40 PM Subject: Re: VLOOKUP vs Macros I sent that a little too soon. The title refers to the fact that I was looking into another solution. the sales sheet for each day is broken down with the following information: Date, EmpID Currently, what I am doing is separating them by date. However, I was thinking a much less cluttered solution would involve pulling the date from the bonus calculation field and then doing either a VLOOKUP with a count or Macro. Where would I start looking if I wanted to do either one of those solutions? Back to searching for the answer. - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: VLOOKUP vs Macros
Same Err:501. I'll post up more in the office. On 10/1/2013 8:11 AM, Johnny Rosenberg wrote: 2013/9/30 John Meyer johnme...@pueblocomputing.com Still doesn't work. Back to the drawing board. Exactly what does your current formula look like and what error message do you get? Still 501? Johnny Rosenberg On Mon, Sep 30, 2013 at 2:32 PM, John Meyer johnme...@pueblocomputing.comwrote: Derp, just saw that. Thanks. On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg gurus.knu...@gmail.comwrote: 2013/9/30 John Meyer johnme...@pueblocomputing.com Sorry, that e-mail got sent off too quickly. I'm trying this formula =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) C1 contains the name of the worksheet (09-27-13) and I'm trying to use it. However, it keeps giving me an err:501. Hint 1: Use the builtin help and search for 501. It will take you to the error codes. 501 means something like ”invalid character”. In this case it seems like you use ”,” instead of ”;” as parameter separators. I thought that there was a setting for that somewhere, but now I can't find it, so I guess that is a LibreOffice feature, but I'm not sure. I used LibreOffice for a couple of years but I am back with Apache OpenOffice again, since LibreOffice was way too unstable for me. It actually destroyed one of my spreadsheets but Apache OpenOffice fixed it for me, that's why I'm back… :D Anyway, replace those commas with semi-colons and I think it will work. Hint 2: If you are working with a big formula and it doesn't work, hit Ctrl+F2 (select the cell that you are working with first) and you are able to study your formula a little better. For instance you can see sub values by placing the cursor on different places in the formula. That way it's a little easier to find WHERE the error is. Regards Johnny Rosenberg On Mon, Sep 30, 2013 at 1:22 PM, John Meyer johnme...@pueblocomputing.comwrote: =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) On Mon, Sep 30, 2013 at 1:18 PM, John Meyer johnme...@pueblocomputing.com wrote: So I guess I'm doing something wrong here. =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) Returns an Err:501. On Sun, Sep 29, 2013 at 10:59 PM, Coreurus coreu...@aol.com wrote: - Original Message --(Start-looking-by-there) From: John Meyer To: users@openoffice.apache.org Sent: Saturday, 28 September, 2013 04:40 PM Subject: Re: VLOOKUP vs Macros I sent that a little too soon. The title refers to the fact that I was looking into another solution. the sales sheet for each day is broken down with the following information: Date, EmpID Currently, what I am doing is separating them by date. However, I was thinking a much less cluttered solution would involve pulling the date from the bonus calculation field and then doing either a VLOOKUP with a count or Macro. Where would I start looking if I wanted to do either one of those solutions? Back to searching for the answer. - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: VLOOKUP vs Macros
Okay, I've put up two screenshots. the first contains the formula and what is listed in C1. The next is the names of the individual sheets. On 10/1/2013 8:21 AM, John Meyer wrote: Same Err:501. I'll post up more in the office. On 10/1/2013 8:11 AM, Johnny Rosenberg wrote: 2013/9/30 John Meyer johnme...@pueblocomputing.com Still doesn't work. Back to the drawing board. Exactly what does your current formula look like and what error message do you get? Still 501? Johnny Rosenberg On Mon, Sep 30, 2013 at 2:32 PM, John Meyer johnme...@pueblocomputing.comwrote: Derp, just saw that. Thanks. On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg gurus.knu...@gmail.comwrote: 2013/9/30 John Meyer johnme...@pueblocomputing.com Sorry, that e-mail got sent off too quickly. I'm trying this formula =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) C1 contains the name of the worksheet (09-27-13) and I'm trying to use it. However, it keeps giving me an err:501. Hint 1: Use the builtin help and search for 501. It will take you to the error codes. 501 means something like ”invalid character”. In this case it seems like you use ”,” instead of ”;” as parameter separators. I thought that there was a setting for that somewhere, but now I can't find it, so I guess that is a LibreOffice feature, but I'm not sure. I used LibreOffice for a couple of years but I am back with Apache OpenOffice again, since LibreOffice was way too unstable for me. It actually destroyed one of my spreadsheets but Apache OpenOffice fixed it for me, that's why I'm back… :D Anyway, replace those commas with semi-colons and I think it will work. Hint 2: If you are working with a big formula and it doesn't work, hit Ctrl+F2 (select the cell that you are working with first) and you are able to study your formula a little better. For instance you can see sub values by placing the cursor on different places in the formula. That way it's a little easier to find WHERE the error is. Regards Johnny Rosenberg On Mon, Sep 30, 2013 at 1:22 PM, John Meyer johnme...@pueblocomputing.comwrote: =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) On Mon, Sep 30, 2013 at 1:18 PM, John Meyer johnme...@pueblocomputing.com wrote: So I guess I'm doing something wrong here. =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) Returns an Err:501. On Sun, Sep 29, 2013 at 10:59 PM, Coreurus coreu...@aol.com wrote: - Original Message --(Start-looking-by-there) From: John Meyer To: users@openoffice.apache.org Sent: Saturday, 28 September, 2013 04:40 PM Subject: Re: VLOOKUP vs Macros I sent that a little too soon. The title refers to the fact that I was looking into another solution. the sales sheet for each day is broken down with the following information: Date, EmpID Currently, what I am doing is separating them by date. However, I was thinking a much less cluttered solution would involve pulling the date from the bonus calculation field and then doing either a VLOOKUP with a count or Macro. Where would I start looking if I wanted to do either one of those solutions? Back to searching for the answer. - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: VLOOKUP vs Macros
Still doesn't work. Back to the drawing board. On Mon, Sep 30, 2013 at 2:32 PM, John Meyer johnme...@pueblocomputing.comwrote: Derp, just saw that. Thanks. On Mon, Sep 30, 2013 at 2:01 PM, Johnny Rosenberg gurus.knu...@gmail.comwrote: 2013/9/30 John Meyer johnme...@pueblocomputing.com Sorry, that e-mail got sent off too quickly. I'm trying this formula =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) C1 contains the name of the worksheet (09-27-13) and I'm trying to use it. However, it keeps giving me an err:501. Hint 1: Use the builtin help and search for 501. It will take you to the error codes. 501 means something like ”invalid character”. In this case it seems like you use ”,” instead of ”;” as parameter separators. I thought that there was a setting for that somewhere, but now I can't find it, so I guess that is a LibreOffice feature, but I'm not sure. I used LibreOffice for a couple of years but I am back with Apache OpenOffice again, since LibreOffice was way too unstable for me. It actually destroyed one of my spreadsheets but Apache OpenOffice fixed it for me, that's why I'm back… :D Anyway, replace those commas with semi-colons and I think it will work. Hint 2: If you are working with a big formula and it doesn't work, hit Ctrl+F2 (select the cell that you are working with first) and you are able to study your formula a little better. For instance you can see sub values by placing the cursor on different places in the formula. That way it's a little easier to find WHERE the error is. Regards Johnny Rosenberg On Mon, Sep 30, 2013 at 1:22 PM, John Meyer johnme...@pueblocomputing.comwrote: =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) On Mon, Sep 30, 2013 at 1:18 PM, John Meyer johnme...@pueblocomputing.com wrote: So I guess I'm doing something wrong here. =COUNTIF(INDIRECT(ADDRESS(1,3,1,,Bonuses)).$E$1:$E2000,A2) Returns an Err:501. On Sun, Sep 29, 2013 at 10:59 PM, Coreurus coreu...@aol.com wrote: - Original Message --(Start-looking-by-there) From: John Meyer To: users@openoffice.apache.org Sent: Saturday, 28 September, 2013 04:40 PM Subject: Re: VLOOKUP vs Macros I sent that a little too soon. The title refers to the fact that I was looking into another solution. the sales sheet for each day is broken down with the following information: Date, EmpID Currently, what I am doing is separating them by date. However, I was thinking a much less cluttered solution would involve pulling the date from the bonus calculation field and then doing either a VLOOKUP with a count or Macro. Where would I start looking if I wanted to do either one of those solutions? Back to searching for the answer. - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: VLOOKUP vs Macros
=ADDRESS(1, 3, 1,, sheet1) returns SHEET1.$C$1 =INDIRECT(ADDRESS(1, 3, 1,, sheet1)) returns whatever is stored in the cell referenced by SHEET1.$C$1 On 09/28/2013 11:30 AM, John Meyer wrote: I have a spreadsheet with a number of worksheets. The first worksheet has the bonuses for employees in this format ID, Sales Agent, Sales For Date, Bonus for Date, Sales for Date(N), Bonus For Date (N). . . The rest of the spreadsheets are each named for the date of sales that they contain. They are in the same format as the column header for Sales For Date. I set up each row like this ID static, Agent name static, =COUNTIF(*staticdateworksheet*!ColumnWithIDFirstRow:ColumnWithIDNthRow,Firstcolumnrowiamin),BonusCalculation What I'd like to do is change that formula around to resolve the sheet name by the first row of the salescount ID (i.e. if It's 9-27-2013 it would pull up 9-27-2013, 9-28-2013, etc.). Is this possible? It's not a pressing issue, I'd just like to see if it could be done. Thanks. - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org -- Andrew Pitonyak My Macro Document: http://www.pitonyak.org/AndrewMacro.odt Info: http://www.pitonyak.org/oo.php - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
Re: VLOOKUP vs Macros
I sent that a little too soon. The title refers to the fact that I was looking into another solution. the sales sheet for each day is broken down with the following information: Date, EmpID Currently, what I am doing is separating them by date. However, I was thinking a much less cluttered solution would involve pulling the date from the bonus calculation field and then doing either a VLOOKUP with a count or Macro. Where would I start looking if I wanted to do either one of those solutions? Back to searching for the answer. - To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
