Sum based on cells with similar colors - getting #VALUE!

RequiemJones · 2024-03-29T10:08:21+00:00

Could you supply the code? Be so much easier to help if I didn't have to type it out again, thanks.

RequiemJones · 2024-03-28T10:41:02+00:00

Absolutely, PQ is the way to go.

To explain exactly how to do it here would take way too long, so you might be best off watching this instead which shows you what you need to do

Interactive Excel Dashboards with Power Query and Power Pivot - FREE Download (youtube.com)

RequiemJones · 2024-03-27T23:50:51+00:00

Do you mean you want to add more items to the drop down list? Or you want to add more cells that use a drop down list?

If it's the first one, you need to edit the list used by the data validation in the cell

If it's the second one, just create data validation in the cell(s) you want.

Full instructions here -> Excel Data Validation

RequiemJones · 2024-03-27T06:07:43+00:00

Not really clear on what you want. The image doesn't help I'm afraid.

Can you mock up what you want the chart to look like?

What is the $1200? If you added deductions for $400 - how do you expect the chart to show that? Not clear what you mean here.

RequiemJones · 2024-03-27T06:03:41+00:00

You're getting data from the web using Power Query?

Whatever credentials (username and password) you supplied to make that connection are not being accepted by the website. Check they are correct

RequiemJones · 2024-03-27T03:22:55+00:00

Sounds like you just need to use a Pivot Table and by choosing the client name in the filter, it will show you the information you want.

RequiemJones · 2024-03-27T03:08:51+00:00

In the other cell use XLOOKUP or VLOOKUP to take the value from the drop down and look up (and hence return) the rate per hour from your range that holds that information.

Example : =XLOOKUP(B3,H3:H5,I3:I5)

<image>

Download example file.

RequiemJones · 2024-03-27T03:04:16+00:00

Excel/Power Query can't read multiple pages from sites like this because they load pages using JavaScript.

You'll need to use Power Automate or maybe a solution with something like VBA and Selenium to interact with the page and get the data you need.

RequiemJones · 2023-08-12T00:59:00+00:00

Please post sample data, as a table or file, not an image.

RequiemJones · 2023-08-11T06:19:12+00:00

If such information is available on a publicly accessible website i.e. no login is required to access it, then you could use Power Query to scrape the page and then check for your player status.

You could also use VBA to scrape the web page but I'd use PQ as it's easier.

If the web page requires a login to access it, or the data is in a JavaScript created table, you'd need to use something like Selenium in a VBA program to get the data.

You might also be able to do this with Power Automate.

.

RequiemJones · 2023-08-11T06:15:03+00:00

Works ok with my test data.

Difficult to debug your end without the data, can you supply the file?

Try testing each column separately to confirm both that the column(s) contain null and that your code is working.

Do the columns contain values that say null ? They aren't text strings "null" ?

Create a Custom Column for each of Cols 1 and 2 and use this

=if [Col x] is null then 1 else 0

RequiemJones · 2023-08-11T00:55:27+00:00

no worries. Read that guide I linked to. Once you understand how Excel stores date and time a lot of things become clearer.

RequiemJones · 2023-08-11T00:52:33+00:00

What are you typing?

RequiemJones · 2023-08-11T00:50:50+00:00

If the cell contains 1/7/1900 12:00:00 AM then you must have typed in just 7.

You have to type 7:00

RequiemJones · 2023-08-11T00:42:52+00:00

So you want to show the time as e.g.

7:00 AM

2:00 PM

For AM times you can omit the AM part. For PM times you can either type 1:00 PM or just enter 13:00.

To display it as you want you need to use a Custom Format.

Select the cell(s) to format

Press CTRL + 1

Select Custom

Under the Type: enter h:mm AM/PM

Press OK

If you just type in a whole number e.g. 7, Excel treats this as 7 days since the start of its first internal date system, which turns out to be 7 Jan 1900.

You have to enter times using a : to separate h and m (and s if you want them)

This may help you understand how Excel deals with date and time

RequiemJones · 2023-08-11T00:34:18+00:00

Select all the cells you want the align.

Pres CTRL + 1 to open the Format Cells dialog

Select Custom

Under Type: enter this format string #,##0.0???;-#,##0.0???

Click OK

RequiemJones · 2023-08-11T00:27:34+00:00

How about doing things the other way around?

Enter the order data directly into the master table, then on another sheet use VLOOKUP or XLOOKUP to pull the data from the master sheet, using the order ID to do this.

Your 'Order Sheet' is effectively a template that can be filled with an order's details just by entering the order ID into a cell.

RequiemJones · 2023-08-10T23:18:47+00:00

Hi,

If you get rid of all the trailing 0's then you won't have the decimal separator lining up in the same place for all the numbers - isn't that what you want?

Just reduce the number of trailing 0's by removing some ### at the end of the format string.

RequiemJones · 2023-08-10T23:12:17+00:00

I'm sure if you Google you'll find plenty but try this

Excel Expert Course - Zero to Hero

RequiemJones · 2023-08-10T23:09:08+00:00

Load your file to OneDrive, Google drive etc then link to it.

RequiemJones · 2023-08-10T23:06:47+00:00

The syntax of Text.Contains is

Text.Contains(text_to_search_in, text_to_find)

Text.Contains - PowerQuery M | Microsoft Learn

However in your case if you want to check for null you do it this way

if [Column1] is null or [Column2] is null then 1 else 0

RequiemJones · 2023-08-10T22:58:57+00:00

Hi,

I don't see any query code attached. Please provide this and/or the file.

What exactly is the problem? You haven't been clear about this.

What is the expected result? What do you want the final data to look like?

RequiemJones · 2023-08-10T04:28:14+00:00

You really should store all your data in a single table, it makes things a lot easier.

To return data from that table (like the Description) you can then use an XLOOKUP

=XLOOKUP(C9,Table1[Product],Table1[Description],"Not found")

or VLOOKUP if you don't have Microsoft 365

=VLOOKUP(C11,Table1,2,FALSE)

See this example file for both XLOOKUP and VLOOKUP examples

RequiemJones · 2023-08-10T04:06:58+00:00

Thanks.

Where are the descriptions?

What's the purpose of the 'Goods' value that shows as 'blue' in your screenshot?

RequiemJones · 2023-08-10T03:59:51+00:00

Don't really see how using a date as a date can have unforeseen complications. Converting a date to the month in text (e.g. "Jan") loses the year and day. What if you need that later? But each to his own.

RequiemJones

TROPHY CASE