Is it possible to select cells with conditions below using formula only?
Yes, VBA is easier, but I want to know if this is possible using formula.
1. selected cells (requested): A1, A3, A6, A10, A15 .... total 20 cells.
2. the increasement between them are: 2, 3, 4, 5, 6, 7, 8 ,,,,
I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.
In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.
I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.
In the attached vacation accrual spreadsheet, I created two tables: Exempt hours earned, and also, Non Exempt hours earned. I have all employees on one worksheet. The hours accrued (Vacation Level) are different for Exempt and Non Exempt. I need: What is the best formula to autofill the Vacation Level column (H):
It needs to choose exempt or non exempt from column (C) then reference the # Years of Service column (F) to find the correct vacation days earned as defined in the two tables on the right, or those numbers could be used in the formula itself. . . I JUST DON'T KNOW! Nothing I am trying is working!
create a formula! Use the spreadsheet to maybe make name ranges? nested IF statements, or LOOKUP, or even VLOOKUP??? Or something that works. not to repeat, but. . . Nothing I am trying is working!
So for example if I have a1, a5, and a6 selected, I want to be able to select row 1, 6, and 7 in one action. Is there a hotkey for this, and if not what macro could I use?
ActiveCell.EntireRow.Select only gives me one row. I want to be able to select all of them at once.
I'm trying to write some code to select cells in a number of columns on a variable row. I have the following code to define the rownumbers for Cash and Pal:
Columns("G:G").Select
Selection.Find(What:="Total Cash", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
[Code]....
Then I can use the following code to select a cell in a single column:
Range("P" & Cash & ":P" & Pal).Select
but I need to select cells in columns P, R, T, V, X, etc.....
If I was doing this for a fixed rownumber I would use:
Range("P22:P23,R22:R23,T22:T23,V22:V23,X22:X23").Select
but because my rownumber is variable I tried to use:
Range("P" & Cash & ":P" & Pal, "R" & Cash & ":R" & Pal, "T" & Cash & ":T" & Pal, "V" & Cash & ":V" & Pal, "X" & Cash & ":X" & Pal).Select
but this doesn't work giving the Compile error: Wrong number of arguments or invalid property assignments
I'm trying to make my vLookup select multiple cells and sum them in one cell
For example
Name
No.
Example1
1
Example1
1
Example 2 2
Example 2 3
The Vlookup would return the value of 2 for example 1 and 5 for example 2
I would like to select say 2 id from sheet1 and 2 names from sheet 2 randomly and copy to sheet3, to cells a and b,have seen various codes but none seem to fit the bill.
View 3 Replies View RelatedI would like to select all cells in column A that begin with the text "SP". Some cells will be contiguous but others will not. For instance in one case, I would like the macro to select cells with the text "SP" which would result in cells A1, A2, A3, A10, A15 being selected. I am working on building a macro that will then do other things to these cells/rows so this is the first step.
The below code will select the first cell with "SP". How would I alter this code to only search Column A and select multiple cells? or totally different code.
Code:
Sub test()
Dim r As RangeSet
r = Cells.Find(what:="SP", LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then r.Select
End Sub
I have a matrix with numbers that each cell is composed by a function of two parameters (two columns). The formula is
"=COUNTIFS(T2:T99,"15",V2:V99,"14")",
So it's counts when in one cell column I receive 15 and in the other 14. For instance, I receive the number 3 - so I have three rows that match (the first column with 15 and the second with 14).
I want, when I select the cell from the matrix (table) with the number 3 (that I receive from the formula) it will highlight the relevant rows..
I have a vlookup formula that I want to copy from cell b16 to b30. How can I do that without excel adjusting all of the formula. The formula looks like this: =vlookup(b16,b1:b15,1,false). I want b16 to change with the cells but "b1:b15" I don't want to change. it should look like this in the worksheet
cell b16 =vlookup(b16,b1:b15,1,false)
cell b17 =vlookup(b17,b1:b15,1,false)
cell b18 =vlookup(b18,b1:b15,1,false)
cell b19 =vlookup(b19,b1:b15,1,false)
cell b20 =vlookup(b20,b1:b15,1,false)
cell b21 =vlookup(b21,b1:b15,1,false)
I am doing a VLOOKUP and i need to copy it over 2000 cells, problem is when I copy it, the array changes, not just the Lookup_value
Example:
=VLOOKUP(A19,Sheet2!A2:B408,2,0)
this is the first cells equation and when i try to copy it to the next cell or do a fill it changes it to:
=VLOOKUP(A20,Sheet2!A3:B409,2,0)
Obviously the Lookup_value is doing the right thing, but the table array is not. and I know i have done this before, not sure why it is doing it now.
I've tried some of the basic formulas, but this is beyond me.
I have table A of 500 rows and need to compare it to table B of 2000 rows.
A
B
C
D
E
1
D6 - 1.01
Table with PC
3
Yes
2
D6 - 1.01
Bookshelf
4
No
[Code] .......
For example with these two, from the second table to the first. IF column B equal to column and the corresponding column C equal to the corresponding column C, return for example "yes". Then I need to check the same thing for the corresponding other columns, but basically everything would be tied to B and C. The problem is the second table, larger one, is basically all over the place, I could manage to do this if we would be talking equal length tables, but I do not know what formulas I could use to check further down a row once we find an equal value.
I am using a spreadsheet for a weekly football league. I would like "Sheet 1" to have the picks for the current week with the point total at the bottom for each team (I have that set up), but on a different "Sheet" I would like to keep a running total for each team. I know how to creat a formula for the total points, but how do I get that formula to attach to the corresponding column on the same row.
EX:
A B
"Team Name" "Formula for total points"
I would like A and B to stay connected when I auto arrange them in numerical order.
I would like a cell to show either OK or No depending on the requirements of three other cells as below
G4 is the cell to show a value. For OK a date in G6 must be more than todays date, a tick must be in F6 and a date in D6 must also be more than todays date.
I can get G4 to do each section individually i.e. (A4 has todays date in it, updating automaticaly)
=IF((G6+365)
I want to create a string of 0's and 1's in the same cell in excel.
I used the function =randbetween(0,1) to generate 1's and 0's. Just wondered if there is a way to repeat this multiple times in same cell so it looks like 1 0 1 0 0 1 etc... as an example. Or a way to merge cells?
I am trying to find a way to use information in one cell in order to look up corresponding multiple values on another sheet. The problem that I am running into is that VLOOKUP only returns the first value. I need the values to be calculated in different cells going vertically, not all combined in the same cell as was in an earlier post. I am not looking to sum anything so a pivot table also doesnt work.
Sample data:
A / B / C / D
Identifier / Pub ID / Invoice # / Job #
ABCD1234 / ABCD / 1234 / A41254
ABCD1234 / ABCD / 1234 / B41254
ABCD1234 / ABCD / 1234 / C41254
DCBA4321 / DCBA / 4321 / A56789
etc.
I am looking for it to do this:
A / B
Identifier / Job #
ABCD1234 / A41254
/ B41254
ETC.
I want to enter ABCD1234 into another worksheet and have it return all of the job #'s, but I have a lot of these so I don't want to have to look up how many job numbers there are associated with it and have to enter different formulas into multiple cells. Filtering also isn't an option as there are simply too many cells to calculate.
If I have the following data set in cells A1:C9:
IDCommentFirst Unique
109876Low OilTRUE
109876Checked On 12/12FALSE
109877Checked on 12/15TRUE
109878Correct LevelTRUE
109877Correct LevelFALSE
109878PerfectFALSE
109877NewFALSE
109878Correct LevelFALSE
In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):
=COUNTIF(A$2:A2,A2)=1
In cell E2, I have this formula to count unique occurrences:
=SUMPRODUCT(C2:C9*1)
In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):
=IF(ROWS(G$2:G2)
I have a long list or work items that I track on a log on daily basis.Some work items are repeated twice or trice to input a certain comment.Therefore, when I try to calculate the total number of work items for the day using counta formula, if results in an inaccurate total since certain work items are repeated twice or trice. The work items consist of a 8 digit number.Example 8876098.
View 4 Replies View RelatedI have a excel sheet from a supplier of mine, that has a mixture of text, and numbers and more importantly "Custom format cells" that have prices in them. I need to apply a multiplication formula to each price, but they are all in different rows and columns, and it will take forever to type a formula into each cell.
Is there a way that if I can select all these cells, to add a global formula that will update all these cells?
I have a drop down list in a column called Report Type (example below).
Report Type - Drop-Down Menu in Column F
Business/Operational/Work Plan
Budget Report
Performance Report
Program Quarterly Report
Program Mid-Year Report
Program Annual/Year-End/Final Report
Service Quarterly Report
Service Mid-Year Report
Service Annual/Year-End/Final Report
Financial Quarterly Report
Financial Mid-Year Report
Financial Annual/Year-End Report
Auditied Financial Statements
In-Year Reallocation
Annual Reconciliation Report
SRI Report
Other Report
I need to count all the cells that have: Budget Report, Financial Quarterly Report, Financial Mid-Year Report, Financial Annual/Year-End Report, Audited Financial Statements, In-year Reallocation, and Annual Reconciliation Report
Is this possible with a countif formula?
I have some cells that are summed with total cost.... now i need to sort the totaled cells? How do i sort a cell that has a formula based on multiple other cells?
View 2 Replies View RelatedI'd like to apply multiple formula to a set of cells on a summary page. My summary page also contains 3 variable dropdowns, and I'd like to display data based on the text selected in those dropdowns (pulling data from 2nd tab "Variables")
The following formula works in the first instance:
=IF(AND(H4="Product Type A1", H6="External", H8="Existing"), Variables!C4, 0)
What I'm struggling to do is add additional formula to the same cell in order to deal with the remaining eventualities of the drop down variables:
Variable 1:
Product Type A1
Product Type A2
Product Type B1
Product Type B2
Variable 2:
Internal
External
Variable 3:
Existing
New
Or am I better using a VLOOKUP or something?
I have this formula populating a huge table of data for number of inspections performed, the first reference is a name of an individual, the second reference is a name of the company, and the third reference is the week ending date.
=SUMPRODUCT(((Sheet1!$C$3:$C$1000=$A2)*(Sheet1!$D$3:$D$1000=D$1)*(Sheet1!$B$3:$B$1000=$A$1)))
there are 5 of these sheets for 5 different categories. I can get these spreadsheets to populate but i then need to be able to sum from each spreadsheet all of the times an individual inspected a certain company, so one cell in each of the 5 tables.
Each time I do this it returns a 0. If i sum from one table it will return a number but if I sum from multiple tables I get 0
=SUMPRODUCT(((Sheet1!$C$3:$C$1000=$A2)*(Sheet1!$D$3:$D$1000=D$1)*(Sheet1!$B$3:$B$1000=$A$1)))
I would like to know if you can point (lots of ) cells to use a formula specified once somewhere else. The problem I have is 6 large formulas duplicated down 6 columns for 7000 rows. This, it turns out has made my file size huge ! Instead of duplicated the formula, can I tell all the cells to use *this* formula, but remembering to increment the references within...
View 3 Replies View RelatedI have four cells that contain text. All have connected check boxes with TRUE FALSE.
I need to be able to select anyone one of these cells with a check box, and have it's text appear in one separate cell eg: A1.
I have no issue connecting check boxes etc. I have no issue reproducing the text from any of these cells into multiple cells with a check box. But they have to be selectable and reproducing in one cell only (eg"A1").
I have VBA code below. Currently the code allows me in column "N" to choose more than one item in a dropdown menu. I am trying to find a way to make column K and M do the same thing. I have tried a number of ways but to no avail.
View 2 Replies View RelatedI have a big file here : [URL] .....
As you can see, the second column contains abstracts which I want to use to determine categories for each document (each line).
Categories are as followed :
Diabetes prevention -> keywords : sugar, diabet*, insulin*
Obesity prevention -> keywords : weight-loss, fat*, LDL
etc. (5-8 categories)
To target one keyword, I found this formula :
=IF(IFERROR(SEARCH("*diabet*"; $B3); 0); "Diabetes prevention"; " ")
What I would like to know is how to add more than one keywords in this formula.
I would also like to know, if it is even possible, how to put every categories in this column, so that i don't have a category per column ... It's hard to explain.
I want to have in front of each line the corresponding column. It means that the formula has to search for every keywords and select one or more appropriate categories.
I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.
Test2014.xlsx
I've got a forecast from a customer and need to summarize it with part number, quantity and date.
The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.
my output needs to be partno, date due, quantity.
The idea is to select an item i one cell, which in return will affect the following cells with the nutritional values. So far so good. I would then like, if a given amount is entered, it calculates the values.
A food item is typed in Cell A. The following cells will Index Match a table (on a different sheet), importing calculation formulas based on the food items nutritional value (Cells C - G). If an amount is typed in Cell B (i.e. 100 grams) the following cells will then calculate the correct nutritional values.
i learned to make an INDEX MATCH function, so that when i type "Gulerod" or something like that, the following cells import information from the Table tab... When the table contains fixed values, this works just fine. However - i tried to make the table (the cells) with calculation formulas (i.e. =SUM(4*B3))... this means the values in the tables are 0 untill a value is typed in B3 (in this example).
... however when i do the Index Match in my main tab, only the "0"'s are imported, and no matter what value i enter in B3 in the main tab, the following cells remains with the fixed value 0.
... so my question is - is it even possible to have the Index Match function import the calculation formula from the table and have it work in the main tab as well? Or is there any other way to make this work in Excel? Basically - to have the main sheet find the relevant data to use for calculation on a different tab/sheet, and then do the actually calculations on the main tab/sheet.