Using If Statements To Retrieve Contents Of Cells From Another Sheet From 1st Selection
Dec 10, 2013
What I would like to do is use the IF function to retrieve data from another sheet called Data which has six columns but has various amounts of data that I would also like to pick as a drop-down menu.
The initial cell has a dropdown populated with data using the Data Validation.
Example would be if the following is selected from the initial drop-down menu and then allows me to select data depending on the previous selection.
PX1 - fetch information from Data T2 to T25 and then select that data which then would allow me to select data again from say column U2 to U9.
Is this possible?
View 5 Replies
ADVERTISEMENT
Nov 30, 2009
I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:
1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case
2. the ToC has to change depending on the sheets selected.
I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.
What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).
View 13 Replies
View Related
Apr 13, 2012
I am trying to find something that would allow me to have a spreadsheet clear the contents of a certain cell based on the selection made from a data validation list which resides on the same row. This can best be described with an example.
The user selects anything but " " or "none" from the validation list, which for this example resides in C5, would trigger code to clear the contents, if any, in AA5. If " " or "none" is chosen the value remains.
This capability should be available for each row through 100.
View 3 Replies
View Related
Jun 19, 2009
I've got the following code, which evaluates a sheet for the string "Event held", and then offsets multiple columns before adding several strings.
The second part of the code is what I can't get working correctly. Each cell containing the strings inserted by the offsets are set to change colour based on their contents.
What I want is for them to change colour as soon as the strings are offset, but at the moment, I have to click on each cell before the colour appears....
View 9 Replies
View Related
Mar 25, 2014
I have contacts list that I made and want to take it to the next level, but don't know how. See attached.
The goal:
1. Highlight an entire row of someone's contact info (i.e. row 9)
2. Click the "ORDER FORM" text box to activate a macro that populates specific cells on the 'order form' sheet with the customer's contact information from the highlighted row. The information I want copied over is in red on the order form sheet. THAT'S IT!! (Oh, and 'paste value' the current date from H5 so that the date is static on the order sheet
If possible, I would like to have an error check in case no row is selected prior to clicking on the text box that pops up the note "Please highlight a contact row before clicking the 'Order Form' button."
View 5 Replies
View Related
Jul 13, 2006
If this has been requested already, I apologize, please direct me to the right thread and flog me with USB cable. I've been searching the forum for an answer for a couple of hours now for the answer to this:
I have a list of words in column A on a sheet and I'm trying to write a formula on another sheet that will check all cells on a pre-populated row and return a value if any of the words from the list on are contained in any of the cells in the row. I've attached an example spreadsheet
View 3 Replies
View Related
Apr 18, 2013
I get a report each day with a list of issues. the "group" that works the issue and the "priority". Based on these two factors, i need to do a double lookup (vlookup?) to another tab or file to match the priority and group and see what value should be brought back for each lines results. For example, if group1 had a prority3 issue, the lookup would find the value from the other sheet or file and bring back the value and put it at the end of the row where the formula is.
Attached are examples of the sheets.
sheet1.jpg
sheet2.PNG
View 4 Replies
View Related
Feb 10, 2012
Can I have a formula in A1 cell to display the No. of cells in selection (in a range) any where in the sheet. Just the No. in A1 cell is sufficient
View 4 Replies
View Related
Apr 24, 2014
I'm playing along with Excel. I discovered it doesn't like cutting and pasting in VBA. This screws up formulas in cells.
It much prefers copying and pasting. So this involves an extra step, going back to the cells you just copied and clearing them.
So I have a selection of cells and I just copied them to a different location. Naturally the location I just copied it to then becomes selected.
I would like to go back to the cells that have the dotted selection around them and clear them. If I simply use Selection.ClearContents it will clear the currently selected cells, NOT the dotted selection.
Command to clear the contents of the dotted selection?
View 4 Replies
View Related
Mar 23, 2014
First sheet has a client name and several cells of data along the same line. All of the data on this sheet is kept long term.
The second sheet is completed copied and deleted. In completing the second sheet the client name is selected of a drop down list and pulled from sheet one.
What I want to do and not sure if there is a way is that when I select a client and populate it on sheet two I would like the related data on that clients line from sheet one to populate to same named lines on sheet two.
if this is possible and if so provide a brief description of how it would work
View 3 Replies
View Related
Feb 21, 2007
If I have a pick list, or drop-down list, in cell B21 and one of the options in that list is "Clear", how can I get the contents of cell C21 to be cleared when I select the "Clear" option in B21?
Constraint... Don't want to use VBA.
I am thinking of hiding a formula somewhere other than in C21 that evaluates B21 and does an If Then kind of deal to clear C21.
View 9 Replies
View Related
Apr 25, 2008
I need to clear the contents of every other cell in a selection of 5000 rows starting with the first cell. Is there a macro that can do this for me?
View 3 Replies
View Related
Apr 5, 2009
Clients come to the shop and can ask to receive body massage, foot massage or herbs massage for 1 hour, 2 hours or more. Prices vary depending on the number of hours requested by client and option (body massage, foot massage or herbs massage). For example, one hour is more expensive than if they get two hours at once.
At the moment, our secretary has to keep records of who's massaging, what option and number of hours. From that she has to calculate how much each employee must get at the end of the day. Hence, I want to create a document to automate all this by letting her select from drop down menus the name of employee, time massage started, number of hours and the option (body massage, foot massage or herbs massage). It contains two sheets:
SHEET 1: management: see atached image: management.jpg
SHEET 2: rates: see atached image: rates.jpg (prices are not real).
In SHEET 1: Management
TOTAL in column H is what we charge customer.
TOTAL EMPLOYEE in column I is what must be paid to the employee after each massage.
TOTAL in column A and B is total price at the end of the day.
In SHEET 2: rates
There's 3 options: body massage, Foot massage, Herbs massage. For each options, there are two columns: first column is the price for client and second column is price to pay for employee.......
View 5 Replies
View Related
Jul 3, 2013
I am working with a catering form that someone else created at work. It has a drop down list of menu items available. Once an item is chosen, the item description pops up.
These descriptions are so much longer than anything else in the row (item name, qty, #, cost). Is there an option to make the full text temporarily visible by selecting the cell, and leave it cut off the rest of the time? I am trying to keep the form at one page.
View 2 Replies
View Related
Jul 1, 2008
I need a formula that'll retrieve the sheet name in order to use with lookup function ...
View 11 Replies
View Related
Nov 28, 2013
I have an excel grid which have 2 sheets, sheet2 have all the data, I am doing a macro which based on a value that I put in sheet1 is supposed to find Items which have similar value +/-3 from sheet1. I made a code which I posted below, I don't have any error message but it doesn't work as it should. I explain you with an example: If in the data grid I have the following values
11.8
67.0
25.5
18.1
13.0
if the value I look for is 12 +/-3, it doesn't find anything.
if the value I look for is 11 +/-3, it finds 11.8 but not 13,
if the value I look for is 20 +/-3, it finds 18.1,
if the value I look for is 23 +/-3, it finds 25.5,
Here is my code:
Sheet1.Cells(1, 3).Select
Dim Amin As String, Amax As String ', Bmin As String, Bmax As String
Amin = Sheet1.Cells(3, 2).Value - 3
[Code].....
View 2 Replies
View Related
Jan 14, 2013
I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!
I have provided a link to the example file below:
[URL]
View 4 Replies
View Related
Jun 26, 2012
I am using ADO to retrieve data from sheet 1 and 2 into sheet 3, within the same workbook.
The following code does the job correctly.
However, when I have two such workbooks open, say workbook 1 and 2 and run the code separately to retrieve data into sheet 3 of workbook 1 (from sheets 1 and 2 of workbook 1) and retrieve data into sheet 3 of workbook 2 (from sheets 1 and 2 of workbook 2), I get a runtime error message.
I suspect it might have something to do with locking: rs.LockType = adLockOptimistic
Sub Get_Data
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
[Code]....
View 5 Replies
View Related
Apr 8, 2008
i have a worksheet which is a form which feeds into another worksheet which acts as a database. i have a button which clears the form based on code which is essentially "clear contents".
the problem is i have a few cells where i would like to retain the formulas. i tried to do this with custom in validation but this did not work. i also tried to enter the code directly into the worksheet but this didnt work either (my skills are limited..). i am avoiding using protect sheet bc that in turn will affect many of my other buttons. is there something i am overlooking?
View 9 Replies
View Related
Apr 5, 2009
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
View 2 Replies
View Related
Jan 15, 2013
Excel macro to retrieve some valid data from one sheet to another which has some values.
Description;- I have made an excel sheet for daily stock details which contains data like stock in, out, daily production for various products. I have made a menu which contains button of stock in, out and production, and one sheet for current stock where I enter In and out. I wand a macro when I click the button from the main menu it gives me all the production details taking from one sheet and past it in different sheet in the same workbook but it should not give the details of those products which does not have and production.
View 1 Replies
View Related
Aug 5, 2014
I'm trying to build an Index-Match formula to retrieve a number from a "data" sheet onto a "summary" sheet.
In example spreadsheet, I need to retrieve the stock price on a specific row from "data" sheet, and as an alternative, the most recent price.
View 6 Replies
View Related
Mar 25, 2008
I have a column in my .XLS whose contents I want to change depending on what the contents of the cells of a different column are. However, I only want two cells from the same row to be dependent on each other. So, for example, if $A$1 reads "1" then $B$1 should read "apple" and if $A$2 reads "1" then $B$2 should read "apple"; if $A$12 reads "3" then $B$12 should read "pavement" etc.
View 11 Replies
View Related
Feb 11, 2010
I am trying to use =COUNTA(A25:A500) to retrieve the number of non blank cells. Only problem I am having is that the cells that are blank are also using a formula to gather its data. The cell would be blank but the formula is still there, so using =COUNTA(A25:A500) returns all these cells with the formula also.
View 4 Replies
View Related
Feb 28, 2009
I am working on a sheet to retrieve some cells which are not empty or equal to 0. Like..
A B
Item A
Item B 5
Item C 10
Item D
Item E 5
Item F
I want to retrieve only the cells which have got some values in column B. Like I want to get the list as follows
Item B 5
Item C 10
Item E 5
View 6 Replies
View Related
Mar 5, 2009
how to apply this function to my sheet given below. In this budget sheet, I want to create a graph which would show all the items percentage (item by item) with the values given in columns C & H only, leaving out the lines which are empty or bear 0 values.
View 2 Replies
View Related
Aug 1, 2007
I would like to test how many cells have been merged in a given cell. I know about the MergeCells property which returns true if the cell is merged. But how do i retrieve the number of merged cells in that cell (supposing it is merged, of course).
View 3 Replies
View Related
Sep 23, 2009
Sheet2, Sheet4, Sheet6, Sheet8, Sheet10, Sheet12, Sheet14, Sheet16, Sheet18, Sheet20, Sheet22, Sheet24, Sheet26, Sheet28, Sheet30, Sheet32, Sheet34, Sheet36, Sheet38
If G8 = “E” and if F8 = “H” and E8 is >=10 Copy A8:G8 and paste to sheet tab Sheet40.
If G8 = “E” and if F8 = “E” and E8 is >=1 Copy A8:G8 and paste to sheet tab Sheet40.
If G8 = “E” and if F8 = “L” and E8 is >=20 Copy A8:G8 and paste to sheet tab Sheet40.
When at anytime “D” is in G8, do nothing.
These conditions would be for each row from 8 – 3000 down the page
View 11 Replies
View Related
Dec 19, 2008
I want to end up with is when I type text in any row of the Master sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (as per column O). For example row 5's contents from the Master sheet would duplicate into the sheet named Accommodation. In addition, should the details ever change in the Master, this would need to be reflected in the individual sheets also. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie row 5's contents to be placed into row 2 of the Accommodation sheet seeing as it's the first occurrence, and row 6's would go into row 3 etc.
View 2 Replies
View Related
Dec 19, 2008
I want to end up with is when I type text in any row of the 'Master' sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (referred to by text entered in column O). For example row 5's contents from the Master sheet would duplicate into the sheet named 'Accommodation' as O5 contains the word Accommodation.
In addition, should the details ever change in the Master, this would also need to be reflected in the individual sheets. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie if row 5 had the first occurrence of the word Accommodation in column O, the contents of that row would be placed into the next available first blank row of the Accommodation sheet, which in this case would be row 2 as the headings are on row 1.
View 9 Replies
View Related
