hagmania 8,861 Posted December 7, 2015 Share Posted December 7, 2015 This is a such a frustrating problem:Using a data connection and trying to update its definitions. Click OK and nothing happens. Stone nothing. Go back a screen and try to add a new connection, click "Add..." and nothing. Wtf. What data source?This is more a quality of life issue, really, since I can just force it to change with VBA, but I've encountered this error in both the Connection Definition and the Command Definition, and again both OLEDB and ODBC, once even with just a straight SQL Server connection. Quote Link to post Share on other sites
Spin 1,014 Posted January 18, 2016 Share Posted January 18, 2016 Taking this is another direction. I accepted a new position and am about to start interviewing replacement candidates for my old position, which is pretty excel heavy with some pretty advanced excel stuff. Plenty of array formulas, complicated index/match look ups, about as "advanced" as excel formula can get really.Anyways, what are some good excel interview questions, that'll show if someone really understands some of the more complex excel stuff?This is also my first time interviewing someone else for a position, so I'm not trying to trip them up or "stump them" with annoying syntax related questions such as "What is the difference between search and find" etc. Because that stuff can easily be googled. I'm really looking for stuff that shows who really understands some of the more complex things excel can do.Like do I just show them a formula like this:{=IFERROR(INDEX(Data!$C$1:$CD$9661,SMALL(IF(IFERROR(SEARCH($B$1,Data!$AC$1:$AC$9661),"False"),ROW(Data!$BW$1:$BW$9661)),ROW(1:1)),1),"")}And say, what is this formula doing when copied down a column?Some questions I initially thought of:1) We generally can't use vlookups within our data set, even though the information is set up within columns, why do you think that is? - Do they understand the limitations of using vlookups2) What do brackets enclosing a formula indicate? -Have they ever used an array formula before3) What are some instances where Index/Match would be necessary instead of using a vlookup? -Have they used index/match before4) How could you do a look up against multiple criteria? - IE, return the value in col A, if Col B is x, col C is y, Col D is Z. etcAny other good ones? Quote Link to post Share on other sites
BroadwayG 963 Posted January 18, 2016 Share Posted January 18, 2016 What do you say when the interviewee asks 'Why don't you just use VBA?' 1 Quote Link to post Share on other sites
Ned 10,585 Posted January 20, 2016 Author Share Posted January 20, 2016 #5-What's a pivot table? Quote Link to post Share on other sites
ragincajun 904 Posted January 20, 2016 Share Posted January 20, 2016 Pivot tables organize and consolidate data.Simple example. If you have a list of 5,000 cars with colors listed you can create a pivot table and sort by car colors in a seperate table telling you how many colors of each car you have. Quote Link to post Share on other sites
ragincajun 904 Posted January 20, 2016 Share Posted January 20, 2016 Taking this is another direction. I accepted a new position and am about to start interviewing replacement candidates for my old position, which is pretty excel heavy with some pretty advanced excel stuff. Plenty of array formulas, complicated index/match look ups, about as "advanced" as excel formula can get really.Anyways, what are some good excel interview questions, that'll show if someone really understands some of the more complex excel stuff?This is also my first time interviewing someone else for a position, so I'm not trying to trip them up or "stump them" with annoying syntax related questions such as "What is the difference between search and find" etc. Because that stuff can easily be googled. I'm really looking for stuff that shows who really understands some of the more complex things excel can do.Like do I just show them a formula like this:{=IFERROR(INDEX(Data!$C$1:$CD$9661,SMALL(IF(IFERROR(SEARCH($B$1,Data!$AC$1:$AC$9661),"False"),ROW(Data!$BW$1:$BW$9661)),ROW(1:1)),1),"")}And say, what is this formula doing when copied down a column?Some questions I initially thought of:1) We generally can't use vlookups within our data set, even though the information is set up within columns, why do you think that is? - Do they understand the limitations of using vlookups2) What do brackets enclosing a formula indicate? -Have they ever used an array formula before3) What are some instances where Index/Match would be necessary instead of using a vlookup? -Have they used index/match before4) How could you do a look up against multiple criteria? - IE, return the value in col A, if Col B is x, col C is y, Col D is Z. etcAny other good ones? Ask if they know what a Fuzzy lookup is. Serious. Quote Link to post Share on other sites
ragincajun 904 Posted January 20, 2016 Share Posted January 20, 2016 Not sure if it will rule anyone out but if they can explain it then you have an Excel Yoda on your hands. Quote Link to post Share on other sites
Ignoratio Elenchi 2,446 Posted January 20, 2016 Share Posted January 20, 2016 Anyways, what are some good excel interview questions, that'll show if someone really understands some of the more complex excel stuff? This is also my first time interviewing someone else for a position, so I'm not trying to trip them up or "stump them" with annoying syntax related questions such as "What is the difference between search and find" etc. Because that stuff can easily be googled. I'm really looking for stuff that shows who really understands some of the more complex things excel can do. At one point when I interviewed for my last job a few years ago, the interviewer just handed me her laptop with an Excel file opened and asked me some questions about the data which I could use any method to answer. I don't remember the specifics but it was like a table of data, 10 columns and 100,000 rows or whatever, and she asked stuff like "how many customers have a join date before June 2010?", "what percentage of our total sales came from customers in NY?", etc. It was nonsense data, the point was just to see how comfortable I was with using Excel and how quickly and efficiently I could extract those kinds of answers from the data. I thought it was great. Rather than asking me boring questions about vlookup and index/match and PivotTables and whatnot, I was free to demonstrate that I knew how to use those things. 2 Quote Link to post Share on other sites
Ned 10,585 Posted January 20, 2016 Author Share Posted January 20, 2016 (edited) Anyways, what are some good excel interview questions, that'll show if someone really understands some of the more complex excel stuff?This is also my first time interviewing someone else for a position, so I'm not trying to trip them up or "stump them" with annoying syntax related questions such as "What is the difference between search and find" etc. Because that stuff can easily be googled. I'm really looking for stuff that shows who really understands some of the more complex things excel can do.At one point when I interviewed for my last job a few years ago, the interviewer just handed me her laptop with an Excel file opened and asked me some questions about the data which I could use any method to answer. I don't remember the specifics but it was like a table of data, 10 columns and 100,000 rows or whatever, and she asked stuff like "how many customers have a join date before June 2010?", "what percentage of our total sales came from customers in NY?", etc. It was nonsense data, the point was just to see how comfortable I was with using Excel and how quickly and efficiently I could extract those kinds of answers from the data. I thought it was great. Rather than asking me boring questions about vlookup and index/match and PivotTables and whatnot, I was free to demonstrate that I knew how to use those things.That's pretty awesome. I wish I had gotten that in one of my interviews. Edited January 20, 2016 by Ned Quote Link to post Share on other sites
dino259 614 Posted January 25, 2016 Share Posted January 25, 2016 Say I have 100 to 1000 rows of data. Is there an easy way to insert 10 or 11 empty rows in between them all? Quote Link to post Share on other sites
Binky The Doormat 12,819 Posted January 25, 2016 Share Posted January 25, 2016 Say I have 100 to 1000 rows of data. Is there an easy way to insert 10 or 11 empty rows in between them all?Probably clumsy, but I would just right click the row where I wanted to insert the rows and start with the first one and then copy and paste the others. First 2 then 4 ... Quote Link to post Share on other sites
TheIronSheik 8,167 Posted February 12, 2016 Share Posted February 12, 2016 OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually. How do I go about this? Quote Link to post Share on other sites
Walking Boot 8,296 Posted February 12, 2016 Share Posted February 12, 2016 OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually. How do I go about this? Can you switch the colons to commas and import it as a CSV? Or am I not understanding how you have the original data? Quote Link to post Share on other sites
TheIronSheik 8,167 Posted February 12, 2016 Share Posted February 12, 2016 OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually. How do I go about this? Can you switch the colons to commas and import it as a CSV? Or am I not understanding how you have the original data?No. It's pulling from an application we use. It's a huge sheet with lots of columns. I only use a certain amount of columns. So my goal is to copy the data into another Excel sheet. Then, in another tab, I have formulas set up to pull only the desired data I need and put it in the correct order. Each column needs some finessing to get it to what I want. I can then just copy that tab and enter it into the report Excel sheet. I do this for a lot of reports. Saves a lot of time.I've got this one down except for this last part. I need to take this one column and separate it into 4 unique cells. Quote Link to post Share on other sites
Ned 10,585 Posted February 12, 2016 Author Share Posted February 12, 2016 OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually. How do I go about this? Can you switch the colons to commas and import it as a CSV? Or am I not understanding how you have the original data?No. It's pulling from an application we use. It's a huge sheet with lots of columns. I only use a certain amount of columns. So my goal is to copy the data into another Excel sheet. Then, in another tab, I have formulas set up to pull only the desired data I need and put it in the correct order. Each column needs some finessing to get it to what I want. I can then just copy that tab and enter it into the report Excel sheet. I do this for a lot of reports. Saves a lot of time.I've got this one down except for this last part. I need to take this one column and separate it into 4 unique cells. This assumes the data always has a ' : ' (space, colon, space) between each value. Change the Sheet1!A1 reference to wherever your data is at.I threw it together quickly, so make sure to test it before you trust it. Cell 1: =TRIM(LEFT(Sheet1!A1,SEARCH(":",Sheet1!A1)-1))Cell 2: =TRIM(MID(Sheet1!A1,LEN(A1)+4,SEARCH(":",Sheet1!A1,LEN(A1)+2)))Cell 3 =TRIM(MID(Sheet1!A1,LEN(A1)+LEN(A1)+8,SEARCH(":",Sheet1!A1,LEN(A1)+2)))Cell 4: =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-LEN(A1)-LEN(B1)-LEN(C1)-9) Quote Link to post Share on other sites
Walking Boot 8,296 Posted February 12, 2016 Share Posted February 12, 2016 Yeah, I just did something like that last month I think. Looks like the basic idea. Quote Link to post Share on other sites
AcerFC 4,857 Posted February 12, 2016 Share Posted February 12, 2016 Is Excel and google sheets similar in what scripts you can writeI am interested in learning about formulas and scripts. Where would you suggest I go to learn. Quote Link to post Share on other sites
Ned 10,585 Posted February 12, 2016 Author Share Posted February 12, 2016 Google sheets is like Excel Lite. Some formulas are the same, but Excel is much more powerful. I don't think you can macro in Google Sheets, but I've never looked either.Get Excel and do some reading online. It's easy stuff.There are also some good trouble shooting tips burred in this thread if you want to read through it all. Quote Link to post Share on other sites
Insein 10,733 Posted February 12, 2016 Share Posted February 12, 2016 I'm an excel amateur. Well above the masses but still below expert level. I like checking in here to see what the experts like to do. Quote Link to post Share on other sites
TheIronSheik 8,167 Posted February 12, 2016 Share Posted February 12, 2016 OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually. How do I go about this? Can you switch the colons to commas and import it as a CSV? Or am I not understanding how you have the original data?No. It's pulling from an application we use. It's a huge sheet with lots of columns. I only use a certain amount of columns. So my goal is to copy the data into another Excel sheet. Then, in another tab, I have formulas set up to pull only the desired data I need and put it in the correct order. Each column needs some finessing to get it to what I want. I can then just copy that tab and enter it into the report Excel sheet. I do this for a lot of reports. Saves a lot of time.I've got this one down except for this last part. I need to take this one column and separate it into 4 unique cells. This assumes the data always has a ' : ' (space, colon, space) between each value. Change the Sheet1!A1 reference to wherever your data is at.I threw it together quickly, so make sure to test it before you trust it. Cell 1: =TRIM(LEFT(Sheet1!A1,SEARCH(":",Sheet1!A1)-1))Cell 2: =TRIM(MID(Sheet1!A1,LEN(A1)+4,SEARCH(":",Sheet1!A1,LEN(A1)+2)))Cell 3 =TRIM(MID(Sheet1!A1,LEN(A1)+LEN(A1)+8,SEARCH(":",Sheet1!A1,LEN(A1)+2)))Cell 4: =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-LEN(A1)-LEN(B1)-LEN(C1)-9)What are the A1, B1 and C1's in your formulas above? Quote Link to post Share on other sites
Ned 10,585 Posted February 12, 2016 Author Share Posted February 12, 2016 OK, I'm having trouble with this and I know it should be easy. Normally, I'd get my solution to this by using Text to Columns and just doing it manually. But I'm trying to create a spreadsheet that will do the work for me. Here's my issue:I have a cell that has info in it listed like this adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2There is no rhyme or reason to the length of each quartet. I'd like to be able to separate that single cell into 4 unique cells with each entry between the colons, not including the colons. Like I said, normally I would just use Text to Columns. But my goal is to be able to just throw raw data into an Excel sheet and have it spit out what I need on another tab. Totally free of me having to do anything manually. How do I go about this? Can you switch the colons to commas and import it as a CSV? Or am I not understanding how you have the original data?No. It's pulling from an application we use. It's a huge sheet with lots of columns. I only use a certain amount of columns. So my goal is to copy the data into another Excel sheet. Then, in another tab, I have formulas set up to pull only the desired data I need and put it in the correct order. Each column needs some finessing to get it to what I want. I can then just copy that tab and enter it into the report Excel sheet. I do this for a lot of reports. Saves a lot of time.I've got this one down except for this last part. I need to take this one column and separate it into 4 unique cells. This assumes the data always has a ' : ' (space, colon, space) between each value. Change the Sheet1!A1 reference to wherever your data is at.I threw it together quickly, so make sure to test it before you trust it. Cell 1: =TRIM(LEFT(Sheet1!A1,SEARCH(":",Sheet1!A1)-1))Cell 2: =TRIM(MID(Sheet1!A1,LEN(A1)+4,SEARCH(":",Sheet1!A1,LEN(A1)+2)))Cell 3 =TRIM(MID(Sheet1!A1,LEN(A1)+LEN(A1)+8,SEARCH(":",Sheet1!A1,LEN(A1)+2)))Cell 4: =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-LEN(A1)-LEN(B1)-LEN(C1)-9)What are the A1, B1 and C1's in your formulas above?Sorry, I should've told you that. They're referencing the length of the previous value. So Cell 2 is using the length of the first value (adrarae) to help figure out the length and location of value 2.So when you paste these formulas into your sheet, you need to update those references to match. So if you pasted these in columns L, M, N, and O then you'd need to update A1 to L1, B1 to M1, and C1 to N1. Don't do this for the 'Sheet1!A1' references though. That's for your source data only (as noted above).Clear as mud! Quote Link to post Share on other sites
TheIronSheik 8,167 Posted February 12, 2016 Share Posted February 12, 2016 Sorry, I should've told you that. They're referencing the length of the previous value. So Cell 2 is using the length of the first value (adrarae) to help figure out the length and location of value 2.So when you paste these formulas into your sheet, you need to update those references to match. So if you pasted these in columns L, M, N, and O then you'd need to update A1 to L1, B1 to M1, and C1 to N1. Don't do this for the 'Sheet1!A1' references though. That's for your source data only (as noted above).Clear as mud!OK. That's what I thought. Which means it's not working for me. First column comes out fine. Next column, not so much. Sometimes I get 2 quartets in the cell. Sometimes I get 2 and a half quartets. My first guess is that I'm doing something wrong, as I have total trust in you. But I'm pretty much just copying and pasting, then changing the A1's and such. Quote Link to post Share on other sites
Ignoratio Elenchi 2,446 Posted February 12, 2016 Share Posted February 12, 2016 Sorry, I should've told you that. They're referencing the length of the previous value. So Cell 2 is using the length of the first value (adrarae) to help figure out the length and location of value 2. So when you paste these formulas into your sheet, you need to update those references to match. So if you pasted these in columns L, M, N, and O then you'd need to update A1 to L1, B1 to M1, and C1 to N1. Don't do this for the 'Sheet1!A1' references though. That's for your source data only (as noted above). Clear as mud!OK. That's what I thought. Which means it's not working for me. First column comes out fine. Next column, not so much. Sometimes I get 2 quartets in the cell. Sometimes I get 2 and a half quartets. My first guess is that I'm doing something wrong, as I have total trust in you. But I'm pretty much just copying and pasting, then changing the A1's and such. Something like this should work: =TRIM(LEFT($A1,FIND(":",$A1)-2)) =TRIM(MID($A1,FIND(":",$A1)+1,FIND(":",$A1,FIND(":",$A1)+1)-FIND(":",$A1)-1)) =TRIM(MID($A1,FIND(":",$A1,FIND(":",$A1)+1)+1,FIND(":",$A1,FIND(":",$A1,FIND(":",$A1)+1)+1)-FIND(":",$A1,FIND(":",$A1)+1)-1)) =TRIM(RIGHT($A1,LEN($A1)-FIND(":",$A1,FIND(":",$A1,FIND(":",$A1)+1)+1))) It's ugly, but once it's done it's done. The alternative is to write something more robust in VBA, which may or may not be overkill. Quote Link to post Share on other sites
Walking Boot 8,296 Posted February 12, 2016 Share Posted February 12, 2016 I'm sure there's a better way, but:In cell A1, the text "adrarae : arzfdbvae : 32rgewfd : wdewve4fgsww2"In B1, =LEFT(A1,(FIND(" : ",A1)-1)) (returns "adrarae")In C1, =RIGHT(A1,LEN(A1)-SEARCH(" : ",A1,1)-2) (returns "arzfdbvae : 32rgewfd : wdewve4fgsww2"In D1, =LEFT(C1,(FIND(" : ",C1)-1)) (returns "arzfdbvae")In E1, =RIGHT(C1,LEN(C1)-SEARCH(" : ",C1,1)-2) (returns "32rgewfd : wdewve4fgsww2")In F1, =LEFT(E1,(FIND(" : ",E1)-1)) (returns "32rgewfd")In G1, =RIGHT(E1,LEN(E1)-SEARCH(" : ",E1,1)-2) (returns "wdewve4fgsww2")Hide columns C & E.I know there's a better way to do that, but it'd take some digging. Quote Link to post Share on other sites
TheIronSheik 8,167 Posted February 12, 2016 Share Posted February 12, 2016 Thanks, everyone! I'll have to give these a try Monday morning. I don't mind if they're overkill. Most of my formulas end up being like that anyway. In the long run, it works out to be time saving. I run these reports each week and by doing it this way, it saves me hours of work. Quote Link to post Share on other sites
Brony 7,619 Posted February 12, 2016 Share Posted February 12, 2016 "wdewve4fgsww2" was my high school nickname. Quote Link to post Share on other sites
Walking Boot 8,296 Posted February 12, 2016 Share Posted February 12, 2016 Yeah, Ig's way is the complex formula method I'm not versed enough in excel to figure out without help. Cleaner than my helper-column method. Quote Link to post Share on other sites
Ned 10,585 Posted February 13, 2016 Author Share Posted February 13, 2016 Thanks, everyone! I'll have to give these a try Monday morning. I don't mind if they're overkill. Most of my formulas end up being like that anyway. In the long run, it works out to be time saving. I run these reports each week and by doing it this way, it saves me hours of work.Was your example an exact layout? It worked when I wrote it. Quote Link to post Share on other sites
VA703 924 Posted March 22, 2016 Share Posted March 22, 2016 is there a formula or rule I can apply that will change all cells with the value of 0 to a certain background color? Quote Link to post Share on other sites
BroadwayG 963 Posted March 22, 2016 Share Posted March 22, 2016 Conditional formatting Quote Link to post Share on other sites
Uruk-Hai 7,557 Posted March 28, 2016 Share Posted March 28, 2016 (edited) I have an Excel file with a bunch of tabbed sheets inside of it. The tabs have names on them. Is there a way I can sort the tabs alphabetically? eta: If not, how can I insert a tab in the correct spot alphabetically? It seems I can only add a new tab at the end of the tab row. Edited March 28, 2016 by Uruk-Hai Quote Link to post Share on other sites
acarey50 610 Posted March 28, 2016 Share Posted March 28, 2016 3 hours ago, Uruk-Hai said: I have an Excel file with a bunch of tabbed sheets inside of it. The tabs have names on them. Is there a way I can sort the tabs alphabetically? eta: If not, how can I insert a tab in the correct spot alphabetically? It seems I can only add a new tab at the end of the tab row. There is no built in way to sort alphabetically, but this macro from Microsoft should sort all tabs alphabetically. https://support.microsoft.com/en-us/kb/812386 As for inserting the tab into the correct spot, simply right click on the tab name to the right of where you want to insert a new tab, click on insert and then click on "Worksheet", and you will get a new blank tab to the right of the tab you right clicked on 1 Quote Link to post Share on other sites
Mr. Ected 2,899 Posted March 28, 2016 Share Posted March 28, 2016 Using Excel 2011 and 2016, having issues with time and dates. When I go to put the time ( [cmd] + [shift] + [;] ), I have no problems. When I try to input a date ( [cmd] + [;] ) I get the time also. Very strange. Quote Link to post Share on other sites
Insein 10,733 Posted April 11, 2016 Share Posted April 11, 2016 (edited) I've exhausted my mental capabilities on Google search and can't figure out this simple error. I keep getting "Insert into statement contains the following unknown field: 'F1'" for the VBA code I have. I'm trying to do a simple append of excel data to an existing access table. That error would suggest that one of my field names is wrong but everyone of them is EXACTLY the same as the DB. I also tried to see if there were hidden fields or extra data beyond the main but nothing else exists. I'm stumped. Anyone else run into this before? Quote Set cn = CreateObject("ADODB.Connection") dbPath = "S:\Compliance Reporting\Databases\Records Review Database.accdb" dbWb = RFile dbWs = "Sheet1" scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath dsh = "[" & dbWs & "$]" cn.Open scn ssql = "INSERT INTO tblSalesRoster " ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=Yes;DATABASE=" & dbWb & "]." & dsh cn.Execute ssql cn.Close Edited April 11, 2016 by Insein Quote Link to post Share on other sites
bcdjr1 687 Posted April 12, 2016 Share Posted April 12, 2016 7 hours ago, Insein said: I've exhausted my mental capabilities on Google search and can't figure out this simple error. I keep getting "Insert into statement contains the following unknown field: 'F1'" for the VBA code I have. I'm trying to do a simple append of excel data to an existing access table. That error would suggest that one of my field names is wrong but everyone of them is EXACTLY the same as the DB. I also tried to see if there were hidden fields or extra data beyond the main but nothing else exists. I'm stumped. Anyone else run into this before? Can you put a sample of the table you're trying to insert into and the sheet you're inserting from? If I had to guess, I would think that you may be trying to insert more fields from the worksheet than you have fields in your table. Quote Link to post Share on other sites
Insein 10,733 Posted April 12, 2016 Share Posted April 12, 2016 8 minutes ago, bcdjr1 said: Can you put a sample of the table you're trying to insert into and the sheet you're inserting from? If I had to guess, I would think that you may be trying to insert more fields from the worksheet than you have fields in your table. I thought that too. I'll double check again tomorrow but I copied a line from the table and pasted into excel to match up the headers and they were exact, even spelling. Which makes sense since I built the original table from one of these files. Quote Link to post Share on other sites
bcdjr1 687 Posted April 12, 2016 Share Posted April 12, 2016 7 hours ago, Insein said: I thought that too. I'll double check again tomorrow but I copied a line from the table and pasted into excel to match up the headers and they were exact, even spelling. Which makes sense since I built the original table from one of these files. You aren't specifying a set of columns from the worksheet in your code, so I'm thinking that maybe it's picking up an extra column and then failing when trying to load that into your table. Quote Link to post Share on other sites
Thorpe 221 Posted April 12, 2016 Share Posted April 12, 2016 Change it to this, which will create the table. ssql = "SELECT * INTO tblSalesRoster_tmp FROM [Excel 8.0;HDR=Yes;DATABASE=" & dbWb & "]." & dsh Then compare tblSalesRoster_tmp with tblSalesRoster. You should then list the field names in the Select and the Insert. 1 Quote Link to post Share on other sites
Insein 10,733 Posted April 12, 2016 Share Posted April 12, 2016 2 hours ago, Thorpe said: Change it to this, which will create the table. ssql = "SELECT * INTO tblSalesRoster_tmp FROM [Excel 8.0;HDR=Yes;DATABASE=" & dbWb & "]." & dsh Then compare tblSalesRoster_tmp with tblSalesRoster. You should then list the field names in the Select and the Insert. I'll give this a shot this afternoon. Quote Link to post Share on other sites
Insein 10,733 Posted April 12, 2016 Share Posted April 12, 2016 4 hours ago, Thorpe said: Change it to this, which will create the table. ssql = "SELECT * INTO tblSalesRoster_tmp FROM [Excel 8.0;HDR=Yes;DATABASE=" & dbWb & "]." & dsh Then compare tblSalesRoster_tmp with tblSalesRoster. You should then list the field names in the Select and the Insert. So this found my problem. The source file has the top 2 rows as blank space with one cell having text in b2. In my macro, I select the rows, clear all and then delete them. The saved file has no space in the top two rows. I save and close. Yet when I send it to access, the table has the first 2 rows as blank aside from the one cell. Now I'll have to figure out how to fix this. Quote Link to post Share on other sites
Gawain 2,251 Posted May 19, 2016 Share Posted May 19, 2016 I have a list of models. I receive a list of all models with on hands for each model by store. There are a couple hundred stores. I want to count the number of stores that have on-hands below a minimum threshold. I'm trying to COUNTIF an INDEX/MATCH, but struggling. Any suggestions? Quote Link to post Share on other sites
Insein 10,733 Posted May 19, 2016 Share Posted May 19, 2016 21 minutes ago, Gawain said: I have a list of models. I receive a list of all models with on hands for each model by store. There are a couple hundred stores. I want to count the number of stores that have on-hands below a minimum threshold. I'm trying to COUNTIF an INDEX/MATCH, but struggling. Any suggestions? What are the headers in your list? Model-Amount-Store Quote Link to post Share on other sites
Ned 10,585 Posted May 19, 2016 Author Share Posted May 19, 2016 40 minutes ago, Gawain said: I have a list of models. I receive a list of all models with on hands for each model by store. There are a couple hundred stores. I want to count the number of stores that have on-hands below a minimum threshold. I'm trying to COUNTIF an INDEX/MATCH, but struggling. Any suggestions? Create a pivot table and then use the filters (in the pivot) to analyze what you want. Quote Link to post Share on other sites
Gawain 2,251 Posted May 20, 2016 Share Posted May 20, 2016 3 hours ago, Insein said: What are the headers in your list? Model-Amount-Store Pretty much: Model-Pcs on hand-Store (and a few hundred other columns) against a static model list. I can use a pivot table, but I either have to manipulate the way the data comes, or select everything. Quote Link to post Share on other sites
bcdjr1 687 Posted May 20, 2016 Share Posted May 20, 2016 (edited) Let's say A is Model, B is Pcs on hand, and C is Store. x will be your threshold. y is the number of rows on your sheet. You will have to substitute for x and y in your code. =IF(B1 < x, C1, "") goes in column D. If your on-hand is less than your threshold, you'll get your store number or name, if not you'll get a blank. Then in a single cell at the bottom, put =SUM(IF(FREQUENCY(IF(LEN(D1:Dy)>0,MATCH(D1:Dy,D1:Dy,0),""), IF(LEN(D1:Dy)>0,MATCH(D1:Dy,D1:Dy,0),""))>0,1)) The last function will be an array formula, so after you put it in you'll get #VALUE!, and you'll have to go to the cell, hit F2, and then CTRL+SHIFT+ENTER. I got the last part of this from this URL on the office help pages. It has an explanation of how it works there. Edited May 20, 2016 by bcdjr1 1 Quote Link to post Share on other sites
Gawain 2,251 Posted May 20, 2016 Share Posted May 20, 2016 Thanks, I'll give it a whirl when I get in tomorrow. Quote Link to post Share on other sites
Ned 10,585 Posted May 20, 2016 Author Share Posted May 20, 2016 10 hours ago, Gawain said: Pretty much: Model-Pcs on hand-Store (and a few hundred other columns) against a static model list. I can use a pivot table, but I either have to manipulate the way the data comes, or select everything. I misread what you were looking to do. @bcdjr1's solution is great. Turn on filters if you wanted to look at the list of stores (filter on the helper column D) that are under-stocked. Quote Link to post Share on other sites
Gawain 2,251 Posted May 23, 2016 Share Posted May 23, 2016 I wasn't able to get the array function to work, but I revisited the Pivot Table idea and was able to make this work smoothly. (countif off of the table) Thanks for the help! 1 Quote Link to post Share on other sites
Insein 10,733 Posted May 24, 2016 Share Posted May 24, 2016 I usually avoid the formulas. Just my preference. I like doing it all with VBA because it takes up less space when you save. If it's small, that doesn't matter but when you get up to 10k+ lines, that many formulas can balloon a File size. Quote Link to post Share on other sites
roadkill1292 10,465 Posted May 24, 2016 Share Posted May 24, 2016 On 3/28/2016 at 7:36 AM, Uruk-Hai said: I have an Excel file with a bunch of tabbed sheets inside of it. The tabs have names on them. Is there a way I can sort the tabs alphabetically? eta: If not, how can I insert a tab in the correct spot alphabetically? It seems I can only add a new tab at the end of the tab row. Is it too simple to suggest doing Move or Copy? Right click on the tab and you can place it wherever you want. Every time I try to answer one of these help questions I find out I'm actually years behind what everybody else already knows and assumes. I have no idea where your Excel skills are, gb, just trying to help. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.