Search results

I didn't understand what the first paragraph is doing, probably haven't had enough coffee yet, but the rest is very cool . . . of course replacing the formulas with values is still important.

Interesting. My intention is to distribute the file within our organization so may have to look at ODC files. Your comment about the path is interesting as I can find no directory with the word PowerQuery. This is likely because we are running on Citrix. It may be we need a tweek of how...

The "storing" you refer to seems to be in memory of the computer. If you reboot you are back at ground zero putting in the User Id and Password again. This becomes a bit tedious when you are using several data sources. It seems Microsoft is intentionally preventing saving credentials. If you...

Recently we upgraded our servers and moved from Excel 2013 to Excel 365. I am enjoying power query but am frustrated in that I can't find a way to have an excel workbook save the Username and Password required to connect to the SQL database. Also after I manually input the credentials I get...

It was very hard to pick a name for this thread. I'm an accountant and as such run Aging reports showing for each of our customer total of invoices over 30 days, over 60 days, 90 days. To aid in collections I do this in excel so we can sort the data so that the companies that are most urgent...

If you look at column C you are asking to index 1,2. I.e. row 1 and column 2. The range you are indexing on is only one column wide.

Is the data contagious once you bring it in? I.e., no blank rows? When you click on the Sort icon is all the data you want to sort highlighted? Scroll down to see if your new data is included.

In Sheet 2 a2 type =Indirect("sheet1!b3"). This works for me if I understand what you are trying to achieve.

I don't see formulas in the file Narayank991?

Yes Sarfraz you can do exactly that in the formula using today() minus the invoice date. I typically have a cell at the top of my aging called report date which I use as an absolute reference. I've attached an example where you can have the system date in B1 or you can type in any date...

Nice tips. Occasionally I receive a spreadsheet that has many many tabs. Some Excel developer must have been thinking of me when they added the ability to right click the scrolling arrows at the bottom left corner of the spreadsheet to bring up a list of all the tabs to select from. From...

It is a bit painful the first time you try this.

I have the lookups on the wrong side of the issue so I put the formulas in c4:d7. Basically the same idea as Luke's. Sorry for the confusion.

I think you have the lookups on the wrong side of the issue Luke. Maybe this.

I'd approach it as thinking about stroking out choices once they are chosen. One of the difficulties I talked about earlier was that it is possible to have duplicates . . . I.e., two scores of 8 for example. How would we identify, in a way that Excel can find it, which 8 we used to satisfy...

Maybe I'm over simplifying but maybe one of these two pivot table layouts would work for you.

I should have said countif rather than counta. Example attached here.

First off the numbers you are looking at need to be rounded. You pick the level. I'm just pointing out that 76.567 is different than 76.568 but if you have formatting set to 2 decimal points they will both look like 76.57. Once you have found the largest number you could use counta() to find...

So far slicers have used the underlying pivot table format. Have you tried refreshing the pivot table?

Depending on what your input is you could do it by using a custom format. For instance a format of #,##0.00;-#,##0.00;"Type a positive number" would show Type a positive number if you put a 0 in the cell and it would show the formatted number if any non zero number were keyed in the cell.

An example based on the spreadsheet provided.

In excel time is stored as the number of days. 2:32:41 is actually .106030092592593 days. So if you want the hours you need to multiply by 24. 0.106 x 24 = 2.544. (0.544 x 60 minutes in an hour = 32.64 minutes . . . .64 minutes x 60 seconds in a minute = well not quite should have kept...

Here is an alternative solution using three columns. Using a vlookup to find the closest beginning of range, using index and match to find the end of the range associated with the beginning of the range and then using a third column to see if the value is in the range and if so return the row...

Having no idea of what was posted previously . . . what comes to mind for me is using the advanced filter to copy the data you wish to another sheet based on criteria. Your present solution seems to be based on writing formulas on the second sheet that reference the fist, such that when you...

If you have control over how the data is listed then I think you would be better served if you listed it something like: Name Date Qty Bob 1/1/2015 5 Pete 1/1/2015 9 Then you can more easily use a pivot table to summarize as you have explained.