Last 50 Posts on Chandoo.org

Chandoo on 2019-10-18 06:26:47
Nope Wayne. If you have an API that can be accessed with secret key, you can use Power Query or Webservice functions in Excel.

Amardeep Subadar on 2019-10-18 05:17:08
Hi chandoo, can you please provide some vba codes and explain in web scrapping

Hui... on 2019-10-17 14:49:19
@Ram Try using the opposite of InputColumn/Row link that you choose previously Hui...

Albert on 2019-10-17 05:39:20
I am wondering how will the plotting work, for some of the targets which may have been achieved before time. E.g. for the month of Jul the target was 226 and the actual was 219. So the chart will show a deficit in meeting the target by 7 points but what if this 7 may have been completed earlier in month of June. So ideally it not a deficit.

Ram on 2019-10-17 03:23:28
Hi I tried to work out an example with data tables but unfortunately entire column adjacent to "confirm input data" column i.e. column H is repeated with same profit numbers ($5.45m). Not sure if am doing anything wrong

Ella on 2019-10-16 14:45:00
Do you still have this macro? I'm looking for something like this to help with a monthly spreadsheet where I spend a lot of time copying/pasting from filtered/visible cells on one spreadsheet into filtered/visible cells onto another spreadsheet.

Hui... on 2019-10-16 13:28:25
@Johannes That is a great pickup There has been a change in the Excel/VBA that isn't allowing a redraw of the chart between calculations. It definitely works up to Excel 2016, but isn't in 2019 or Excel 365 I'll investigate and update if I find a solution Hui...

Johannes on 2019-10-16 11:17:20
The animation above shows dynamic charts for Pi Estimate, Variance from Pi, and Throw Status. However, in the Excel sheet these charts are not dynamically updated. The data of these three charts is shown at the end of the simulation. How can I create dynamic charts? Many thanks for the answer.

wayne on 2019-10-15 16:22:16
Can this work for sites i have to log into like TD Ameritrade?

sam on 2019-10-15 11:22:24
=SUMPRODUCT(--ISERROR(MATCH(WEEKDAY(calls[Call time],2)+calls[Call time]-INT(calls[Call time]),(ROW(working.hours[Day])-6+(working.hours[End]))))) gives 134

Ramhesh on 2019-10-15 05:02:37
Hello Chando, Thank you for your quick response and help in resolving this issue. I downloaded the file and it's working. Cheers

Chandoo on 2019-10-15 04:54:54
Thanks Ramhesh.. Sorry for the broken link. I have fixed it. Please try again.

Ramhesh on 2019-10-15 04:20:41
Hello Chando, I came across this topic and Dash Board today. Would like to say "Thank you" for sharing your thoughts and to understand importance of retirement life planning and when i tried to download the Excel sheet and Zip version file i couldn't able to see any file available for downloading. Can you check and share me the file so that i can us e it

DILIP on 2019-10-15 02:56:39
I have tried to download the file from google drive and unable to open the file. It is giving following error message. " sorry we could not find c: \user\loca\microsoft\windows\inetcatch\ie\vdi13uc2\pk's_Utility_tool_v1,01(10).xlam it is possible that it deleted, renamed or deleted. and then after a new sheet is opening but not the downloaded google sheet. kinldy help me how to solve.

Marck on 2019-10-13 17:31:30
Dear, i think thath you know very well Excel. I am not a beginner in Excel, i am the owner of a Italian Website about Excel. Thank you for you resources. Best regards. Marck

Chandoo on 2019-10-12 20:06:01
@Sreekumar... interesting question. Your Age & Salary ranges are somewhat inconvenient to work with. Assuming you can split the range start and end values into separate cells, you can use INDEX(outputrange, match(), match()) like this:

Chandoo on 2019-10-12 19:53:46
Thanks for purchasing this Padmaraj. I have already emailed you the download link.

