I'm putting the finishing touches to a grade sheet for the new academic year. I have one column that calculates a grade using the OFFSET function from a given number of points. However, I only want this cell to show a grade when the nine individual unit columns have been populated. If fewer than 9 columns have been populated, the cell in question needs to be blank. I've tried using COUNTA to count the number of populated cells and then linking that cell to an =IF(Q5=9... formula, but haven't been able to crack it yet. I'm probably missing something obvious...
formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula
I'm trying to create an accounting document, but stuck in couple fields
1) Make balance appear in column E5 only if column C5 OR D5 is polulated, so goes for the rest of the cell in that colu
2) I like to have 2 digits after the decimal but (same as above) i want it to appear ONLY if there are any decimals to that number, all those 0 become confusing.
3) I need to make it so the last calculated cell on sheet Jan of column E gets transfered auto to sheet Feb D3 of F3 so the calculations could proceed to the next month
Seeking a way to automatically select just the populated range of cells in Cols A, B and C starting with row 2. In this sample there are 48 however this changes.
Is there a formula that would search for rows, or cells withing a row, that contain information and populate consecutive numbers in an adjacent column. For example if I am working with rows 1-4... F1 and F4 has information populated, while F2 and F3 are blank. I would like for the formula to populate E1 with the number 1 and E4 with the number 2. So the formula should decipher that F2 and F3 are blank therefore ignore these rows in the consecutive number scheme for column E. I would like for the formula to be scalable to any number of rows
I would like to create an spreadsheet that prompts me to input data into a cell (eg; 'C1' - 'Quantity Ordered') as soon as another cell on the same row becomes populated (eg; 'A1' - 'Item Code'). I need this function to apply for several rows (25 rows in total), so that if data is then entered into cell 'A2', I get a prompt to enter data into 'C2', all the way up to a prompt for data in cell 'C25' when cell 'A25' is populated.
As it is imperative that I never forget to populate the relevant cells; I would also like to 'enforce' the prompt - perhaps by preventing the worksheet from being saved until the relevant cells have had data entered into them, or perhaps by using some kind of form, rather than a messagebox.
I have attached a sheet as an example, as i am trying to Divide a cell by the number of populated cells in that column e.g column (A) has a table of 16 but only has 13 populated cells in that column, the sum would be something like this =sum(A18/13. Sometimes the column will have less and at times more populated.
I am sure this should be REALLY simple, but i just can't find the answer i'm looking for. I have a sheet which changes daily in quantity of information. I want some simple code which counts up the number of populated cells in column A and returns the answer to a cell in another worksheet - or just automatically use it to print that number of pages.
I think i should be able to do the printing side, but, currently have no idea how to count up the number of populated cells in the row. If someones feeling very generous though, giving me the code for printing that number of pages too would save me some work (the worksheet which needs printing is different to the worksheet which needs the number of cells adding up.)
I have this formula populating a huge table of data for number of inspections performed, the first reference is a name of an individual, the second reference is a name of the company, and the third reference is the week ending date.
there are 5 of these sheets for 5 different categories. I can get these spreadsheets to populate but i then need to be able to sum from each spreadsheet all of the times an individual inspected a certain company, so one cell in each of the 5 tables.
Each time I do this it returns a 0. If i sum from one table it will return a number but if I sum from multiple tables I get 0
I have a range of data in columns A2 to Mx. The length of the columns varies, and all cells are not always populated. I want to be able to select the entire range, no matter what its length, no matter how many cells are populated (as both of these will vary on each tab) However, I do not want to select A1 to M1 as this is column headers which need to be excluded from the data for compliation purposes.
Sheet 1 contains range B12:B353 with names of people. Sheet 2 range B12:B353 contains a formula to extract the names from Sheet 1 if range D12:D353 contains a certain value:
=IF(Sheet1!D12:D354="A",(Sheet1!B12:B353)," ")
At the bottom of each column in Sheet 2 I am trying to get a total count of populated cells (a running count of names actually visible). I have inserted formula:
=COUNTA(B12:B353)
Which should count the number of cells with text in them, right? But I believe it's somehow counting the formula as text because I inserted the value of "A" in Sheet 1 Column D for 3 names. It returned a number of 331 at the bottom of my column in Sheet 2. First off, a return of 331 doesn't make sense in any way (still scratching my head at this return. It should have returned a value of 3.
In cell H5 there is list of dates and in H7 there is codex, based on these two conditions in H11 a data validation list should be populated from the code_sheet using column B. The populated list should be unique entries, as the column B in code_sheet has duplicates.
I have a bunch of cells that are only displaying ########## (they contain strings of text, and the actual text can be seen in the formula bar).
I know for a fact that the cells do not exceed the character limit for a cell and other cells in the same column are being displayed just fine. I've also tried adjusting the row height and column width but it doesn't seem to be working.
I have a variable COMTXT that loads (via loop) several short lines of text to form a "mouthful" of comments together. But have 2 issues with it.
1- I need to add a carriage return after loading each short line of text. [code] Module1.COMTXT = Module1.COMTXT + Sheets(3). Cells(Module1.COMCODE, 1) [?CODE]I have try'd [& vbCrLf &] in many syntax's to no avail.
2- When displaying COMTXT to a sheet, its too big for the cell ! and not able to enlarge the cell without major disortion to rest of sheet. How do I acheive this and allow the text to display like a textbox or label, covering many cells and rows.
At my work we use excel to keep a large number of labels done in one day, those labels are then added up to give our weeks total, running total and average by day. What i am looking to do though on a seperate sheet though is find out which day we had the most, an example of what it looks like
The formula contained within these cells equals =MONTH(C84) [the date 2 cells above it]... to which I've formatted the cell to "mmmm" and it displays "January" across the board. Can anyone explain why this is occuring and how I can fix it please? I'm aiming to graph this information and can't accurately do it using the moving monthly system i've put in place if I can't change the months dynamically like this.
Secondly, and on a side note. Line 84 currently takes todays date (not currently todays date in the example screenshot) (=TODAY()), and to get to the previous 13 months I deduct 31 days. I can foresee this as being a problem further down the track especially when leap years come into play. Is there a function in Excel where I can simply deduct 1 month from another? =MONTH(C84)-1 ?
The basic spreadsheet consists of a list of names down the left hand side with 19 columns across - one for each art activity (I have pasted a copy of it below - but it doesn't paste so good!). Each person has selected their three activities and this has been recorded by placing a 1, 2 or 3 in the relative columns. Each person has been told that they will get a place on two out of three of their chosen activities (in which case we will change one of their choices to a 0).
What I would now like to be able to do is to create a mail merge in Word which looks at each person and then goes along that row to find out which columns have a 1, 2, or 3 in it. The heading for that column would then be used as a merge field so that I end up with a word document which looks something like this:
I am trying to create a summary Calendar that would display all the names of the people who are off on a given day. I have a grid where I display each day of the month and a person in the group has their own column. If they are out of the office for any reason their name appears in that row. In the big Summary Calendar cell for each day of the month I want to look across all the people columns for that day and then display the names of the people who are not going to be in the office. Can I do this by a formula or will using VBA work best.
I wanted to count the number of instances that Matt's been late. If there are consecutive timestamps (ex. 600-620, 620-640) that he's been late, I wanted Excel to display 1 and then I'll just sum it up. Or if Excel can do this directly, add all the instances because what I actually need is the total per person.
In this example, I would need a result of 4 instances.
tell if the blank cells will affect the formula or if I still need to do something about them.
Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.
Supplier P/N Buyer P/N
HGFYE/12 111111
HYEYDH/14 222222
[Code] ..........
Cell D2 is an open cell that any data can be entered into as a search term. What I am trying to do is search for a Supplier P/N that have the characters "H", "G" or "E" in it, so entering "HGE" into cell D2 would display the results into columns F & G as below.
Supplier P/N Buyer P/N
HGFYE/12 111111
HDGTEY/56 333333
I can easily do a formula for 1 character or a string of characters.
To complicate it further, if the search term has in this example has "YFF", I would like the same formula/code to workout that the result in F & G should show this time
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.
How to to find a formula which will pick the highest number in a row when another row is populated. For example, in the table below row 1 has some numbers, with rows 2-5 being populated with a Y. I'd like a formula to look across the rows individually and pick the highest number in row 1 (it should equal the numbers shown in column F.) when there is a Y in row 2. Then the same again for 3.
