Posts by VWRSue

Hopefully, I can better explain what I'm looking to do. The spreadsheet is used to capture a lot of data. We are a manufacturing company, and we track chemical production records. The data is entered into the "2021" tab. The remaining tabs are used for metrics. For example, on the MFG BR Metrics and Charts tab, there is a table titled "Total Initiated by Month by Product Family". This just tells me how many production records for that product was issued for each month. The formula I use to capture the data is the COUNTIFS formula. This is the formula for January 2021:

=COUNTIFS('2021'!$A:$A,"=Chemical",'2021'!$B:$B,">44196",'2021'!$B:$B,"<44228",'2021'!$H:$H,"=Buffer").

February would be the same formula, but the > and < numbers would reflect January 31, 2021 and March 1, 2021, respectively, and so on for each month.

Each year, I have to create a new spreadsheet for the following year. As you can see from the formula, I'm asking for all "Chemical" records in Column A, where Column B (from the 20201 tab) is greater than 44196 (December 31, 2020) and less than 44228 (February 1, 2020) for all Buffer products. I do the same for every month, just changing out the numbers for each month's dates.

Currently, when I prepare the spreadsheet for the next year, I have to change all the dates (I can do this easily by find and replace throughout). However, I would like to know if there is a way to just have it capture the data for the month...."give me all the data for January, give me all data for February, etc." rather than telling it greater than December 31st but less than February 1st. This will allow me to just change the year, as well as accommodate leap years.

Thanks for the tips...never really thought about it, and will make sure to follow the rules. I apologize for any inconvenience I've caused. I am, however, unclear as to why I'm getting this message. I have asked for help several times with this same spreadsheet, but don't recall ever requesting help with this particular problem, either on this site, or any others. I rarely use forums other than this one as this is where I seem to get exactly what I'm looking for from you folks.

It would be very helpful to me if you could provide me with where I have cross posted, so I may better understand what I've done wrong. Thank you!

Hello,

I have a file where I capture data per month. Currently, I use a countif formula for each month, e.g., I want to know how many records I've received for the month of January. My formula is: =COUNTIFS('2021'!$A:$A,"=Chemical",'2021'!$B:$B,">44196",'2021'!$B:$B,"<44228",'2021'!$M:$M,">0",'2021'!$H:$H,"=Buffer"). This gives me all data for Chemical Buffer products that I've received in my office for the month of January 2021. Is there a way to have excel provide the data by month of January vs. greater than 12/31/20 and less than 2/1/21? (I would want this to work for all months.) I have attached the file and the formulas I've described can be found on the MFG BR Metrics and Charts tab.

This is perfect!! Yes, there are duplicates, but they are actually separate documents that we track, so I'm pleased that you didn't add a step to remove them. The extra column is not a problem.

Does the macro identify with the filename? The file you've been updating is a copy of the original, so I'd like to copy over the data we've added from the original to this file and rename it.

Thank you so very much. Love this site and all of you are so helpful...it is greatly appreciated!

Hi gijsmo,

This document is a tracking tool when document control issues a manufacturing batch record. It is used daily and data is added throughout the day as they issue batch records. This data is added on to the "2021" sheet. On occasion, there is a Non-Conformance to the batch record, and we log that information in the NCR column. We would like to capture the list of NCRs and Lot Numbers on a separate sheet. Once they are listed on that separate sheet, we have to go into a database and manually copy the reason for that Non-Conformance, and paste it into another column on the NCR sheet (the reason for the NCR can be quite lengthy). I would like to have the Lot Number and NCR columns copied onto the NCR sheet from the 2021 sheet (as you have provided). Once that is done, we will add our comments. Then, as we add more NCR data onto the 2021 sheet, append the new NCR data that we add onto the NCR sheet, without changing the list that is already on the NCR sheet, then we will add the database comments to the new data. Is that possible?

Thank you gijsmo! This gives us a list just as we want, however, is there a way it wouldn't be recreated each time it is run, but any new data added to it? Once we have the list created, we need to add more data from other sources, which has to be done manually. I don't want to re-add that additional data each time I run the macro.

Hello,

