List And Formula In The Same Cell
Jul 13, 2006
I am working on a worksheet template that creates a list in which the length is controlled by data input into a cell. One of the columns of the list contains a Vlookup formula to load data in the cell automatically. The cells in the column also contain a drop down list. everything works fine. If I set a list length to a certian number and fill in the data, using the drop down list on some cells and filling the others in automatically, it works great. Now if I make the list smaller, the cells in which I set the data through the drop down list remains filled with old data. It appears they lose the formula part of the cell and only the drop down remains. Is this clear at all? Is there any way to overcome this prediciment?
View 2 Replies
ADVERTISEMENT
Nov 26, 2009
First.
I am working on a costing sheet for work and I need a formula to add in employee wages.
Here is what I am trying to do.
Cell 1A - I have a drop down list of employee names
Cell 1B is where the number of hours an employee worked on the job will be entered
Cell 1C is where the employees wages will come up.
Cell 1D will be 1B*1C
What I am looking for is the formula for 1C where the wage will automatically come up when their name is selected in 1A. I guess this would be a IF command?!?!
Second question.
In order to have a drop down list (the instructions that I found anyhow) requires the list in the dropdown to be somewhere on the same sheet.
Can this be circumvented to retrieve the list from a different spreadsheet altogether? Or from a different sheet within the workbook?
View 5 Replies
View Related
Jul 4, 2014
I seek to create a date validation list based on information is another cell (see images)
Based on (data validated list) Cell B4 - have a validation list drawn from another tab on the same worksheet "Sub regions"
equally the same with sub regions and citys...
View 6 Replies
View Related
Oct 24, 2013
I am looking for a formula which will allow me to return a value, 1 or True, if any of a number of words or phrases in a list exists in a specific cell. Per the below example, any formula which will achieve the result in column A
For Example:
Sheet 1
Column A
Apples
Oranges
Banana
Fruit Salad
Fruit Smoothie
[code].....
View 6 Replies
View Related
Mar 21, 2014
A
B
c
d
[Code]....
So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3
View 3 Replies
View Related
Apr 27, 2014
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies
View Related
Apr 18, 2013
I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.
For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).
I am also going to upload a data sheet, and an expected results sheet.
unique list.xlsx
View 1 Replies
View Related
May 9, 2014
Is there a way to create a drop down list from a comma delimited list in a single cell? For example, col A is Name & Col B is the delimited list - Blue,Red,Green (list can be different for each name). Would like a drop down list in col C that allows you to pick one of the values from Col B.
View 3 Replies
View Related
Feb 8, 2014
I would like to take a List of numbers, which come from a separate spreadsheet, place them into a UserForm text box and then use a command button to input the list of numbers into a column of cells. The list would often vary, between 1 and 10 entries. But if I could do this, I would be able to resolve an issue with my spreadsheet that continues to haunt me (the dreaded number stored as text.....). I know that you can assign a single textbox value to a single range-cell value. But if I could input a list at a time, it would obviously save a lot of effort.
Example
In spreadsheet #1, I would copy the list below.
25466
87550
66985
44252
In the user form I would paste these numbers into the text box and they would be input into Sheet #2
B6 = 25466
B7 = 87550
etc. etc.
View 2 Replies
View Related
Jan 7, 2009
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15
A drop-down list displays the values 1 through 15. Cell A1 changes to 20
Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
View 3 Replies
View Related
Jan 29, 2013
I have 3 related dropdown lists that work perfectly, if you enter data from left to right. But, if you click in the cell containing the second list, which is based on the first list, of which nothing has been selected, you can just type any value in the cell. Is there a way to prevent this, or at least validate that what is entered is a value in the list.
View 5 Replies
View Related
Mar 19, 2009
I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.
View 2 Replies
View Related
Oct 1, 2011
Version: Excel 2007 WinXP
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
View 2 Replies
View Related
Feb 21, 2009
I have created a drop down list as follows
Whiteboard 16mm
Colourboard 16mm
Colourboard 18mm
Veneer 18mm
Veneer 19mm
Veneer laid up 40mm
Painted 16mm
Painted 18mm.
When I select one of these items I need CELL A1 to return just the thickness. EG: I hit dropdown list and select Whiteboard, CELL A1 then displays 16mm. Or I select Veneer laid up and it displays 40mm. I would imagine this formula would be quite long as I would have these 8 items in it.
View 3 Replies
View Related
Oct 12, 2009
I have a list of budgets on a summary page. They correspond to a tab name with the budget detail.
I want the formula to automatically pick up the tab name from the list.
How can I use the list from the summary to point to a cell within a budget tab. an example is attached.
I realise some of the formulas on the detail tab need to be sorted but I needed to remove some sensitive work info from it :o) (Dont want to be losing my job)
View 2 Replies
View Related
May 25, 2012
I have created an analysis worksheet but need to a formula that looks up against a list, hence why I think (and hope) that this might just need an array formula.
So the worksheet has a few thousand rows of data (one for each order) that is put against an email address. I need the formula to sum the revenue generated by 'club members' who placed on a specific date whos email appears in a list on a seperate tab. If it wasnt for the email list I would use a simple sumifs formula but I am stumped by the need to look up against a list of emails.
As close as I've got is the following;
=sumifs(data!AC:AC,data!Z:Z,"Club",data!L:L,B11,data!S:S,'Discount'!B:B)
the data tab is the one with all the data and the discount tab contains the list of email addresses to look against (B:B). AC is the revenue data, Z is whether they are club members, L is the data (B11 is the date to look up against).
View 9 Replies
View Related
Jul 6, 2007
is there a formula to list the first and the last day of each month and for every year?.
example:
if i put in cell A1: 01/09/06 ----->01/September/2006
the result should be:
A2: 30/09/06 ------> 30/September/2006
A3: 01/10/06
A4: 31/10/06
A5: 01/11/06
A6: 30/11/06
A7: 01/12/06
A8: 31/12/06
A9: 01/01/07
A10: 31/01/07
A11: 01/02/07
A12: 28/02/07
.
.
.
and so on
how to do that with a formula?
View 9 Replies
View Related
Sep 14, 2009
How to find whether a particular cell is blank or is text in a given row/column?
To elaborate more, I have some data in a row:
A B C D E
15 N 16 17 18
I am using a below formula to find whether there is any text in the row
ISTEXT(row(A1:E1)) but I am getting result as False.
View 9 Replies
View Related
Dec 16, 2007
I require a function that returns an array of all cells linking from a given cell.
For example if the cell Sheet1!A1 contains "=C1+Sheet2!C1+SUM(A3:A5)"
the function ReturnArray(A1) would return the array...
("Sheet1!C1";"Sheet2!C1";"Sheet1!A3";"Sheet1!A4";"Sheet1!A5")
View 5 Replies
View Related
Jan 28, 2009
is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?
View 3 Replies
View Related
Feb 14, 2013
Cell B1 is to display money value dependent on what item has been selected from drop down box in Cell A1
View 2 Replies
View Related
Apr 1, 2014
I would like a formula that will give me the unique list of items in Col A but will give them to me in alphabetical order. Sample list of data and desired result below.
List of ItemsDesired Result
Lamp Chair
Table Desk
Chair Filing Cabinet
Filing CabinetLamp
Lamp Phone
Desk Table
Phone
Chair
View 4 Replies
View Related
Apr 3, 2014
I have a list of what we call "model codes" which are is a similar format to "DFS41FC57DD728NCWRY3"
The list could be 6000 rows but may contain hundreds of duplicates and may only contain 50 different model codes
I am looking for a formula that will populate a new column (B in the example below) with only the individual codes (unfortunately the xls example I created wont upload)
COLUMN A COLUMN B
DFS41FC57DD728N3SRY3 DFS41FC57DD728N3SRY3
DFS41FC57DD728N3SRY3 DFS41FC57DD728NCWRY3
DFS41FC57DD728N3SRY3
DFS41FC57DD728N3SRY3
DFS41FC57DD728NCWRY3
View 5 Replies
View Related
Jun 29, 2014
I am trying to make a simple planning sheet.
I have a workbook with 2 sheets.
1.sheet contains Columns : DayDateTimeClassTeacherCovered
2. sheet contains the availability of teachers. so all columns start with a date and the rows contain the name.
A row therefore looks like this.
7/1
Harley
Lidia
Livia
Thais
Salete
Carla
My issue: The 1.sheet has a column Teacher, now in this column I want to have a drop down list. (E2, E3, E4, E5 ... etc..) This list is depended on the date which is in column B.
Essentially I want the list to look up date in its row go to the second sheet and give me all the possible names in it. That is all.
View 11 Replies
View Related
Aug 1, 2014
I have a list of data with 2 column. One is a column of unique values, the other column has a lot of blanks.
I'd like to auto generate both columns without any of the blank data from column 2. I've attached a mock spreadsheet of what I'm invisioning.
View 6 Replies
View Related
Dec 16, 2013
I have a tab with about 10,000 records with IDs and timestamps (date and time). On another tab I have a list of IDs and start and end timestamps. There are duplicate IDs on both tabs. I want a formula for the 10,000 records tab that checks the ID and timestamp for each row against all the IDs and start/end time stamps of the other column and flags the rows where the ID and timestamp fall within the start/end timestamp on the other tab. It seems pretty simple, and it feels like the code should be something like this (but this code doesn't work):
=IF(AND(F2=List!C:C,B2>=List!F:F,B2<=List!G:G),"Y","N")
where F2 is the ID and B2 is the timestamp on the records tab. The "List" tab has the ID in column C and the start/end timestamps in columns F and G respectively.
View 1 Replies
View Related
Jan 11, 2014
I simply need to "break down" the numbers from the first box. The problem is that the distributional data (A, B, C) are not always the same. One day there might me 10 A's, another day 2, etc...
View 5 Replies
View Related
May 23, 2014
I am trying to write a formula for my account statement its got a list of dates of invoices descriptions then the value of invoice.
I want to check the dates of invoice to the date of statement if less the 1 day invoice total to go in a box called current, if between 2 and 30 days to invoice value to go into 1 to 30 days past due, if between 31 and 60 days invoice values to go into a box called 31 to 60 days past due, and then if dates are between 61 days or more then invoice totals go in to a over 61 days over due box.
View 1 Replies
View Related
Aug 14, 2014
I have 2 columns, I need to consolidate one of the columns separated by a character.
For example, I need to turn this.........
Part# Part #2
1AMAC330221132609
1AMAC330222724908
1AMAC330222724977
1AMAC3303419188468
1AMAC33034F6ZZ-19C836A
1AMAC3305107-0442A
1AMAC330511911006
1AMAC3305119188473
1AMAC33051F0TZ-19C836-A
1AMAC33051FOTZ-19C836-C
into this..........
Part# Part #2
1AMAC330221132609*2724908*2724977
1AMAC3303419188468*F6ZZ-19C836A
1AMAC3305107-0442A*1911006*19188473*F0TZ-19C836-A*FOTZ-19C836-C
View 8 Replies
View Related
Oct 20, 2008
I have a list of account balances in one column. How can I pick up only the bottom number in that column? I have 20 worksheets and want a total across all worksheets of the last number listed in a particular column but each worksheet is different because they contain a different number of rows with activity. So, if in col. C I have:
$482
$1,000
$899
$456
$231
... then I want $231 to be what is captured for that worksheet, but if the 2nd worksheet contains:
$500
$1000
$344
... then I want $344 to be what is captured for worksheet 2. Does that make sense. In the end I want to sum $231, $344.... etc.
View 3 Replies
View Related
