Returning The Value Of Two Cells Chosen

Mar 14, 2009

I am trying to use lookup to return the value when the condition of two cells are met.....

View 10 Replies


ADVERTISEMENT

Function To Sum Cells Depending On Month Chosen

Jun 3, 2014

Is there anyway that when I choose (for example, Jan) the result shows the sum of January numbers. On the other hand, if I choose Feb, the results would be the sum of February. See Mar on cell C8 and Feb on cell E15.

A
B
C
D
E

1

1st
2nd
3rd

2
Jan
1
2
3

[Code] .......

View 2 Replies View Related

Copy Cells By Chosen Background Color

Feb 28, 2008

I am using the following code to copy all rows that have a cell with a background color to another worksheet. In my first workbook this works just fine. In my new workbook, however; it is not working correctly. There are 111 rows that have a cell with a background color (all the same color). When I run the macro it is only copying the last row that has a colored cell. I have been trying to figure out the reason for the last 3 hours and I give up.

Sub CopyColor2()
Dim rReply As Range, rCell As Range
Dim lCol As Long

Set rReply = Application.InputBox _
(Prompt:="Selct a single cell that has the background color you wish to copy", Type:=8)

View 3 Replies View Related

Import Specific Cells From A User Chosen Spreadsheet

Nov 24, 2009

I need to import some spreadsheets that are sent to me into a master database spreadsheet. I have attached both below. The complaintstest.xls is the master database and the other is the sheet that will be imported. What I would like to happen is when the macro is run, the user will be able to select the sheet they would like to import through a dialog box. After the sheet is selected the macro will import the cells from the selected sheet into the master database in a particular order. In the master database example on row 2 I have listed the columns from the sheet to be imported in the corresponding column on the database sheet. This will macro will be run a couple of times a week so the macro will have to find the next empty row to start the import on.

View 14 Replies View Related

Lookup Description For Chosen Value In Table With Merged Cells

Sep 11, 2007

Is there a way to capture ALL the description in Column D whenever I change the code? This mean if I change the code to 546CC0004, the description will change
Currently I copy manual from this file to another file. I Have more than 100 code with different Description. a Sample is enclosed.

View 4 Replies View Related

Join Chosen ComboBox Text With All Used Cells In Range

Oct 16, 2007

I am trying to find a way to create a macro that will take the data selected from the combo box and concatenate it with the data found in cells.

For Example:

Combo Box Selection - "Test"

ID (Column A) - Before
12345
98765
99999
55555
empty cell
23232

ID (Column A) - After
12345 Test
98765 Test
99999 Test
55555 Test
empty cell
23232

View 9 Replies View Related

Populate Cells Based On Chosen Drop Down List Value

Apr 25, 2008

I am attempting to populate a row of several cells on a worksheet using a drop-down list, using data from a seperate worksheet in the same workbook. The worksheet containing the data will be hidden (I do not think that matters in this case). Do I place a VLOOKUP function in the first worksheet cells? see attached sample

DropDownSample.xls

As a follow-up question, as time goes by, the data in the source worksheet will be appended with new items (additional rows of data). As each new item is added, will I need to edit the formulas, or is there a way for Excel to dynamically add the new data? This might be stretching my wish list a bit but I thought I'd ask.

View 3 Replies View Related

Link Cells To Chosen Workbook Macro Code

May 19, 2008

I am trying to write a code in VBA to collect data from several weekly workbooks, and list them up in a monthly workbook.

These weekly workbooks change names depending on the week, for example, we have:

Report week 1.xls
Report week 2.xls
Report week 3.xls
Report week 4.xls

Each report has the same layout, so the data I want will be in the same cells. However, as the report names change as the weeks in the year go on, I can't write the code for weeks 1-4 as they will not work with week 5 onwards!

I have found out how to chose a file of my choice using:


Sub GetImportFileName()
Dim FInfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant

' Set up list of file filters
FInfo = "All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5 ......................

View 9 Replies View Related

Compare Two Cells And Returning OK Or NG Value

Jun 9, 2014

I'm trying to match and make judgement for my excel file and return as "OK" or "NG"

Eg.: New Picture.png

View 6 Replies View Related

Returning A Range Of Cells?

Jun 3, 2009

Just for fun (and a massive headache) - I am trying to do the following

=IF(INDIRECT("'"&$Q$1&"'!E2")="Table",IF(INDIRECT("'"&$Q$1&"'!H2")="Yes","Graph","Table"),IF(INDIREC T("'"&$Q$1&"'!E2")="Descriptive",INDIRECT("'"&$Q$1&"'!J2")," "))

Where Q1 is the name of the worksheet I want to access.
Where E2 is the type of information I am trying to display (Table or Descriptive)
Where H2 is if it needs a Graph or Not
Where J2 is a comments field

What I am wondering is if the following values are set

Q1= May-09
E2 = Table
H2 = No

How can I get it to return the information contained between cells P2 to S3 on worksheet May-09

