Multiple Select Cells With Formula Only

Sep 29, 2008

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 ,,,,

View 9 Replies


Select Multiple Cells Based On Multiple Criteria?

Sep 25, 2013

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.

View 1 Replies View Related

Formula To Select Data Using Multiple Criteria

Feb 11, 2010

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!

View 3 Replies View Related

Multiple Active Cells - How To Select All Corresponding Rows

Apr 19, 2014

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.

View 1 Replies View Related

Select Cells Across Multiple Columns On Variable Row?

Nov 25, 2011

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:

Selection.Find(What:="Total Cash", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _


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:


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

View 4 Replies View Related

VLookup Select Multiple Cells And Sum Them In One Cell

May 16, 2014

I'm trying to make my vLookup select multiple cells and sum them in one cell

For example




Example 2 2
Example 2 3

The Vlookup would return the value of 2 for example 1 and 5 for example 2

View 2 Replies View Related

Select Random Cells From Multiple Sheets And Copy

May 10, 2014

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 Related

Macro - Select Multiple Cells By Searching For Specific Text

Nov 8, 2013

I 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.

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

View 7 Replies View Related

Highlights Multiple Rows If Select Cell (formula Cell)

Oct 3, 2013

I have a matrix with numbers that each cell is composed by a function of two parameters (two columns). The formula is


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..

View 3 Replies View Related

Copy Formula To Multiple Cells

Feb 11, 2009

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)

View 2 Replies View Related

Copying Formula Over Multiple Cells?

Oct 4, 2011

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



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:


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.

View 4 Replies View Related

Complex Multiple Cells Comparison Formula

Jan 16, 2014

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.


D6 - 1.01
Table with PC

D6 - 1.01

[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.

View 5 Replies View Related

Formula For Connecting Multiple Sheets And Cells?

Sep 25, 2011

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.

"Team Name" "Formula for total points"

I would like A and B to stay connected when I auto arrange them in numerical order.

View 1 Replies View Related

Formula To Include Multiple Requirements In Different Cells

Aug 30, 2013

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)


View 2 Replies View Related

Multiple Formula In One Cell / Merging Cells?

Nov 7, 2013

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?

View 3 Replies View Related

Formula/VBA To Return Multiple Values In Different Cells

Nov 7, 2008

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


I am looking for it to do this:
A / B
Identifier / Job #
ABCD1234 / A41254
/ B41254

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.

View 11 Replies View Related

Concatenate Multiple Cells With Array Formula

Jan 21, 2009

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
109878Correct LevelFALSE

In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):


In cell E2, I have this formula to count unique occurrences:


In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):


View 9 Replies View Related

Formula To Calculate Same Numbers As One In Multiple Cells

Oct 1, 2006

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 Related

Apply Global Formula To Multiple Random Cells?

Aug 7, 2012

I 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?

View 3 Replies View Related

Countif Formula For Multiple Variables In The Same Range Of Cells

Jun 4, 2014

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?

View 4 Replies View Related

Data Sorting - Formula Based On Multiple Other Cells

Jul 1, 2014

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 Related

Excel 2010 :: Multiple Formula For Single Cells?

Feb 18, 2014

I'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:

Variable 3:

Or am I better using a VLOOKUP or something?

View 2 Replies View Related

Summing Multiple Cells Populated By Sumproduct Formula

Nov 9, 2012

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.


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


View 1 Replies View Related

Multiple Cells Pointing To A Single Reusable Formula

Mar 30, 2007

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 Related

Select One Text Cell From Multiple Text Cells With Checkboxes Reproduce Into One Cell

Jan 24, 2013

I 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").

View 1 Replies View Related

Multiple Dropdown Select (multiple Columns)

Mar 4, 2009

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 Related

Using Formula To Target Multiple Keywords In Order To Categorize Cells

Jan 15, 2014

I 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.

View 2 Replies View Related

Formula To Lookup And Sum Cells Based On Multiple Criteria In Row And Columns?

Jun 17, 2014

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.


View 3 Replies View Related

Formula To Report Back Cells That Meet Multiple Criteria?

Feb 16, 2014

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.

View 4 Replies View Related

Importing Formula / Code From One Cell To Another To Affect Multiple Cells?

Mar 5, 2014

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.

View 5 Replies View Related

Copyrights 2005-15, All rights reserved