In the attached file, I would like to copy data from 2021 tab, columns U (NCRs) and E (Lot Number) to NCR tab, but only copy over if the data in column U is data other than N/A. The data on the NCR tab would just be a list of NCRs and the corresponding Lot Number. I don't want to include any data where column U is N/A. Is this possible?

Thank you,

Sue

I would like to move to a specific cell within a row based on the data I enter in the first cell. For example, if I enter the name Lonza in cell C2, I would like to have the cursor go directly to cell L2. If I enter the name MP Bio in C2, then I'd like the cursor to go directly to cell P2. If I enter MP Bio in C3, the cursor to go to cell P3, if I enter Lonza in C4, the cursor to go to cell L4, etc. Is this possible? I have attached an example of the file. Column C is a drop down list.

Hello,

I have a spreadsheet (see attached), where I would like to copy specific data from tab labeled 2020 to tab labeled Outstanding Log. The following data is to be copied: Column Q from 2020 to Column A on Outstanding Log, Column A from 2020 to Column B on Outstanding Log, Column E from 2020 to Column C on Outstanding Log; Column N from 2020 to Column D on Outstanding Log, Column J from 2020 to Column E on Outstanding Log, and Column V from 2020 to Column F on Outstanding Log. However, I only want to copy data over within the last 30 days using the dates from Columns E and J on the 2020 tab, and where there is no date in Column AC.

I'm not sure if this could be done with a formula or not, so I thought I'd post it here first.

Thank you!

It seems that you get this with the first few lessons (through 6, I believe).

Hi,

I have a spreadsheet that tracks when training is renewed. As you can see, it's set up to turn the cells red in Column E if a date is past due. Is there a formula available where the cell will turn red 30 days before the due date, rather than when it's past due?

Here is the link for the Freebies: https://www.ozgrid.com/freebie…ing-excel-excel-workbooks and this is what I get:

Then I went to contact to let someone know; filled out the form, but there is no way to submit it (no submit button, pushing enter just gives me a return, etc.). Can you tell me how to submit after filling out the form?

Hello,

I went to your Freebies section to see about basic Excel training for a co-worker. However, I am getting an error message when I click the link that either the link is broken or the page no longer exists, or has been moved. I then went to Contact and filled out the form to see if this training is still available, filled out the form, but I see no way to submit my question (no submit button, enter just enters to the next line, etc.).

Is the training still available?

How do we submit any Contact communication?

Hello,

I have a fillable form that I'd like to use with a mail merge. I can easily fill in the fields that are text, but I'd like to know if there is a way to fill in a check box using mail merge.

Hello. I would like Excel enter data from one cell into another cell, based on another cell's entry. For example, I have a date in column A, in column B, there will either be a date entered, or n/a. If column B is n/a, then I would like Excel to take the date in column A and enter it into column C. If column B is a date, then I want to enter data into Column C myself. Is this possible?

Hello,

I have a spreadsheet with a number of tabs. One of the tabs has numerous pages that will not be needed all the time. Is there a way I can have Excel only print specific pages within a tab as well as all other tabs. Setting the print area does not work as I will need to print page 1 and 4 and 7, but not pages 2,3,5 or 6. I have attached a copy of the file, and the tab I am referring to is "Weighed Chemical Tags." Thank you!

Yes, that's how I customize the ribbon. Thanks again for your help. This is a great site!

I was writing my response when Roy posted about the buttons....so thank you again...it's nice to know you would have gotten me there!

So I figured out the issue. I have customized my ribbon with Macro buttons. Neither of you would have known that. If I use those buttons, then Excel wants to open my original file. If I just run the macro from View Macros, it works fine. I had assumed that once I C&P the code, the macro buttons would be associated with the new code. Apparently, that doesn't happen, or there is a step I must take to associate the button with the new code. Anyway, I recreated my buttons and everything works great.

Thank you both for all your help. I greatly appreciate your patience!

As an aside, I've started the VBA tutorials provided on this site...I hope it doesn't teach me just enough to be dangerous!

Thanks again!

Hi Mumps. I didn't change a thing. I copied exactly what you provided and pasted over what existed. I tried two ways: First, C&P the code, then Save As with new filename; Second, Saved my original file with new filename, then C&P the code, then saved it. I have attached the new file.