For my work I have an inventory spreadsheet that I’m in charge of keeping on top of. I’d like the “date checked” cell to update to the current date every time I make a change in X row. Is there a way to do this? I figure if there is, there’s probably some code wizardry going on. I’d love some help with this cause it would take probably 10 minutes off this daily task for me

Further, is there a way to add some sort of button or check box that would count as a row update? This would make it easy to just check the box and have the date update if there’s no changes to note.

Hi

I've run into a weird problem using excel that started today (after years of use). Excel keeps moving the decimal spot while I write numbers. If I write out 1330, excel treats it like 13,30, unless I write 1330,0.

Any way to fix this?

Hello everyone, I have a spreadsheet that calculates the delivery time for certain orders, based on the quantity in stock and import arrivals. However, I am only able to calculate dates for full fulfillment of orders. How can I open this formula so that it calculates partial services, even if I don't have the entire amount?

1º I need to order the orders by product, so that the stock can be subtracted by the orders in sequence

Formula used:

=IF(G6=G5,AK5-I5,W6) if the product is the same, subtract the stock, if not, just show the stock

2º After that, I need to calculate the expected date for each line

Formula used:
=IF(AL6="No Safety Stock",IF(AK6>=I6,$AK$5,IF(AM6>=I6,$AM$5,IF(AO6>=I6,$AO$5,IF(AQ6>=I6,$AQ$5,IF(AS6>=I6,$AS$5,IF(AU6>=I6,$AU$5,IF(AW6>=I6,$AW$5,IF(AY6>=I6,$AY$5,IF(BA6>=I6,$BA$5,IF(BC6>=I6,$BC$5,IF(BE6>=I6,$BE$5,IF(BG6>=I6,$BG$5,IF(BI6>=I6,$BI$5,IF(BK6>=I6,"Waiting for Production","Waiting for Production")))))))))))))),IF(AND(AK6>=I6,AL6>0),$AK$5,IF(AND(AM6>=I6,AN6>0),$AM$5,IF(AND(AO6>=I6,AP6>0),$AO$5,IF(AND(AQ6>=I6,AR6>0),$AQ$5,IF(AND(AS6>=I6,AT6>0),$AS$5,IF(AND(AU6>=I6,AV6>0),$AU$5,IF(AND(AW6>I6,AX6>0),$AW$5,IF(AND(AY6>=I6,AZ6>0),$AY$5,IF(AND(BA6>=I6,BB6>0),$BA$5,IF(AND(BC6>=I6,BD6>0),$BC$5,IF(AND(BE6>=I6,BF6>0),$BE$5,IF(AND(BG6>=I6,BH6>0),$BG$5,IF(AND(BI6>=I6,BJ6>0),$BI$5,IF(AND(BK6>=I6,BL6>0),"Waiting for Production","Waiting for Production")))))))))))))))) this formula calculates the delivery time, based on stock and import arrivals

https://preview.redd.it/2zmi9gd8lh0e1.png?width=1605&format=png&auto=webp&s=ba33558d9de83b577d0963588e298bb1b1847a8b

This way, I can only calculate the delivery time to fulfill the entire order, but I would like to be able to provide information on partial deliveries.

Picture of spreadsheet, leaving marked the cells that were used for formula 1 (blue) and formula 2 (green)

I have about 31+ excel workbooks (database) with multiple sheets having similar columns.

I have a other sheet which I use to fetch data from all these workbooks using VLOOKUP.

(Only one, common for all, fetch data from one sheet at a time) I use it to take print outs.

The problem is I have to manually Find & Replace the workbook/sheet reference to fetch the data from different sheets. I is very difficult to manually change the reference sheet.

I added a separate sheet like this.

https://preview.redd.it/yoop0qoljh0e1.png?width=1048&format=png&auto=webp&s=05e7ba2d10b609cb24ce093b619d594e4d2dd785

Is there any way to get the table_array from this table??

Thank you.

Hello Xcel ppl! May I ask for a solution on this?!

In sheet 1, Column A has a list of members and Column B has a blank space for their ID numbers. In sheet 2, Column A contains their ID numbers while Column B has the names that corresponds to their respective ID numbers.

I'm looking for a solution to match the members in sheet 1 with their corresponding ID numbers present in sheet 2 and put it in Column B.

Please help. I'm losing my mind about why am I even bringing this work with me after hours haha. much oblige!

So for my work I have built a pretty advanced worksheet that streamlines alot of inputs, does a lot of math and probably 1000 if statements. Then spits out a fully formated and compiled system note for our CRS.

It drastically cuts down time spending doing tasks and really streamlines my work.

Everything was perfect till my manager saw this and was like share this with the team!

Well here's the problem, if people put something in the wrong cell and use cut and paste to move it into the right cell, OR accidently clicks and drags a cell down cause they are going to fast it creates a ref error as the cell that got XV'd into is now a difference reference. I know how to fix it... Ctrl z lol, but some of my non tech savvy coworkers can't grasp this, or don't know the giant reference error till it's like 20 inputs latter.

I used absolute cell references, but it still breaks it. We do a lot of pasting into the cells from outside excel.

