***Official*** Excel Help Corner

[hagmania 8,861]

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.

[Spin 1,014]

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 vlookups

2) What do brackets enclosing a formula indicate? -Have they ever used an array formula before

3) What are some instances where Index/Match would be necessary instead of using a vlookup? -Have they used index/match before

4) 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. etc

Any other good ones?

[BroadwayG 963]

What do you say when the interviewee asks 'Why don't you just use VBA?'

[Ned 10,585]

#5-What's a pivot table?

[ragincajun 904]

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.

[ragincajun 904]

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 vlookups

2) What do brackets enclosing a formula indicate? -Have they ever used an array formula before

3) What are some instances where Index/Match would be necessary instead of using a vlookup? -Have they used index/match before

4) 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. etc

Any other good ones?

Ask if they know what a Fuzzy lookup is. Serious.

[ragincajun 904]

Not sure if it will rule anyone out but if they can explain it then you have an Excel Yoda on your hands.

[Ignoratio Elenchi 2,446]

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.

[Ned 10,585]

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

[dino259 614]

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?

[Binky The Doormat 12,819]

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 ...

[TheIronSheik 8,167]

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 : wdewve4fgsww2

There 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?

[Walking Boot 8,296]

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 : wdewve4fgsww2

There 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?

[TheIronSheik 8,167]

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 : wdewve4fgsww2

There 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.

[Ned 10,585]

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 : wdewve4fgsww2

There 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)

[Walking Boot 8,296]

Yeah, I just did something like that last month I think. Looks like the basic idea.

[AcerFC 4,857]

Is Excel and google sheets similar in what scripts you can write

I am interested in learning about formulas and scripts. Where would you suggest I go to learn.

[Ned 10,585]

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.

[Insein 10,733]

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.

[TheIronSheik 8,167]

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 : wdewve4fgsww2

There 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?

[Ned 10,585]

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 : wdewve4fgsww2

There 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!

[TheIronSheik 8,167]

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.

[Ignoratio Elenchi 2,446]

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.

[Walking Boot 8,296]

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.

[TheIronSheik 8,167]

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.

[Brony 7,619]

"wdewve4fgsww2" was my high school nickname.

[Walking Boot 8,296]

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.

[Ned 10,585]

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.

[VA703 924]

is there a formula or rule I can apply that will change all cells with the value of 0 to a certain background color?

[BroadwayG 963]

Conditional formatting

[Uruk-Hai 7,557]

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

[acarey50 610]

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

[Mr. Ected 2,899]

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.

[Insein 10,733]

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

[bcdjr1 687]

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.

[Insein 10,733]

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.

[bcdjr1 687]

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.

[Thorpe 221]

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.

 

 

[Insein 10,733]

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.

[Insein 10,733]

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.

[Gawain 2,251]

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?

[Insein 10,733]

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

[Ned 10,585]

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. 

[Gawain 2,251]

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.

[bcdjr1 687]

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

[Gawain 2,251]

Thanks, I'll give it a whirl when I get in tomorrow.

[Ned 10,585]

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. 

 

 

[Gawain 2,251]

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!

[Insein 10,733]

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.

[roadkill1292 10,465]

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.