How do I reference value of a cell in VBA example..
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("$A$1:$XFD$10000").AutoFilter Field:=2, Criteria1:= _
"NAME"
I want to replace hard coded "NAME" with the contents of a cell in another sheet?
I've got a workbook with about 1,000 worksheets in it and I need to add up the values across the sheets from the same cell reference. I can get this bit to work with this formula:
=SUM('1:2241'!BJ3)
So this adds up the contents of cell BJ3 across all worksheets located between 1 and 2241. However I also need to count the number of times a zero appears (zeros only not blanks) so I thought this might work:
=COUNTIF('1:2241'!BJ3, "=0")
However it just returns the #VALUE error.
One of my longest running problems with Excel is how to use a cell value to point to a reference. If I have three sheets, 'Main' and 'Data 1' and 'Data 2'. In the Main sheet I'd like to be able to have a value of either 1 or 2 in cell A1. Then in cell B1, use the value of A1 to point to cell A1 in either Data 1 or Data 2 sheets.
View 3 Replies View RelatedThe first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.
Example 1:
If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’
Example 2:
If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’
Example 3:
If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’
From the following example:
A1 TAG B1 mileage
G10-1622 15246
G10-1622 15246
G10-1583 15246
G10-1622 28659
G10-1195 28659
G10-1622 28659
desired results
Enter tag in cell
G10-1662
Mileage
15246
15246
28659
28659
I would like to enter a particular TAG # in I5 and return all the mutiple associated data
See attached : tire2.0.xls‎
Here is a made up scenario which tries to emulate my query:
I have a spreadsheet with two tabs - one called Model and the other called Prod. In the Prod tab, I have the following text entered in single cell - A1
My name is
I was born in
I Live in
What I would like to do is add cell values from the Model tab at the end of each line. For example;
My name is JOE
I was born in 1915
I Live in AUSTRALIA
I tried the following but it didn't work.
My name is Model!A1
I was born in Model!A2
I Live in Model!A3
I believe it didnt work was because the cell starts of with text rather than a formula
I am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.
View 5 Replies View RelatedI want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B
B1 contains 2
B2 contains "tribbles"
An imaginary function might go like this........
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
In sheet1, I have the borrowers and outstanding per employee. In the Area Avg sheet, I have the average borrowers and outstanding in each area as this is the standard for comparison (I got this through the subtotal function).
Now what I have to do is in sheet1, format(change the color) each cell under borrowers which is greater than the average of the relevant area. Since, (in my actual data) the name of the area may be duplicate in different regions, the reference value (average borrowers of the area) from "Area Avg" sheet has to be traced through multiple criteria which are: Division, Region and Area. The same also has to be done for outstanding but the solution can be provided for borrowers only.
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:
View 2 Replies View RelatedI have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB
ABC
CDE
And have a formula in column B that converts this to
=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)
I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
View 9 Replies View RelatedThe values in Cells D1, D2 and D3 are user inputs.
A formula in B16 should sum values in database (ie sum of B6 to B11)
Example :
A
B
C
D
E
1
Enter the column no
B
2
Enter the start row no
6
[code]......
Result
149
I am making a spreadsheet to control my house expenses, and for that matter I would like to have the total sum of expenses for each month, but I am not quite sure how to do it.
I attached an image to better illustrate my question.
I have two persons in my household, and I use this spreadsheet to calculate how much we owe to each other.
But I would like also to know how much we spent in each month (because we both have a top budget we need to control).
I tried to use IF functions relating E8, E9, E... cells to the column A, but I had no success.
I have these data arranged in three columns as follows:
ABC
91183
112198
***3181
54201
***5***
***6***
***7***
***8***
(where *** stay for blank cells)
I need to "report" somewhere and somehow within the spreasheet hopefully by means of some formulas the following data:
1. for column "C" the maximum value within first group of consecutive numbers that are exceeding 180: in this example equal to 201
2. for column "C" the total number of values within first group of consecutive numbers that are exceeding 180: in this example equal to 4
3. for column "B" the first number corresponding to the first group of consecutive numbers that are exceeding 180: in this example equal to 1
4. for column "A" the corresponding number to the maximum value within first group of consecutive numbers in column "C" that are exceeding 180: in this example equal to 5
and most of all I would like to generalise the problem so that to "report" the same data for all cases that are appearing in the above example;
Event Manager Status
1call A yellow
2sleep B yellow
3run C red
4jog D pink
5eat E pink
6call E yellow
7sleep F red
I need to output 4 unique Status before the event sleep. When I say unique it is compared to its previous status. So that would be [yellow,pink,red,yellow]. Notice that I could repeat yellow but not a sequential yellow. I cant use previous formula on listing unique values because I have reference point which is sleep, that i need to lookup from a huge pile of data, and i need a counter to move up. I tried vlookup with countif but i still have to define the max range. The data is huge and my menial skills are limited to excel.
I have a reference value(say 'X') at L3, now i want to select a value next higher to this value(ie..'X') in the column ''C'' say and also i have to print the corresponding values of the row corresponding to B,C,D,E,F etc.
View 1 Replies View RelatedWe've a pivot with a report filter. And, I choose multiple items from the report filter. Is it possible to get all those selected values in a particular cell ?
Currently, if I do a '=C12' (where, C12 is the report filter cell), in another cell (say, D12), i get to see '(Multiple Items)'.
language
batch
WC
FC
zh
1
36
0
id
1
0
0
[code]....
in the above table I need to the sum of WC for each Language code & batch no. eg. for Language "id" & batch 1 I need to get the SUM of WC corresponding to the criteria.
I need to count all of the unique value in column C( Supplier Name) If the Owner is Analyst on column D and the Buyer is Mark column A.
I would be needing a formula to count it and not pivot table. I dont know how to set up an array and I just have learn how to use the Countif formula.
Buyer
Parts
Supplier name
Owner
[Code]......
I would like to be able to categorise some data by reference to a list of values e.g. How do I categorise a range of retail stores by turnover :
Store A has a turnover of £8m and I have a range of turnover bands :
0 - 1M = category 1
1M - 5M = category 2
5M - 10M = category 3
> 10M = category 4
I would like to assign a category number based on a lookup against the banding table. I can do this with a formula consisting of nested if statements but that is very cumbersome and with large volumes of data it becomes very slow in recaulating all the formula.
I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.
Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.
The reference cell is H5, the Date ranges are J2 - J12, and the principal fields are: K2 - K12.
In the next post I will include the Amortization schedule.
In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).
Microsoft Excel - ZZ_Loan.Calculator-Update.1.1.xls___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6H7H8H9H10H12H13H14=FGHIJKLM1 v 1.0 PaymentApply DatePrincipal + InterestAdditional PrincipalInterest Only2 1/1/2009 833.333 2/1/2009 833.334Quarter Close Backward 3/1/2009 833.335 Close Date9/1/2009 4/1/2009 833.336 Days of Interest0 5/1/2009 833.337 Beg Principal Balance$100,000.00 6/1/2009 833.338 Paydowns$980.41 7/1/20091,321.51 9 End Principal Balance$99,019.59 8/1/20091,321.51 10 Net Principle Due$3,562.50 9/1/2009 829.2711 Interest Due & Accrued 10/1/20091,321.51 12 Interest Earned (cummulative)$7,350.66 11/1/20091,321.51 13 Interest pymts (cummulative)$7,350.66 14 Net Interest Receivable$0.00 TEST [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'm trying to write a function that returns, from a different sheet, the value in column C and row 6*current row.
For example, if the function is typed into cell B5 in Sheet 1,
the resulting value would be whatever is in cell C30 in Sheet 2
I tired the following but it gave me an error
=Sheet2!&"C"&(ROW()*6)
i have a list of 5 teams in a named range ("teams") within a worksheet.
On a userform I have 5 combo boxes.
What I would like to have is an easy way to remove a used name in the list for the next combo box.
i.e. someone selects team a in combobox1, combobox2 then has a list of team b,c,d and e. I'll be locking the combo boxes and writing code so the next one will unlock if the previous box is populated.
i also need this to work if someone puts team c in first combobox this then gets removed in combobox2 then in combobox2 they input team e then both these values are removed for combobox3.
only way I can think of doing it is creating a lot of named ranges and using a lot of if then code which I'd like to avoid.
I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C
As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.
I also need E (which has also shifted over one) to sum A,B,C, and C2.
Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.
I have an workbook (AUTHPbWB) that has a sheet(Replacement) where data is input by end-user. it also has a button(Accept) which stores data in another worksheet based AUTHPbWB cell value D5. The variant data is in range B8-h15 of AUTHPbWM/replacement. When the accept button is clicked, it searches the database sheet for the next available row and inserts the new records. The database sheet has column A that has predefined values that should not change.
what im looking to do is, on clicking the accept button and when the records update in database, the corresponding values in column A of the database sheet get copied to another worksheet (data) only for the new records updated.
[Code] .....
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
i want to match a cell data with a range of cells and if matches return the cell reference in another cell
View 3 Replies View Related