Is there anyway to disable this feature and stop the references from changing?

Otherwise I'm about to make a IFERROR statement into basically the entire work sheet, and then do a conditional format to change the entire sheet to bright red to let them know they fucked up.

Hi, I am quite puzzle on how to mark column G as the legends in column H. I'm a newbie and I only know data validation that uses the default format but I want to have a filter for column G that changes just like the format of column H. Thanks a lot!

https://preview.redd.it/ysss7293hh0e1.png?width=1079&format=png&auto=webp&s=1c4ca04e09ee60c893a6ec0d1c8886c5702b3f8c

I want the paid yes/no column and the amount owed columns to be blank if the availability is not any idea why this isn't working? Also Available is row T paid yes/no is Row U and Amount Owed is row V

I'm trying to change the anchorpoint of my diagonal row. Now its where the purple cirlce is, and it needs to be at the red circle. Can someone help me?

https://preview.redd.it/ac7hmsh9hh0e1.png?width=463&format=png&auto=webp&s=82ba6b566086668fc3088386506419cac3cfdb69

Hello experts. I've been at this problem for hours, using Google, YouTube, ChatGPT and Copilot, but I can't seem to find a solution.

I have an imported sheet with a collection of equipment for all trailers of a specific brand. All the parts are crammed into one sheet, but I want to make them into separate sheets.

The sheet has part numbers in column A (A3:A678) and corresponding "Y" values in G3:DY678 which marks if the part number matches the variant listed in G2:DY2.

I.e.:

I've made one sheet per model, which is supposed to fetch which part numbers matches that model. The information I have to go with is the Y and the variant number. I want to list all part numbers that matches the range.