View 3 Replies View Related

Returning Two Cells Of Information If

Jun 28, 2007

Joe Cheddar 1 2 1
Juan Valdez 1 3 2
Sam Vidal 1 1 2
Betty Juno 2 4 2

...there is only one "1" in a column. So if column 1 has three ones it doesn't return anything. Columns 2 and 3 though have only one occurance of "1." What I need returned is for column two, "Sam Vidal", and for column three, "Joe Cheddar." Column one would show "No Winner"

View 13 Replies View Related

SUM Is Returning Zero On Cells That Have Formulas

Oct 31, 2011

I have a column of formulas I'm trying to sum. The SUM function gives me zero.

The formulas are simply pointing to another place in the spreadsheet so the contents of the column look like this:

=D11 (And it displays what's in D11... $1432 etc etc)
=D28
=D30

When I sum those I get Zero. Is there a better way to do this? All cells are formatted as currency

Also... cell D11 is actually a sum of different cells. (As are the rest of them) So I guess what I'm saying is that I'm trying to display the value in cell D11 and D28 and D30 and sum up the total.

View 7 Replies View Related

Returning First And Last Non-empty Cells Of Column

Oct 3, 2008

Ok, so I have a column of data in 24hr time format over a 24hr period from noon thru midnight and back to noon again. It has been necessary to make some of the cells in the column blank, and I am having difficulty extracting the first and last non-blank entries of the column (basically I want the earliest and latest time in that specific 24hr period). The time data is in order, so for example, some of the first values in the column are 14:30, 21:10, and 22:40, while some of the last values in the column are 02:20, 05:50, and 11:00, therefore the values I require are 14:30 and 11:00.

Any help that anyone can give on this matter would be greatly appreciated, but until then I guess I will keep plodding through the help sites and playing with formula until I spontaneously combust.

View 6 Replies View Related

Max Index Lookup Returning Two Cells?

Oct 19, 2007

I have a formula I need to modify.

In column b I have a list of items by number, each one is unique
In column c I have a list of items by number. In this column a item may not be listed or listed 1000's of times.
In column d I have a list dates for each of the items listed in column c
In column e I have a list of prices for each of the items listed in in column c

The idea with the orginal formula was to look up the item and return the date

=MAX(INDEX(($C$2:$C$39999=B2)*(D$2:D$39999), 0))

Now I need to get it to return the most recent date from column d and the dollar amount right next to it from column e. How would I do that?

I trying to return the most recent date and price for a item.

I thought about breaking this into two formulas and having the second formula read the date that was returned from the above formula and the item number from column b and returning the value from e, but got stuck. I was thinking a lookup function with two criteria.

View 6 Replies View Related

Returning A Row Of Cells Based On A Color

Apr 25, 2007

I am having a bit of a problem writing a formula that works under the specific conditions that I need.

I have a range of 6 cells, A1:A6 which contain text
I have formatting that turns that row red based on specific conditions. The problem is that if A1:A6 is red, I then need the text in those cells to be copied to Sheet 2 B1:B6.

View 9 Replies View Related

How To Get Referenced Cells Returning Blank Cell Instead Of Zero

Apr 6, 2014

Referenced cells returning zeros: can these be made blank or string data?

But why I still get the value 0 instead of blank cell when I press ctrl+shift+enter?

My formula = IF(U15="Bank", PROPER(E15),"")
and E15 = VLOOKUP(B15,'Customer Data'!$D$6:$K$100,3,FALSE)

View 5 Replies View Related

Nesting OR, AND Within IF: Comparison Amongst Several Cells And Returning A 1 If True

Dec 24, 2008

I'm trying to do a comparison amongst several cells and returning a 1 if true. if D25 is blank or does not equal Yes, Mixed or SP3, and if both F25 and G25 are blank, the output the value 1. First try:

=IF(AND(D21="",D21<>"Mixed",F21="",G21=""),1,"") - This seems to ignore the Mixed and outputs a 1 even if D21=Mixed.

Second try:

=IF(AND(OR(D25<>"Yes",D25<>"Mixed",D25<>"SP3"),F25="",G25=""),1,"") - same as above, if D25=Mixed then value of the cell is 1.

View 2 Replies View Related

Create Summary By Returning Two Cells From Multiple Worksheets

Apr 17, 2013

I am using Multiple worksheets ( 30 to 35 ) of same format to calculate Individual scores of an event. Each sheet contains Participant Name in C4 and Score in E 21.

Macro to provide a summary sheet which returns C4 & E21 in all the sheets..

The score in E21 is calculated thru another macro done with excel recording feature..

View 4 Replies View Related

Returning A Value If The Dates/times In Two Cells Are Between X Amount Of Hours

May 18, 2009

I have a spreadsheet used for calculating information based on the dates specific shifts are requested/cancelled by our clients. I have a formula for working out if a date & time of cancellation is less than 48hrs notice of the shift starting. This is because we have cancellation fees based on this.

What I have is this formula: =IF(A16="","",IF(INT(A16)-INT(G16)<2,1,"")) that returns a 1 if that shift is cancelled within 48hrs notice. This works fine but I have to now change the notice periods to the following:

72hrs+ - return 1
48hrs-72hrs - return 2
13.5hrs-48hrs - return 3
0-13.5hrs - return 4

edit this formula to take this into account? I figure it's using multiple IF's and changing the <2 into something else like the number of hours but I'm not sure of the exact syntax.

View 4 Replies View Related

Returning String From One Cell Based Upon Multiple Cells

May 22, 2013

I need to return the specific text of a cell based upon the text in multiple cells.

Worksheet as follows:

Asphalt Sub:
BLDG1-Backwash 1
*BLDG1*
*Sub*
BLDG1-Backwash 1 OverEx
*BLDG2*
*Sub*

[Code].....

I have three columns thus far. I need to add another column and search all items in Column A using the criteria in Columns B and C. For instance I need to search for BLDG1-Backwash Sub: and place it into column D using the criteria in Columns B and C.

If possible, I would also like to remove both the words, BLDG1 and Sub from the result so then it only shows "Backwash".

My spreadsheet is much larger than this and I have muliple buildings I am dealing with, sometimes 30-40.

View 4 Replies View Related

Comparing Values And Returning Info From Separate Cells

Sep 18, 2013

If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).

Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
D16 (answer cell) =John
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45
D16 (answer cell) =Bob

