stSQL1 = "SELECT * FROM serviceinfo"
The "*" means select all data in a sql statement. If you dont want them all then you need to do something like:
'strSQL1= "SELECT [TABLE1], [TABLE2] FROM [ServiceInfo]"
that will give you the data of table1 and table2 (of course you would use the...
Glad it helps. About 90% of what I learned, I learned from here
regarding the formula from luke, basically it comes down to that if a cell has #N/A excel will just ingore it. So could use the NA() function in other forumals
Take a look and see if that helps you at all. There are two ways that I can see go about accomplishing, what I think you want
https://www.dropbox.com/s/qosk0anzrcfsc48/hide%20Chart.xlsm
just some quick observations:
1) Near the end of the code, where you color the cells, I would just use the conditional formatting thats built into excel. I always try to use as much that is native to excel as i can
2) I havent really dove into this code, but seems to be a of "select"...
When you have list like you do and looking for 1 value, using IFs, vlookups, etc can get quite cumbersome. I have attached a simple function that uses the Select Case to create a simple UDF. If the selected fruit is empty, then will show "Please enter a fruit", but can be easily changed to...
Calculated columns in pivot tables can be helpful, but they are often too simple. I would take a look at that the "GetPivotData" function. Can be a little intimidating at first, but once you dive into it's quite easy and makes using pivot table & calculated columns much easier.
This should get you started in the right direction. Of course it can be as simple or complicated as you want to make it.
https://www.dropbox.com/s/meb1j5aoc6ytxkp/Address%202.0.xlsx
I took a quick look, and I would have to guess that since the numbers are so big ( when I copied and pasted into my excel it gave me x.xx+Exx) so using the countifs it probably only goes out so many numbers. Probably need to break that number up into some a little more manageable
vandanarana,
What I have found to be a lot of help when I'm having trouble with some VBA, is that just turn on the recorder and do whatever I'm trying to do ( assuming it can be done via the recorder). Of course it won't be perfect, but it will give you a good head start of what needs to be...
for these types of simulations, I have found that using the solver add-in is the best method bar none. I guess it's how involved and how much time you want to invest. Stats in excel is pretty vast area, there are a few good books out there, but the one I have found to be invaluable is...
I think the attached will get you on your way. Everything that it does I learned here from Chandoo, both in the free and paid services. If you are at all serious about xl, then I would highly recommend the classes. Anyways...
*I made some assumptions and filled in random data for the rest of...
This should get you started in the right direction. Offset can be a little tricky, but it's very powerful in order to make your excel dynamic. Chandoo has great free tutorials on this site, so I would look at that.
https://www.dropbox.com/s/1hczqes6agx413j/Rolling%20MAs.xlsx
I always found adding calculated fields in pivot tables to be somewhat restrictive. Only makes sense though, since the data is coming from only one source. A couple of ideas:
1) Connect your data to PowerPivot add-in. You can do much more with pivot tables that way.
2) Use your cube...
Just took a quick look, is there anyway you can upload the .csv file? Seems odd to have the dates going across instead of down. Since you got a table how you want it. If the newest data will always becoming in the same way, seems relatively straight forward to able to create a macro to import...
I'd say go for it. Though SQL is a very broad subject, so who knows if you will get an answer. I myself, have been learning T-SQL. Just seems like a natural progression. Learn XL to manipulate the data, then learn some SQL to efficiently get the data.
Always interested to see how other people do things in Excel. I think that's the great thing about xl, is that there are so many ways to accomplish it. I like what you have here, so always something new to look at.
What I have done was just assign the macro to the actual picture of the...
Maybe this will help you in what you are looking for:
http://www.2shared.com/file/bKfrFeuh/Chart_Zoom_Example.html
*my apologies if this reply shows up twice