In the sheet for the model, I have listed the variant numbers in H3:P3 (although some models has a huge number of variants, so that can't be limited to end at P3), so that can be used as a reference, as can the Y's in the imported sheet.

The problem is that I can't seem to use INDEX/MATCH, because that just gives me the first match, and I can't use FILTER because that won't search multiple columns.

This code works to some extent, but it's not dynamic and will only give the parts that matches one variant.

=FILTER(Utstyr!$A$3:$A$1000;(Utstyr!$G$3:$G$1000="Y")*(IF(H3<>"";MATCH(H3;Utstyr!$G$2:$EE$2;0);0)))

This code gives an error because there are multiple matches to Y and it's static because of the reference to H3 (and not H3 onwards):

=INDEX(Utstyr!$A$3:$A$1000;MATCH(H3;Utstyr!$G$2:$EZ$2;0);MATCH("Y";Utstyr!$G$3:$EZ$1000;0))

Are there any experts out there who knows how I could go about solving this issue? I'd highly appreciate any help.

Hello and thank you for your help in advance!

I'm currently working on a project where I would like to analyze data according to specific values. however, as the data in each row belongs to an individual subject, I would like to freeze the row in itself.

Example:

Row 1 has data in column A2-A8. Row 2 has a different set of data in the same rows.

I would like to only look at a specific information that is listed in column A6 and see to how many rows that applies, without losing the connection to the original subject.

Hope that makes sense, sorry for the bad English! Thank you!

|| || |||

Excerpt of table and hopeful outcome

Hi, I'm new to power query and I have been experimenting with a basic project. For the sake of this problem, basically I have 'Workbook 1' (size of 3mb). This workbook has Tables A (100~ rows), B (5 rows after grouping) and C (100 rows after grouping). Part of the power query is merging Tables B and C into Table A. When I introduce these merges and start loading the query, it says that by the end it is 'loading 100mb from Workbook 1'. What does it mean that the power query is 'loading' so much data from a source that is only 3mb? I can't get a grasp on this, and the refreshes are really slow. Is this just a result of merging a 100 row table with a 100 row table? and if so why would it lead to 'more' data?

Hello. I am working on a project using a pivot table. For example, my table ranges from A1 to A44 (table 1). I have data, table 2, that is not included in the pivot table in A46 to A51. When I use any filters in my slicer for table 1, the rows collapse and do not go past A15. Is there a way to automatically move table 2 to begin at A16 when the filter is used so the date is easier to read and you don’t have to scroll down to A46 every time you filter to see the table?

I'm trying to make a schedule, weekly, based on each monday. so my columns would be Monday Nov 11, Monday Nov 18th, Monday Nov. 25th. Is there a short cut for this? I need it to be for a year.

I refuse to believe that anyone actually uses the space delimiter option when pasting data; so why is it always selected by default?!?

Hello, I hope my explanation is understandable but I'm struggling with the following:

I have a roster-based Excel and I'm trying to get a total amount of available people on a specific day. Their roster is displayed as different text, for example V1 and V2 due to different work hours. But also V is displayed, which shouldn't be counted because this mean they're absent.

So if on a specific day there are 3 people in the office and 2 absent, which formula do I use to display a total of 3 instead of 5 based on their work hours which is displayed in different text based cells?

Thanks in advance!

I am making a card game and all my data is organized in excel. Is there a way to connect the data in the excel chart to autofil into a card frame template?

I thought i was good at excel until I got a new job and they use pivot tables. Something I have never used. Scary at first but im getting it.

So i have this workbook. Summary up front with Pivot table. I add a sheet each week with new and updated data. Keeping the old sheets as a "historical tracker" if you will. I only want the summary to show the current week.

First issue is, updating the data source is a pain. Id love a way to be able to select a sheet but i dont think i can do that. Tried setting a variable but couldnt make that work in the data source.

The real problem i want to solve is this:

My columns never change, however, lets say that Column "Severity" (can be A, B, C, or D lets say) this week has 4 "A", 3 "B", 3 "C", and 5 "D"'s.

i do some standard calcs with those numbers outside of the table. all good.

Now Next week there are no "C" Severity's. Now i get the #ref flag in my calc because the C row just doesnt show up and the formula is looking for something even a 0.

There has to be a way to either write the formula to compensate for this.

Currently im just adding a few of these together.. its not a complicated formula but it is a pain to redo it weekly.

Any help would be amazing.. thanks

Sorry if the question is unclear, didn't know how to phrase it correctly.

I'm an intern in a logistic company, and I had to create calculators on transport costs in excel for each country we transport to. Now what I want to do is create a new excel sheet where I can simply select the country I need info on and it shows me the corresponding calculator. I've been messing around with drop down lists but I can't find it to work. If more information is needed to help me, I can provide it.

If anyone knows how to do this, help is very much appreciated as it would boost my grades!

Hey fluffy beard wizards

In sample data below, how do I change the formula in H2 so when I choose the data validation list in L2 (Tender_AU, Tender_JP, Tener_US), it'll replace the last line of the formula with the appropaiate Tender_choice? Do I use INDIRECT? I've tried a bunch but couldn't get it to work 🙁

Thank you so much as always.

https://preview.redd.it/g8j9e6c5zg0e1.png?width=1698&format=png&auto=webp&s=59e11ff1a7179af9cfce1c2da010f2e95f2b6160

Hi Excel Veterans,

I have 3 separate tables within one sheet. Need help on how to freeze the table headers for each table but not the entire row where it is located within.

Is this possible?

For instance, Header of table 1 and 2 is located at row 3, a separate table is aligned nicely below table 2 columns, but is table 3. Also need table 3’s header to be frozen so only the contents can be sliding away.

I've just learned how to drop a repeated cell into several rows. Now I want to copy this formula so that it works for all the cells in that column...but because of the repeated cells I'm now skipping rows.

I.e. I've got a list of names that I need to repeat individually x times. The x is variable, and the number is in its own cell.

Sheet 1:

Name Rept

Smith 3

Jones 7

Khan 12

Ito 2

On sheet 2 I've created:

Name

Smith

Smith

Smith

But now when I want Jones to be next, it gives me Ito because I'm 4 cells down from where I started. I've tried $A$2+1 but it didn't like that!

Dear excel masters,

I need a solution to compare dates (12 columns) with the current date to check the certificates of vendors. If one value is invalid it should print invalid in b2 I tried f.e. =if(date()>or(f2;h2;…);“valid“;“invalid“) But all I can get is valid.

TLDR how to check if one operator fails

I've got a count of how many times x is (it varies), but I don't even know if this is possible?

I've tried TEXTSPLIT REPT, but can't figure out how to put a delimiter in without returning an empty cell for the final one.

What I want to achieve is to count specific values ​​that fall into specific months. It doesn't matter if the value is only within a fraction of a month, as long as it is within a month it should be fully counted.

I have used =MONTH to put a number to the dates and then =SUMIFS if the number meets the criteria of being within the mentioned time period. But where I get stuck is when the start and end dates are in two different years. Since 1 (January) can never be greater than 12 (December), the number is never added.

The top half of the image below shows how I want it to be (manually counted). The bottom half is the result I get when using Formulas (error highlighted in red).

How can I get it to summarize values ​​that lie between two different years?

Currently using some check boxes for a list and need a function for them to show the completed amount. Example would be I have ten check boxes, only four are marked. So the result of the function would show 4/10. (Or something similar) Only, at a much larger scale.

Any help would be appreciated! =}

Hi, I need to calculate the volume of a graph using the volume of revolution formula and integration. I need to obtain a bunch of values for differing upper bounds (but I will keep the general function the same). I am using Excel on a MacBook. I tried to Google a solution, and it said to use the QUADF function, but that does not appear in my formula bar. Is there any way to find the integral?

I have a complete workbook of data. A new sheet is created for every project i work on and i basically want to create a tag and sort system. I prefer assigning multiple tags using the data validation like type A, Type B, financial year and so on...

And i want another cumulative sheet where i need to just select the tags and the data from all projects would popup. How do i go about it? And what do i need to learn to make this work?

Hi, I am using this formula from this https://exceljet.net/formulas/filter-on-first-or-last-n-values

=INDEX(FILTER(data,data<>""),SORT(SEQUENCE(3,1,SUM(--(data<>"")),-1)))

It shows in a vertical array but I want it in a horrizontal array so I switched 3 and 1, yes it becomes horizontal but it is in reverse order (counting from last to first), how do I fix it?