View 2 Replies View Related

Matching Cells Across Sheets And Returning Color Value Based On The Sheet?

Dec 24, 2013

I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail. Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?

Excel Help.xlsx

View 8 Replies View Related

Comparing Two Columns And Returning Number Of Cells In One Column With Higher Values?

Apr 8, 2014

I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.

So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.

View 3 Replies View Related

Referenced Cells Returning Zeros: Can These Be Made Blank Or String Data?

Aug 30, 2007

I have a summary worksheet consisting of columns that are referenced to other sheets within the same workbook.

If the cell in the 'referring' (original) sheet is blank then the forumula returns a zero instead of a blank in my summary worksheet, which is messing up some other calculations.

Is there a way to make these cells truly blank, or possibly into string variables instead, so that they do not interfere with my calculations?

View 13 Replies View Related

Comparing Customer Names In Rows And Returning Text Data In Different Cells If Same

Jan 26, 2010

I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:

Sheet 1

A1 - Mary Jones, B1- Internet Donation
A2 - Mary Jones, B2 - Silent Auction Purchase
A3 - Mary Jones, B3 - Event Ticket Purchase
A4 - Gary Jones, B4 - Foundation Grant
A5 - Gary Jones, B5 - Internet Donation
A6 - Sara Parker, B6 - Event Ticket Purchase

I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:

Sheet 2

A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3
A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes

Does this involve IF, MATCH or VLOOKUP formulas?

View 9 Replies View Related

Returning The Contents Of A Non-empty Cell In A Range Of Empty Cells

Jan 8, 2008

I have a long range of cells (U3:AX3), all of which are empty save one. Is there a way to search through the range of cells, and return the contents of the one cell that contains text?

I would do this with a series of nested IF statements if there weren't more than 30 of them!

View 9 Replies View Related

Save As PDF To Chosen Folder

Jun 17, 2013

I'd like my macro to pop up the window that'd ask where I'd like my pdf file (exported from excel) to be saved. How can I do that? I understand that the code below will assign the name for the file and save it to desktop.

Code:
Sub SavePDF()
Dim MyFullName As String

MyFullName = "C:UsersqlarimerDesktopFilename.pdf"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFullName, _
Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

End Sub

View 1 Replies View Related

Insert Chosen Pictures To Every Nth Row

Aug 18, 2008

I need to insert multiple images into a worksheet every 45 rows in and a specific column. I have sourced from other threads a means to insert multiple images but they are all inserted in the one cell.

What I require is the means to either place them directly into specific cells or move them to the correct cells once in the worksheet.

As you will see I am also attempting to resize the images once they have been inserted.

Below is the code I have thus far.

Sub Insert_Pict()

Dim Pict As Variant
Dim ImgFileFormat As String
Dim PictCell As Range
Dim Ans As Integer
Dim newPicture As Shape

View 9 Replies View Related

Go To Range Chosen: ComboBox

Oct 28, 2006

I need to go to named cells in a workbook based on a selection in a combobox - I just can't seem to work out the coding -

View 4 Replies View Related

Print Chosen Sheets

Oct 29, 2006

I have a workbook containing over 75 sheets. I would like to print only a select few of those sheets using a macro. I did download the "sheets to print" workbook submitted by Dave Hawley ,and have tried (in vain) to adapt the code so that it does not display ALL of the workhseets in the userform box. I want to limit the sheets displayed in this box to onnly those that I name.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved