I have a list that has blank rows mixed in. I'd like to return a list without those blank rows to be used as a dropdown menu for a budgeting tool.
I tried using the offset formula from the link below but this just cut off my list after the first blank rows showed up.
My current list looks something like this:
Code
5000-Salary
5000-01 - Hourly
I have a worksheet which shows multiple tests and whether they are completed or not[code]Test 1 Person1 Test 2 Person2 Test 3...Person12. I want to search for a specific test, e.g. pH which could be in any of the "Test" columns but only return it if it is not completed. The "Person" columns will be blank if no one completed the test.
I set up the Advanced Filter criteria with all the appropriately labeled columns and for the Person columns, I put ="" but it is returning all rows which have pH in them whether they are completed or not. What should I be putting in the Person columns of the criteria to only return those rows which have the pH test but it is not completed. I attached an example of the criteria range and the filter window to help clarify (the full spreadsheet is huge)
I am trying to have the user click on a drop down list that shows several scenarios and return a score based on the scenarios. I created a list on a separate worksheet. The list contains the following:
Comprehensive list of policies 100%
General policies with no details 75%
Purchased policy templates 50%
No policies 0%
I would like the user to see this list including the corresponding scores,but when they select one, I would like the percentage to be returned in the cell.
I tried using Data Validation with VLOOKUP and named my list; however, it returns the text in the first column instead of the percentage.
I have a spreadsheet with some cells setup with a drop-down list containing Y, N or N/A
This is being used on a TabletPC but if I make a mistake or need to change back to a blank field I have to invoke the soft keyboard, activate the cell and hit backspace then close the soft keyboard - quite a long-winded procedure just to change an incorrect choice!
What I would like to do is add a blank to the list so if I have to revert back to a blank I can just use the stylus to choose a blank from the chooser list.
How do I add the option of inputting a blank from the Data Validation List bearing in mind I am using the Data Validation Source box for entering my choices directly and not specifying a range of cells?
I have tried adding "" and even a space to no avail.
I have a list in one worksheet which comes from "=SALESMEN!$D:$D" but the list is extremely long with blank values. How can I make the list only show values from column D which are non-blank?
Currently the list goes up to 30, however I want to use all of Column D from the SALESMEN worksheet, that way if I add to it, the names will automatically be added to the list in the other sheet.
I have a tracking sheet... in that I got in column C say "priority" there I have a drop down list date say P1, P2, P3
Now I need to restrict that if a user enters a data in the C2 and trying to enter a data in D2 leaving the drop down list data blank in C2. A error message saying "pls select priority forst" etc...
I got the following solution from moderator:
You can apply the Custom Data Validation formula =LEN(C2)>0 to D2, making sure that Ignore Blank is unchecked.
It works but, when I enter something in that cell it gives the error message and when I click OK or Cancel it just ignore the conditions and leave the data what ever I entered and moves further.
I am using Data Validation and I don't want the user to go past a cell without putting in an entry. When I leave the box (ignore blank) unchecked it doesn't do anything after I have protected the document.
I am currently working in an excel file for which i want to create a macro. I am using colomn F as the base from which it should be determined whether a row should be copied. In colomn F the user can select three options through data validation. If 'completed' is selected, the whole row should be copied to the second sheet, and each copied row should be pasted to the next empty row. The data on the source sheet starts at row 11 and should be copied to sheet 2, starting from row 11 as well.
View 5 Replies View RelatedI am a personal trainer/strength coach, and i use excel to write my workout programs.
To save time in writing programs i have made lists of all of my exercises in a separate workbook. I have them all linked via data validation in drop down lists to different templates for different populations ;weight loss, injury prevention, sports performance, ect. And it has been working great!
However here is my problem, I would like to be able to return an entire list of an exercise circuit with one drop down list and have it input all the exercises in cells below the drop down list.
So for example i have three columns on a separate sheet.
Column one
Circuit A
exercise 1
exercise 2
exercise 3
Column two
Circuit A
exercise 4
exercise 5
exercise 6
Column Three
Circuit A
exercise 7
exercise 8
exercise 9
In cell A1 i want a drop down list that that has circuit A, B, C,
using the drop down list if i pick circuit A it will return exercise 1, 2, 3 in the cells A2, A3, A4, receptively.
I have a colum of up to 5000 possible entries and I need to find the row number with last non blank value. If row 1 = 'A' , row 5 = 'B' and row 10 = 'C' with rows 2-4, 6-9 blank, I need a combination of functions to come to Row(10). The last value is not the largest and I can't use helper cells to get it into a sorted order.
View 4 Replies View RelatedI am applying data validation in my workbook. The data in ColS consist of:
ColS
(Row2) X
(Row3) Y
(Row4) Y
(Row5) Y
(Row6) X
In ColT, I am to apply data validation: i.e. If in concerned row of ColS is having "X" value, then I am to show an error message. Else, values from A2:A5 should be shown in the as list and these should be accepted in ColT.
I have a worksheet containing a list of states in column A and a list of companies in column B (along additional data in columns C-L). I want create a lookup formula on a separate worksheet where users can select the state from a dropdown menu and it will return the information from columns B-L for that state.
For example:
A B C D
Alabama ABC Company Active Expires December 2009
Alabama 123 Company Expired Expired April 2008
Alabama XYZ Company Active Expires August 2009
Alabama Larry Company Expired Expired May 2006
How can I do this without losing my mind? I've tried various Index, Small and Match formulas and none of them work.
I want to lookup the tags in sheet1 on sheet2 and see if they have a date in columnB. whatever date is in columnB sheet2 i want that same date to show up on sheet1. but if the there is no date i want the cell to remain blank. do i make any sense at all? look at my example. example2.xls
View 3 Replies View Relatedmy question is how to return blank cell if i have no data inputted in left cell.. or if AH9 is blank or no data inputted AI10 will be blank using this formula.. =IF(AH9<75,"A",IF(AH9<82,"B",IF(AH9<87,"C",IF(AH9<92,"D","E"))))
View 2 Replies View RelatedI want to load the actual data exist in the rows by selection value from the data validation list.
E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.
On the second sheet, i made a data validation list of all the person names
Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.
I am using Excel 2010 and I am trying to average the amount of days in a month to a daily average per person in my worksheet.
Total sales per person
A5 = 10 - This is the Grand total per person for column A
A6 =4
A7=6
Daily average per person
C5=2.6 - Average for all persons here
C6=2.0
C7=3.0
The formula I am using is:
=(SUMPRODUCT($A$6:$A$15,C6:C15))/$A5
Which gives me an answer of 2.6 in cell C5 as shown above which is what I am wanting.
Please note that my cell range for my staff goes from 6-15 for both Column A and C where the other cells are blank in both columns.
My question is, If I was to clear all the data in both Columns A6:A15 and C:6:C15, cell C5 would return to a #VALUE. How to I change the formula so that if the cells were Blank, cell C5 would also be blank until I enter data for each person again?
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies View RelatedI have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.
View 2 Replies View RelatedI have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)
for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked
I have a data validation cell that has 5 items to be selected, lets say A-E.
I would like a macro that checks and does the following:
A or B selected, hide row 25-34, rows 45- 53
C or D selected, hide row 24-44
E selected, hide rows 34-53
In each case, the macro should check and unhide all rows between 25-53 before doing the above
I am creating a tracking spreadsheet where i can toggle between months (attached). It returns data properly when I select January or February from the yellow highlighted dropdown list. However, when i choose March, it returns 1/0/1900 because no data has been entered for March (table array) on the AAAG tab. How do I write the formula to return a blank cell on the summary sheet when no information has been entered into the table array yet?
View 2 Replies View RelatedI have a spreadsheet that has 4 columns and column D has some blanks randomly down the sheet. How can I delete any and all rows that have a blank in column D?
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies View RelatedI have tried to do this with with index/match formulas but can not quite get it right.
I have a spreadsheet with tool sets broken into individual parts ...
i need to return the rows in another sheet that have values only. for example if cell "A1" = 1 "A2"=2 "A3" = nothing "A4"=4 i want cells "B1" "B2" "B3" to return 1,2,4 respectivley.
View 2 Replies View RelatedIn table 1 you will see that not all rows contain data (this is because i have removed it previously with a macro) the second table shows what i want the macro to do but without deleting rows:
A
Hiddenrow with formula
B
C
[Code]....
So basically i would like a macro to search for the first none blank value in column A and copy the row to the first available blank row , it also needs to blank out that row once done. i.e row 3 would be moved up to row 2, 5 up to 3, 7 up to 4 etc etc.
The hidden rows contain formulas which cannot be deleted or my ws goes Pete Tongue.
I would like to know how can i insert a blank row in between ACC# when ever a new series of data begins
name Acc # Balance Date
John 2222 200 june 2013
John 2222 344 june 2013
John 2222 700 Sept 2013
Silva 4444 333 June 2013
Silva 4444 333 June 2013
Silva 4444 333 June 2013
Raff 5555 897 June 2015
Raff 5555 897 June 2015
Raff 5555 897 June 2015
Raff 5555 897 June 2015
a code that will delete blank rows between data, below is sample of my data, the header row is 8
From this ....
In a rage of rows I have some rows that are blank - without any text or data. Is there a macro I might enter that will look at the range of rows, determine which are blank and then delete the blank rows?
I've got a spreadsheet that I prepare for a group that has patient data subtotalled by physicians. The problem is that the group uses this data for data entry and wants a blank row between each different patient name within the physicians subtotals and I have no clue how to get there. Any chance there might be some VBA code that could accomplish this?
View 4 Replies View Related