Search results

I want to calculate the cost of a plant based on what the total costs to buy that plant from a supplier. I have attached a workbook with what I am working that a supplier sent. It is straight forward with most of the items with the exception when BOXING or COLD HOT fee is added in the column...

I use Power Query to transform data from Excel files into appends. There are two results that are loaded into the data model and a relationship is created between them. The data model is then loaded to a Power Pivot table. It works well enough but on the Description field on the pivot table...

I use the formula Weeknum(a1,1) for a date that is January 2, 2022 but it doesn't return week 1 instead it returns week 2. I have tried various other first week dates January 2, 2022 thru January 8, 2022 and it won't return week 1 but week 2. I have used the other return_type handles but can't...

I don't know VBA and asking for help on learning VBA to write a formula in column C if the cell in column A is not empty. I have attached a workbook with the formula and the way I am using now is to put the formula in more cells than the user will use. The user may add comments to explain what...

I download payroll data then using Get Data transform the raw data into a table with multiple years, 2015 thru 2021. I want to create a column that will tell me what the payroll number it is for that year, 1 thru 26 January 16, 2015 would be payroll 1 for 2015 January 30, 2015 would be payroll...

I would like to use a Pivot Table based on Rows with fields named Update Description # of Crops Sell Date Area & Tables Values based on Finish Units Units Planted Units Booked (from query SPRBKNG18) Filters Cust Name (from query SPRBKNG18) Pot Size Group I thought I could do it using...

Office 365 Using the Excel PQ or Get & Transform query to retrieve data from SQL but returns the above error message on the other users' computers. My PC doesn't get the error and the queries return the data. Searched online but nothing really helpful. I have used the Excel query part for a few...

I can download payroll check history for several years into a workbook showing Employee ID Employee Name Check number Check Amount Date I want to create a spreadsheet showing current pay rate, Date of last raise, Raise amount and previous pay rate for each employee. I thought it would be easy...

Is it possible to use a formula to limit the years of information that a Power Query pulls into a workbook from a SQL database? something like: year field is >=YEAR(TODAY())-5 So that the query for year 2017 drops off 2011 pulling in only information from years 2012 to 2017, in the year 2018...

I want to count each occurrence of an item in one column (Item Number) based on more than one variable to give the sequence of the item, 1, 2, 3, 4, 5, 6, etc. using table structure. I use COUNTIF(INDEX([Item Number],1):[@[Item Number]],[@[Item Number]]) and have included a file with the formula...

I want to calculate the change in customer sales comparing 2014 to 2015 sales data using a Pivot Table. I can't get figure out how to do it. Any help is appreciated. The data is pulled in using Power Query from SQL database. Thanks, Lee

I have a list of employees and their work groups. I can use Countifs() to get how many are in each group. Is it possible to get the individuals in the group preferably in a cell with a comma or space between? I can do it with Pivot Table but need it outside a Pivot Table. Thanks, Lee

I used to use countif($a$2:a2,a2) to count and then number each occurrence of a number in a column but now need some help to get if done with multiple criteria. DESCRIPTION 4" BEGONIA-BADA BING SCARLET 1 4" BEGONIA-BADA BING SCARLET 2 4" BEGONIA-BADA BING SCARLET 3 4" BEGONIA-BADA...

When I do a refresh on Power Query the Sort I had in Excel goes away. If I do the Sort in Power Query it doesn't import that Sort to Excel. I don't think that Power Query imports or loads the information the same way twice. Is this just the way things are or am I missing something simple...

Excel 2013 Column A has the dates from January 1, 2012 to December 31, 2012 beginning in Row 2 formatted as 1/1, 1/2 to 12/31. Row 1 has the years 2012 to 2015 in columns B - E The sales data is in a separate table called ARBatch. I have this to pull in the sales data for each day copied down...

I have one column in Excel 2013 Table that isn't carrying the conditional format when a new row is inserted. The conditional format is a drop down list to choose an entry. Any help is appreciated. If the cell contents are copied using copy/paste then the formatting is there. Thanks, Lee

How can I change an international date type from SQL inside Power Query to an American date field? The data from SQL comes out as 20140620 and I want to change it to mm/dd/yyyy or 06/20/2014. Any help is greatly appreciated. Thanks, Lee

Is it possible to not have any orphaned rows when printing a Pivot Table report? Currently I use page break preview and manually adjust the page breaks so as not to have an orphaned row on the next page with its sub header and related rows on the previous page. This is ok if the report is...

I am fairly new to Powerpivot and need help in getting the average units sold per week as rows based on the number of years displayed as columns. The example below shows the years and the units sold per week for the item 4" Sq Pots. I'm wanting to create a new column that would have the average...

I have a Power Query that returns a date as 20130910 for September 10, 2013. I can get the date in Excel 2013 but every time I refresh the Power Query it removes my column or my query gets corrupted and I have to redo. The M. language is hard for me to follow and I can't find an example of a...

I want to insert the first cell in a filtered table column in the footer of a report. I found a vba code snippet from http://www.cpearson.com/excel/headfoot.htm to do the footer but I don't have a clue on where to begin with selecting the first cell in a filtered column. Thanks for any help. Lee

I have a table in Excel 2010 with the following in column C =IF(A834<>"",COUNTIF($A$2:A834,A834)&" OF "&COUNTIF([ItemNo],A834),"") When I paste a new block of items into the table it changes the formula to =IF(A835<>"",COUNTIF($A$2:A842,A835)&" OF...

1G ANTHERICUM VARIEGATED ANTHERICUM 1G ARTEMESIA-POWIS CASTLE ARTEMESIA 1G BEGONIA-DRAGON WING PINK BEGONIA 1G BEGONIA-DRAGON WING RED MANDEVILLA 1G MANDEVILLA-CRIMSON RED MEGASKEPASMA 1G MEGASKEPASMA-BISHOP'S CLOAK PETUNIA 1G PETUNIA-EASY WAVE WHITE THUNBERGIA 1G THUNBERGIA...

=TRIM(IF(NOT(ISERROR(SEARCH("SUPERTUNIA",H2,1))),"SUPERTUNIA",IF(NOT(ISERROR(SEARCH("BEGONIA",H2,1))), "BEGONIA",IF(NOT(ISERROR(SEARCH("ALYSSUM",H2,1))),"ALYSSUM",IF(NOT(ISERROR(SEARCH("DIANTHUS",H2,1)))...

I am using Excel 2007 to view sales history as a percentage of sales and quantity of items sold. I can change the percentage to reflect based on column total and total. I have the year and then customer type as a sub category of year in the Row Labels of the pivot table Field List. Is there a...