Peter Bartholomew on 2019-10-12 10:32:46
Hamish, Yes it all works perfectly. That includes cases in which the data table does not comprise raw data but rather is made up of dynamic arrays. Naming the anchor cell of each dynamic array allows expressions such as = XLOOKUP( MAX(selectedNetSales#), selectedNetSales#, selectedSalesPerson# ) Conversely, if the returned field is comprised of anchor cells for separate dynamic lists (e.g. employment data for the specified salesman) then the list can be returned by adding '#' =XLOOKUP(0,sales[Net Sales],EmployeeInfo,1)#

Peter Bartholomew on 2019-10-12 09:38:53
Here is an idea. If you wish to analyse data for a given month, the relevant portion of the Sales table (sorted by date) is given by = XLOOKUP( EOMONTH(month,0), EOMONTH(+sales[Date],0), sales,0,1 ) : XLOOKUP( EOMONTH(month,0), EOMONTH(+sales[Date],0), sales,0,-1 ) which can be referred to as a named formula 'selected'. Being a reference to the original table, range intersection with columns works. Hence = XLOOKUP( MAX(selected sales[Net Sales]), selected sales[Net Sales], selected sales[Sales Person] ) provides an answer to Who had most sales for February? Caution: The formula requires 7 separate searches of the data but they are very fast.

Mike on 2019-10-11 22:04:14
I simply made a Text Formula: =MID(jobs[[#CELL],[Job title]],1,LEN(jobs[[#CELL],[Matching Job (correct answer)]])) I hope my translation from spanish to english is OK but this Works on my woorkbook. Regards,

Mike on 2019-10-11 21:20:58
=IF(COUNT(C4:X4)<=20,IF(SUM(C4:X4)<=1400,"GO","NO GO"),"NO GO")

Padmaraj S on 2019-10-11 08:58:55
I had purchased unprotected version today, EBS Payment ID: 140686093. Do let me know when would i receive the pack Regards, Padmaraj.S

Sreekumar on 2019-10-11 07:11:55
regret to inform you that i could not upload any workbook for reference. Pls help with the excel formula Salary Range Age 15-25 26-35 36-45 18-25 1 2 4 26-30 3 5 5 31-35 4 81 1 36-40 6 8 2 Require excel formula when the age and salary is written the corresponding value with the range data of the table gets displayed in "Output" Example Input Age 29 Sal 29 Output 5

Manoj Kumar on 2019-10-11 04:12:02
Sir, You are really doing great job. Its highly appriciated.

Thanks on 2019-10-10 23:03:10
I have a real big problem at school with lookup and goal seek

Chandoo on 2019-10-08 19:32:23
Hi Gabriel, Welcome to Chandoo.org and thanks for posting your question. I am not sure I understand the requirement clearly. Can you please explain how the answer "4" is arrived at?

Gabriel on 2019-10-08 11:23:21
I have two lists . If LIST B IS ONLY APPLE FOR LIST A ID'S THE COUNT SHOULD BE 1. IF LIST B HAS OTHER THAN APPLE or APPLE FOR LIST A ID'S THAT SHOULD NOT BE COUNTED. PLEASE HELP List A List B COUNt OF ONLY APPLE FOR LIST A ID's ARE : 4 111 APPLE 111 APPLE 111 APPLE 111 APPLE 112 APPLE 112 ORANGE 112 GRAPE 113 GRAPE 113 ORANGE 114 APPLE 114 APPLE 114 APPLE 115 APPLE 115 APPLE 116 APPLE 117 ORANGE 117 APPLE

Chandoo on 2019-10-08 05:29:56
Not sure I understand the problem. If you move the folder location, you need to go to "Source" step and change it. Then refresh the query.

MOHAMMED MOHADDIS on 2019-10-07 13:53:04
Hi Chandoo, Thank you so much for the helpful information, and an amazing way of explanation. Can't thank you more awesome website for beginners to advance in Excel.

Rosalia on 2019-10-07 09:29:03
Yes please don't share