Connect with experts and redefine what’s possible at work – join us at the Microsoft 365 Community Conference May 6-8. Learn more >

Need Help

1703 Topics

Formula or function for IF statement based on cell color
I don't know how to code in VBA but am trying to automate an if/then calculation based on cell color. As shown in the picture, if the colors of the cells in column B are the same as those in Column G across the row, I want to subtract the values in columns F and K in the same row to return the absolute value of the subtraction in column L. If the colors of the cells are different, I want to add the values in columns F and K and return the value in column L. I will have multiple tables of varying numbers of rows where I need to perform this operation. Any help is greatly appreciated.
Solved
Laurie McDowell
Jun 15, 2017 Place Excel
1.6MViews
2likes
33Comments
How can I stop Excel from changing the colors of my chart ?
I have a chart with past and forecasted figures for several series of data. I set the colors of each serie in order to have something nice and meaningful : 1 color per serie, dotted for forecast. Each time I filter my data to see a particular serie, Excel destroys my work to put back its automated layout which are ugly and meaningless. How can I prevent this ? I'm using excel professional plus 2013 Thanks a lot !
Solved
fabien.calendar
Jun 21, 2017 Place Excel
382KViews
1like
33Comments
Highlight the minimum value in each row
Hi. I'm a complete beginner to Excel and need some help please. I'm doing a spreadsheet comparing prices from different companies for hundreds of products so I'll have about 10 columns and up to 1000 rows. I want the cheapest price in each row to be highlighted after I input the data. Could somebody please help me do this? I'll be mainly using Excel android app. Thank you in advance.
Kim Flanagan
Jan 17, 2018 Place Excel
350KViews
0likes
4Comments
Copy Data to Other Sheets' Columns Based on Criteria
I have one Orders sheet and 12 Month sheets (Jan, Feb, Mar, etc). The Orders sheet has five columns: Dept, Vendor, Brand, Cost and Date. The Dept column has ten different options ie Truck, Car, RV, Boat, etc. The Orders sheet will have all the order data for an entire year. The Month sheets have the same columns as the Orders sheet excluding the Dept and Cost columns. Instead of one column for Dept there are 10 columns, one for each of the options. These columns will be populated with the cost of each option. This makes a total of 13 columns on the Month sheets. Vendor, Brand and Date columns are A1:A3 and the Dept options columns are A4:13. I would like to populate the Month sheets based on the Date column from the Orders sheet. For example, all the orders between Jan 1 and Jan 31 would appear on the Jan sheet, all the orders from Feb 1 to Feb 28 on the Feb sheet and so on for the remaining Month sheets. Depending on the option selected in the Dept column of the Orders sheet, I would like the associated cost to populate the corresponding options column on the Month sheet. For example, if on the Orders sheet an RV was entered at a cost of $35,000, that cost would be transferred to the Month sheet to the appropriate option column. In this case the RV column. To sum up, each row in the Month sheets would have the Vendor, Brand, Date and Cost in the appropriate option column. This data would be auto populated from the Orders sheet. Is there a formula(s) that makes this possible? Thanks in advance!
Solved
Rob Nunley
Sep 22, 2017 Place Excel
258KViews
0likes
11Comments
sum by color when colors are set by conditional formatting
i have a column of numbers that are color coded (to represent a specific mfg department) and I need to total the values by color. Meaning I need to total all the values that have the same background color. I have done an exhaustive search online and was able to quickly find a way to sum by color, unfortunately it only worked for cells whose color was set manually (NOT using conditional formatting). I have found a few references to VB code that should provide the functionality but I can't get any of them to run (except for the one that works for manually set colors). I have control of the data that I'm trying to sum. is there another method to "tag" values? I thought of adding a letter prefix, but coulnd't find any way to sum a column of numbers that are contained in text strings. Any help would be greatly appreciated!! I'm using Office 365 (excel 2016) on a windows 10 machine
Solved
matt nipper
Dec 20, 2016 Place Excel
254KViews
0likes
304Comments
Combining IF and INDEX functions
I'm trying to find a formula that will allow me to display multiple fields of data when a specific value (a product SKU) is entered into a cell. I managed to get this to work with a VLOOKUP formula, whereby I could enter the SKU into one cell, and have the following cells display various values, such as the brand, item, Net cost, VAT, gross price and so on. However, want to use an INDEX formula instead so that if I edit the reference table then it won't affect the returned results. I also want to be able to specify which columns/value are returned (for example, I don't necessarily want to display the Net cost of a product. From what I can tell, VLOOKUP only allows for either a single column's value to be returned, or all of the specified columns from left to right, with no option to omit any. I think I need a combination of IF and INDEX, whereby IF the value matches an SKU in, say, Column A, then values relating to that product code are returned. I thought I was getting there with the following formula (albeit only being able to return one value): =IF((P15=D:D),INDEX($E$15:$O$105,0,2)). D is the worksheet column in which all of the SKUs are listed. This worked if I entered the relevant SKU on row 15, as it returned the correct gross price that was displayed in column 2 of the reference table. However, a value is only returned if the SKU is that of row 15, otherwise it displays 'FALSE' if I try and search prices using any other any other SKU input. This has been driving me mad for hours, so I'd be hugely grateful if anyone is able to enlighten me. Thank you.
Solved
Peter Fleckney
Dec 15, 2017 Place Excel
246KViews
0likes
8Comments
Missing JSON option at Data > New query > From File
There is allegedly an option to import JSON data in XL 2016, but the option is missing in my copy. It isn't there in Customize the ribbon, so it can't be added from there, either. Any ideas where it got to?
Noel Burgess
May 13, 2017 Place Excel
205KViews
0likes
8Comments
Writing a formula to return a blank if no data is in an adjacent cell
I've forgoten how to how to write a simple formula that will keep the cell blank if there isn't any data in an adjacent otherwise it should do the calculation. This is how I wrote it. =IF((E7=" "," "),(F6+E7)) Thank you
chris.stgermain
Mar 03, 2017 Place Excel
171KViews
1like
5Comments
Re: Macro to save as PDF with auto filename as cell value
Hi I recently used the following Macro, provided by Gary's Student, to create a PDF from an active sheet, generate a unique Filename based on a cell ref and save it to a specific location. This macro works well for me, however, I would like to add to it so that I can also attach it too and email and send to a specific email group (using Outlook). Maybe have it allow you to View so it can be sent manually, or the option to automatically send. Below is the value I used to create the filename based on the date entry within I3 and I formatted it as shown. Cell H1 "=I:\2017 - 2018\Operations Unit\Day Sheets\"&"DS_"&TEXT(I3,"yymmdd")&".PDF" Sub Macro1() s = Range("H1").Value ' ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ s, Quality:=xlQualityStandard, IncludeDocProperties _ :=True, IgnorePrintAreas:=False, OpenAfterPublish:=True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub
Solved
Joe Gray
May 30, 2017 Place Excel
166KViews
0likes
26Comments
Macro Settings grayed out
I have an EXCEL spreadsheet that needs to use a macro written specifically for a task. I'm using Microsoft EXCEL 2016 MSO (16.0.9001.2080) 32-bit. I am not able to change macro settings at: FILE > OPTIONS > TRUST CENTER > TRUST CENTER SETTINGS > MACRO SETTINGS I have created a self signed certificate and added certificate to clients and EXCEL file too. Is it now impossible to run macros even in .xlsm files? Please help as this is killing this process we use on a daily basis.
Gary Nelson
Feb 20, 2018 Place Excel
152KViews
0likes
4Comments