I am trying to populate a table (a7:c27) in the attached sheet with data.
First, I must enter a number 1-16, which uses index function to return either "series" or "base"
If it is base, i want the number in the corresponding table filtered down - i.e. if 1 originally picked then h7 is (40) is entered into each month as base = constant.
If the result is series, then the data in i7:z7 is entered into the the 18 month table.
I've just started working on an FTE calculator and wish to populate a 12 month calendar with FTE depending on the start and finshed dates.
FTE Calculator non nursing.xlsx
I've attached the file. In Column D the user would select the month the staff start and in the Column E the month the staff will finish. I would like the fte that is calculated in Column Z then to populate in the 12 columns AB:AM (Jul to Jun) with corresponding month start and finish.
I'm having a bit of a block with an index/match formula that I am trying to create for the attached spreadsheet. i.e. I need to populate cell J3 with the info the corresponding letter contained in the table to the far left. The numbers of reference to match are the 'zones' in H3 and J2.
View 2 Replies View Relatedwith the attached spreadsheet?
I want to populate "selections" sheet with data from the previous sheet (it will be the date) depending on the value of a cell in column G. You will see I have already got the data from 27th April into my selections sheet but wondered if it can be done by a formula to save me copy and paste time?
I have my projection formulas that use the date and age to estimate production on each farm. Basically the age is calculated first and then the production rate is estimated. I have been trying to figure out if, instead of having a formula in each cell, can I use VBA to populate the ages and then the production.
Here is an example:
8/22/2009
FarmAge8/22/09Age8/29/09Age9/5/09Age9/12/09Age9/19/09Age9/26/09Age10/3/09Farm 1
34181351813617937179381793917940177Farm 2
39223402204122042220432204422045218
All the functions I have created work, I just can't have the worksheet auto-calculate due to the total number of cells with functions. Calculating the worksheet takes approx 1 min...
I should be able to adjust any code to the ranges needed, but this example shows the basic layout.
Macro to extract data from cells and populate them into a table. Go to the links below for the images. Why I can't attach images here.
HTML Code: [URL]....
I have several spreadsheets referencing the "Data" sheet's table (about 35 columns, and the row lengths will differ from 10 to several hundred).
I need to be able to filter the table in "Data", and have the hidden rows not show up everywhere else in the document. I have both vlookups and index formulas in the other spreadsheets, and what I'd like to do is be able to filter by any column in the table and have only the shown results show in the other sheets.
I know this might be accomplished using subtotal, and Row, etc., but how to set it up with the different formulas I have going on in the sheet that pull data from the table. I need this to work with both the vlookups and index cells.
I need to complete the 'cost field' in Table A, from Table B, but the 'cost' value must be selected from Table B based on it's ' date completed', as per my example attached...
View 9 Replies View RelatedIs there any way to have a cell change its colour depending on whether a condition is met. E.G., can i have it as green if the result is a plus number and red if its a negative?
View 2 Replies View RelatedIn my spreadsheet cell g1 is a name and cells g3:g6 contain data for that name.
I have a macro that exports the data in g3:g6 to another workbook and then deletes it.
What I need is a macro that when I try to change g1, gives me a warning if there is unexported data in cells g3:g6 (ie the sum of them is greater than zero) and gives the option of either continuing or stopping (presumably using a YesNo box).
I'm trying to make a listbox that contains a list of items from ranges that differ depending on the contents of one cell.
In other words, say the contents of the cell in question is 'flower' the listbox will be populated with the range of cells containing flower names; if the cell was 'shrubs' the same listbox would be populated with the range of cells containing shrub names.
I've scoured the site, but can't find an exact query that matches mine (though I've probably missed a bunch of them).
Attached is my sample data.
In sheet "support data" I have two columns.
One column is a client reference and the one next to it is a client name.
On my form the combo box is populated with the client references.
When i select a reference in the combo box I would like to populate textbox1 with the corresponding client name from "support data"
(as an advanced feature it would be good if the textbox1 kept changing as the mouse was hovering over the list of client references in the combobox)
I am trying to have a cell populate verbiage depending on the total % of the cell to it's left. I have created this formula but it's not working.
=IF(F25=100,"Meets Expectations")+IF(F25>100,"Exceeds Expectations")+IF(F25
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
How to populate MTD data linked to the other spreadsheet file (monthly data) to the table on the left, depending on the month selected month from the dropdown list.
Attached files for reference :
Dropdown Month.xlsx‎
MONTHLY DATA.xlsx
What I want to do is, given the data in col. a, I want to supply a number(range) of strings which can be found in column a, whenever a string is found, which exists in given range, I want to write the string from the Key(col. c) in col b instead of the value from the string that matches a value.
See below for example of what I got working but not exactly what I was looking for
Col. A- Data
Orangegreen
Blackwhite
Blueyellow
Col. B - Result
Col. C - Keys
Orange
White
Yellow
I used
Code:
=INDEX(A1:A3,MATCH("*"&C1&"*",A1:A3,0))
This does produce a result, but not entirely what I am after.
This requires only one string to search for, and will in cell B1 result in a value of 'Orangegreen' as I can only supply one valeu to the function. What I want to do is supply the function with a range of values, and have those values outputtet in colb instead af the value of cells where the result is found.
I tried modifying the function to include a range, but that does not produce a valid result.
I want to pull the file location of a hyperlink to populate a image control in a form. I have 150 names, each with pictures. So I need to do a index,match or vlook up within the VBA and make the result the active cell. From there I know how to pull the file location out. Just need to know how to make the address of a index match result in vba.
View 5 Replies View RelatedI have used in Index & Match formula in column G as follows :
=IF(ISNA(INDEX(R:R,MATCH(E13,S:S,0))),"",(INDEX(R:R,MATCH(E13,S:S,0))))
The codes in columns R & S are as follows:
******** ******************** ************************************************************************>Microsoft Excel - M_NECPE.Man Accts.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutR1=RSTU1New CodesOld Codes 2 310100199999V 4101002NEW 51010032270A 61010061100 7101009NEW 81010101104 91010111101P 101010121101S 111010141102 121010151102L 131010181130 141010222203 151010231106I Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The code in column E & G is as follows:
******** ******************** ************************************************************************>Microsoft Excel - M_NECPE.Man Accts.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG62H62G63H63G64H64G65H65G66H66G67H67G68H68G69H69G70H70G71H71G72H72G73H73G74H74=EFGH621106B| 631100|101006 641101D| 651101K| 661101P|101011 671101S|101012 681102|101014 691105R| 701103|101037 711104|101010 721105| 731106|101031 741106I|101023 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have used a lookup table to calculate the value in column H as follows, but cannot get it to work
=IF(ISERR(VLOOKUP($E62,$M$3:$O$850,3))=1," ",VLOOKUP($E62,$M$3:$O$850,3))
I have two lists of values in ascending order. Column A is an identifier and is always one more than the previous. Column B is ascending as well, but there is never any guarantee that it will increase from one row to the next or how much it will increase when it does.
My index formula looks like this: =INDEX(A2:A21,MATCH(D9,B2:B21,1)+1)
In this example, if D9 = 1 then it needs to return 6 but instead returns 10. How can I make this index for my purpose? Or should I be looking for a different function?
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
If I populate a cell from a cell on another sheet but plan on deleting the data on the other sheet is there any way to hold the result on the new sheet even if I delete where it came from?
View 3 Replies View RelatedI am attempting to get an INDEX function with multiple "IF" statements to return more than one result. Building multiple IF statements. That portion of the below formula works. However, I can only get the INDEX formula to return the first, single result.
Formula:
=IFERROR(INDEX(Func_Area,SMALL(IF(Const_Start<=DATE(YEAR($B$2),MONTH($B$2),DAY($B$2)), IF(Cost_End>=DATE(YEAR($C$2),MONTH($C$2),DAY($C$2)),ROW(Func_Area)-MIN(ROW(Func_Area))+1,"")),ROWS($A$4:A4)))," ")
Func_Area,Const_Start,Cost_End are all Named Ranges on Sheet "Proposed". My intention is to return a list of projects on Sheet Test1 for all projects starting construction after a certain date and completing construction before a certain date. I suspect something in my ROWS fuction or ROW-MIN+1 is wrong.
This formula returns the first result in the first row, but every subsequent row is blank...see attached spreadsheet.
I am using this to count how many times a particular number appears across numerous sheets, My sheets are in a Named Range MySheets....
=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A30"),A2))
This works great.
What I now need to do is to find ALL matches and return B1 from those sheets..
GHIJKLM1Countif ResultFirst ResultSecond ResultThird ResultFourth ResultFifth ResultSixth Result25AppleBananaPearMangoOrange31Banana43BananaMangoOrange
I have played around and the closest I can get is with this...
=IF(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A30"),$A2),INDIRECT("'"&MySheets&"'!B1"))
This as you may know only returns the result B1 from the first sheet within MySheets.
I'm compiling a list of names on a separate tab from a column that only lists the names sporadically. Problem: In the formula, the IF provides a TRUE, the row function, and consequently, the INDEX function (when I hit F9) provides the correct "name" and row number, but when I hit return, it provides the text 4 cells beneath what it should.
=IF(ROWS(F$5:F5)
Here is a code for me to print the names in ascending order even if values are of same means it is showing one name only instead of the other name
means if values are A=1 B=2 C=3 D=4 E=2
it has to show like this A B E C D
But the result is coming like this A B B C D
Formula which i used
=OFFSET(B$6,MATCH(SMALL(C$6:C$18,ROW()-ROW(C$6)+1),C$6:C$18,0)-1,0)test.xls
Here i am attaching the sample file for your reference
I have a workbook with two worksheets,
sheet 1 contains student data, name dob, actual age, raw score and an empty column standardized score
sheet 2 contains a conversion table, using the actual age from sheet one you locate the age in the top row of table in sheet 2, and using the raw score from sheet 1 you locate the matching raw score from the first column of the table in sheet 2, going down and acrosss until you meet this gives you a standardised score
i want the standardized column in sheet one to fill by using a formula which looks at the table in sheet two locates the two values and returns the result.
I have a table below that looks like this:
LOC
# of days
DTX
3
RTC
3
PHP
12
IOP
12
The # of days column will be a manual input. Then I have a larger table that will take those values and convert them to look like this: (Obviously the dollar values are pulled from somewhere else)
1
DTX
$ 1,292.00
2
DTX
$ 1,292.00
3
DTX
$ 1,292.00
[code]....
I've been thinking of trying to use a macro but not sure that is the best way. Using a button or something doesn't seem very elegant either.
I have data that was taken hourly for a whole year. It is in three columns as shown below:
Date Time Value
8/13/2009 0:00 0.6
8/13/2009 1:00 1.2
8/13/2009 2:00 1.4
. . .
. . .
8/13/2009 23:00 0.8
8/14/2009 0:00 0.7
8/14/2009 1:00 1.5
. . .
. . .
What I want to do it is put the data in a table as shown below:
Hour 8/13/2009 8/14/2009
0:00 0.6 0.7
1:00 1.2 1.5
Such that the hour is in the first column and the values for each date is in individual columns. How can I populate the table easily using formula? For the first date column I can just reference the first 24 cells. But how can I create a formula for the second date that I can use to copy over for the third date, the fourth date, and so on? So for 8/13/2009 0:00 I can put into the cell =C2 For 8/14/2009 0:00 I want to put something in that automatically advances 24 cells down the value column and that I can copy and paste into the remaining date columns of the second table to populate it automatically. It seems like this should be easy but I can't figure it out.
I'm trying to get dates within a column on a table highlighted depending on their value.
I know how to do this using today's date, but I am having issues in using a separate set date as the parameter.
For example;
I want all values in a table in =K7:K72 that are more than 30 days older than a value set in A3 to be highlighted red.
This should be quite easy, but every time I enter anything the whole column changes colour when this is incorrect.