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")
how i can select the most recent 10 cells from a list of data to be included in a formula. I have selected it manually, for example F122:F131. This is fine but i don't know how i can put it so that it will update to the 10 most recent results when i input another line of data. So when i put in more data it stays on the previously selected. I want it to move shift down to include the new data but only the 10 most recent.
View 5 Replies View RelatedI have a list of data in columns Q R S and i need to in between each line insert the data from columns D E F underneath and then underneath that the data from G H I
so bascially i have a list at the moment like this
Q
R
S
[Code]....
There is 2 sheets in total , 1 i need to inc 1-1 draw as above and the other sheet to be exc the 1-1 draw . I have attached an example of what i am trying to achieve . can offer as i have had this on excelforum.com with over 150 views and no solution
I have a list of Names that has numbers accumulated over months. I am looking for a formula to use in a search tool that uses a drop down list to identify the name I am looking for and then the formula will show me the increase that happened in certain months.
See the attachment the result is coming from Denominators tab, I want the answer displayed in the Display tab, you will see a drop down list at C3 in Display, I am trying to show the answer in cell D9-I9.
how many the denominator has increased in that month. so for D9 it will come from denominator tab column c minus b, E9 will be d minus c
I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)
View 3 Replies View RelatedI have a list in column L2:L352. I would like to be able to start at AD2 and drag horizontally so that AD2 = L2, AE2 = <BLANK>, AF2 = <BLANK>, AG2 = L3, AH2 = <BLANK>, AI2 = <BLANK>, AJ2 = L4, etc.... until L352 has been autofilled.
View 2 Replies View RelatedI 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.
The data looks like this,
RequirementsQtyResourcesQty
Level 1 Sentinel10Level 1 Sentinel105
Level 2 Sentinel10Level 2 Sentinel167
Level 3 Sentinel10Level 3 Sentinel156
Level 1 Banga20Level 1 Banga401
Level 2 Banga20Level 2 Banga307
Level 4 Banga20Level 4 Banga0
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.
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
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.
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies View RelatedIs it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:
A1= "1"
A2= "2"
A3= "3"
Then I type in the simple formula in the cell
B1= "=A1"
and then drag down but it skips 3 cells so then
B5= "=A2"
B9= "=A3"
If it didn't skip then
B1= "=A1"
B2= "=A2"
B3= "=A3"
this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.
Something similar to this B1 "=A1(skip3cells)"
I am looking to average a range of cells which won't always be the same size. How do I create a formula array that will omit empty cells in my formula.
View 9 Replies View RelatedWhat I'm trying to do is link a cell to give me the sum of 2 different cells located in 2 different tables I created.
So I want to have Cell B5 look for the number in cell B3 in the Table that starts in column Q1 and give me the corresponding number in the same row in the column R1
then lookup the number in cell B1 in the Table that starts in column T1 and match it with the number in Column U1 that corresponds to it in the same row
and Finally give me the sum of both corresponding numbers.
IE.. the number in B3 is found in Q13 which uses the number it corresponds to in R13. The takes B1 finds it in T16 and uses the number it corresponds to in U16.
Then returns the sum of R13 and X16 into B5. (B5 should show 176)
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
View 9 Replies View RelatedI try this method but data entry not allowed
Here is a scenario to protect
formula cells but allow access to data cells:
. Select all cells in the worksheet by clicking on the top left corner of the sheet.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Uncheck the Locked and
Hidden check boxes, then click OK.
These four steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to
protect the formula cells, and here is how we do it:
. Select all cells that have formulas.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Check the Locked and Hidden check boxes, then click OK.
. On the Tools menu click
Protection and select Protect sheet
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
View 3 Replies View RelatedI 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.
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)
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).
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?
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.
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 View RelatedI need to create a formula that looks at 2 adjacent cells. If both cells show 'YES' then I need the 3 cell to show 'YES'. If either/both of the 2 adjacent cells are blank then the third needs to be blank.
View 2 Replies View RelatedTo get cell to display results of formula I must edit (F2) and hit enter. Is there an easy way to do this in bulk rather than editing and hitting enter on hundreds of cells?
Example of formula:
=IF(COUNTBLANK(K2)=1,"",CONCATENATE("*** ",K1,": ",K2," "))
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 RelatedI 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
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
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.
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.