Conditionally Importing Data From One Of Several Different Worksheets Based On 1 Factor?
May 22, 2014
I am currently working on a spreadsheet that records data from 11 different locations, with each location having it's own page within the workbook.
All of the pages are identical in terms of how the data is stored, the only difference is literally the name of each page (i.e. the location).
What I would like is to have a master page that then pulls through the relevant data, dependent on which location I choose from a drop down list on this page. As each individual page is identical in terms of lay-out, one formula will work for each page, with the only obvious exception is the page reference embedded in the formula. I know how to do a standard lookup for various figures, but I can't figure out a formula that would work with 11 different options... Is there any way to accomplish this?
View 1 Replies
ADVERTISEMENT
Apr 3, 2009
I used to know my way around Excel pretty well back in college, but I'm drawing a blank here several years later. I know there's a way to do this, but can't remember how.
In the attached sheet, I have info on Sheet 1. Sheet 2 only displays the info with LABEL=2. How can I make this sheet 2 automatically do this and update based on changes to Sheet1?
View 4 Replies
View Related
Sep 11, 2006
I am searching for a formula or simple macro to select text data from column named "SM NAME" to corresponding worksheet. I need this to populate as many worksheets as there are SM Names. ( there are usually 10 SMs)
For Example in the Master Data there are Several SM names listed. I need to extract the "ID" and "Agent" columns in the Master and populate into the workshhet with the Approriate name tab. The Master list changes regularly.
View 4 Replies
View Related
Feb 23, 2012
Im trying to import data from multiple worksheets. The data from these worksheets are scattered throughout the different work sheets.
I would like to extract the specific data from each worksheet into a master template that ive created. 80% of the worksheets templates match my master template.
Is there any way to do this without taking 1 week to complete?
View 1 Replies
View Related
Feb 7, 2014
Here is a table with values from Factor1 to Factor 5. Underneath that, for a specific index, I can mark Y or N for factor values. For instance, for index 1, Factor1 and 2 is marked as Y. The goal is to calculate total factor based on variable and factor values. In this case, variable value is 6, factor 1 =1 and factor 2=1.25). So Total factor = 6*1.25*1.5 = 11.25. If all factor values are marked as N, then total factor = variable value (Example is for Index 3)
How this can be implemented. I tried using COUNT function to count the total number of Y but that works only if all factors have same values.
Table_factors.jpg
View 4 Replies
View Related
Jun 21, 2006
I have what on the outset seemed a fairly basic task. In VBA I wanted to conditionally hide worksheets based on a value in my main worksheet.
I have 1 "main interface" worksheet with quite a few "result" worksheets as part of my workbook. Instead of scrolling left and right in the tabs menu everytime I wanted to veiw a particular worksheet I wanted to rather hide all the worksheets except the "main interface" and the relevant "result worksheet", which excel would identify based on a value in my main worksheet.
I wrote the following VBA code to do this but for some reason I cannot get it to work correctly. Perhaps I have made a simple logic error? I am fairly new to VBA. Any advice? ......
View 9 Replies
View Related
Jun 2, 2009
I have workbook that expands or shrinks in number of worksheets each time and I need to gather information from each worksheet to compile a "total" spreadsheet. The location of the cells to be copied in each worksheet vary however it is always in the same column. Managed to find a macro that will collect the information if it is located in the same cell, across all worksheets but did not manage to find something that will conditionaly copy.
Need code, that will search based on text that will be found in the worksheets and then copy the values from the column next to it (same row) to the "total" worksheet. If any of the text that is to be searched in the worksheet does not exist, then it should leave the cell "blank" in the "total". I have attached a workbook of with the example of the worksheets that I have and the result that I want to have at the end, in the "total" worksheet.
View 4 Replies
View Related
Jun 17, 2014
I need to delete line if 2 conditions are meet.
If col A is blank and col D or E is blank then I want the line deleted for lines 1no more than 100 The option of D or E has me stymied I end up with it not working right. This is what I had tried
Dim lastrow As Long, c As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To lastrow
If Range("a" & c).Value " " and Range("d" & c). Value " " or Range("e" & c)Then ActiveSheet.HPageBreaks.delete
Next c
But doesn't work
I also need macro to run multiple worksheets which varies anywhere from 12 sheets to 50 sheets.
View 6 Replies
View Related
Jan 10, 2012
Is it possible to conditionally a set of cells based on a different cell? Example; Background color of cells A2:A10 = blue if A1>0
View 1 Replies
View Related
Feb 28, 2014
What I am trying to do is create a multi-currency expense report. For example, cell B1 has a drop down list of three different currencies. Based on the currency selected in B1, I want C1 to reflect that number formatting using the proper currency.
Im sure there is an easy way to do this but I have tried using an if statement in the conditional formatting section but it does not work.
how to total the different currencies using the ifsum formula, so this is more of a presentation task, but still it has annoyed me to no end.
In the example workbook, the currency value selected in row E should effect the currency formatting in Rows F-L
View 1 Replies
View Related
Apr 29, 2009
i've been trying for teh last two days to figuire out a way to conditionally format the first cell in a row to turn a certain color based on the values of several independant cells in that row, as opposed to monitoring the whole row. I have tried numerous IF, AND, OR, formulas to no avail. The cell obviously also needs to ignore blank cells...Can this be done?
Basically I want the first cell to turn yellow if dates in certain cells are within 90 days of today, and turn red if within 30 days of today.......
View 9 Replies
View Related
Sep 29, 2006
I'd like to hide rows based on the value of a specific cell (that specific cell will always be C14, and it will have up to 8 variables selectable from a drop down list).
For example, if C14 = Innovative/exploratory, i'd like to hide rows 15-45. If C14 = Technical Service, i'd like to hide rows 15-32, and 42-44. I'm not familiar with VB, but am a quick study -
View 4 Replies
View Related
Nov 22, 2007
I am trying to hide rows based on the contents of column BS. It the cell is empty (not just zero value, but completely empty of all formulae), then I want to leave it unhidden, then if the cell value is less than the value in cell E2 I want to hide the row. This code doesnt seem to like the first if...then next i
Sub hiderows()
Dim i As Long
Set v = cell.Value("$E$2")
' Turn off screen refresh for speed
Application. ScreenUpdating = False
' Get last row Col BS
For i = Range("BS2000").End(xlUp).Row To 15 Step -1
If IsEmpty(Cells(i)) Then Next i
ElseIf Cells.Value(i) < v Then Rows(i).Hidden = True
End If
Next i
' Turn on screen refresh
Application.ScreenUpdating = True
End Sub
View 4 Replies
View Related
Feb 6, 2008
I'm trying to figure out how to lock a range of cells (all in a row) when some puts a specific character in one of the cells. I have a sheet where I want people to enter data. Once they put a 'C' in the last column to indicate that the entry is complete, the whole row should be locked so they can't change any of the cells for that entry. There are some good pointers already on the forum to help me get the basics:
Conditional Cell Lock Unlock
http://www.ozgrid.com/forum/showthread.php?t=66397
http://www.ozgrid.com/forum/showthread.php?t=76379
However, all of these point to specific cells rather than a range of cells that can be located close to the target of the change.
View 3 Replies
View Related
Jan 18, 2013
I had a list of people on sheet1 of my workbook, and on sheet 2 a table with a list of names and their department.
I started off by wanting to use VLOOKUP within the conditional formatting feature of Excel to find anyone who worked in "finance" in sheet 2. I could get this formula to work on the sheet but as soon as I put it into the conditional format feature it wouldn't work.
Trying to find a work around I added a column in sheet 1 where I inputted the VLOOKUP formula and caused it to display "1" if the person worked in finance. It looked like this:
A: Person's name
B: Value of 1 or 0
The idea was to hide column B and use conditional formatting to say "if column B2 = 1 colour column A2 blue". Now I could get this to work on a single row but couldn't make it work over a range, ie. "if cells A2:A150 have a 1 in the column next to them colour the relevent cell blue"
I feel I'm missing the obvious. I found quite a few answers online but non actually specified how to get the conditional formatting to recognise the relevent cell it should be looking at in a range.
1) How would I get conditional formatting working over a range
2) Whether it is possible to use VLOOKUP within the conditional formatting function of Excel.
View 3 Replies
View Related
Dec 1, 2009
Format the number in Column AL to '[hh]:mm' when Column C's value is ="P/T", otherwise format to 'General'
Column C's value is referenced from another sheet in the same workbook via a VLOOKUP function.
Column AL's value is based on an IF formula which goes like this:
=IF($C4="F/T",SUM($AJ4)+($AK4/2),SUM($D4:$AH4))
Basically the above formula asks if Column C's value is F/T then count (because if F/T then the corresponding values in that row are whole numbers). If not, then SUM (because if P/T the corresponding values in that row are set to [hh]:mm format).
I have another sheet in the same workbook which has code (quoted below) which does something similar but I don't understand it enough to get it doing what I want for the sheet in question.
View 6 Replies
View Related
Jun 5, 2013
I'm quite new to VBA, but I am attempting to get a Forms ComboBox to appear or disappear based on whether a certain cell (P7) reads YES or NO. P7 in turn updates in turn based on a user-selected value. As of now, the ComboBox only appears or disppears if I go back in and out of the formula I entered into P7. Basically, I want my ComboBox to dynamically update based on the value in P7. That may sound a little muddled, so here is my code for the ComboBox:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("P7")) Is Nothing Then
If UCase(Target) = "YES" Then
Me.Shapes.Range("Drop Down 30").Visible = msoTrue
Else
Me.Shapes.Range("Drop Down 30").Visible = msoFalse
End If
End If
End Sub
View 3 Replies
View Related
May 24, 2013
I have a large workbook with 2 rows of data I want to format (R25:R530, M25:M530). Row M is already populated, row R is new data that I am entering. When the value of a cell in row R is larger than its adjacent cell (R25:M25) in row M, I would like the font to turn red. When the value is smaller, I would like the font to turn green.
With the amount of time I've spent attempting to conditionally format, I could have gone through and formatted each cell individually by now!
View 8 Replies
View Related
Dec 6, 2013
I'm trying to conditionally format rows of data based on duplicates in the first column, then filter the results. I have a table of data with mutiple variables assigned to different "headings" that looks a little something like this:
A
B
C
D
[Code]....
[selects the table, then GoTo Special selects the blanks, fills in the blanks with the cell above, then Copy and Pastes As Values the entire table again to fill everything in]
I’m stuck on how to input a formula into the conditional formatting window that will:
Format the text to white in columns A, B, and E based on there being a duplicate above that row in Column A only. i.e. conditionally format the values with a * below:
A
B
C
D
E
[Code]....
If I use the conditional formula I found: =A1=A2, then cell E3 gets made white text when I don’t want it to, hence the “referencing column A” part of the question (Column A is always a unique ID number whereas Column E can have a duplicate in the row above). [Edit: Why can't I type Enter or put a line break here... I'll try re-edit at home...] When I go to filter on Column C for YYY again, the conditional formatting needs to realise to un-white the text, which is my next headache because it means conditional formatting that acts relative to hidden rows as a result of a filter... Oh and did I mention the client wants this done in 2003? This is an afterthought though – as I can force them to use 2010 if need be
View 6 Replies
View Related
Nov 29, 2012
I have a long row of data with words/numbers in each cell. Whenever I see a cell starting with W, I want to add up the value 2 cells to the right of that cell starting with W.
For example, if these following 9 cells in the same row read W1, 90, 100, M2, 90, 100, W2, 90, 40, whenever the cell starts with W, I want to sum up the value 2 cells to the right of that cell starting with W. In this case, I am adding up the values 2 cells to the right of W1 and W2, which is 100 + 40 = 140. How can I do that?
View 2 Replies
View Related
Jul 10, 2014
I have a bunch of different worksheets that I need to pull a single cell from (lets say A1). On my summary sheet (first sheet) I have a list of all the worksheet names. Is there a macro that can pull data by referencing their names in the summary sheet.
Example: Column A has a list of all the worksheet names in my workbook. I want to extract cell A1 from each worksheet based off the name in Column A and be put into Column B. Otherwise, you would have to go through each tab and link it (I am working with 100+ tabs).
Column A Column B
PIN
CID
SIP
View 9 Replies
View Related
Jan 14, 2008
I have a spreadsheet of ~5000 rows of data that I would like to separate based on the product code column (11 product codes - 43,301,304,313,332,334,979,984,985,986,992). As it stands, I've been copying the sheets, then removing the data by filter, but doing this every week seems to be a waste of time.
View 9 Replies
View Related
Nov 15, 2009
I have a workbook with data extracted from our ERP (transaction sheet), and a list of cards (card list sheet) which ties the relevant cards to companies and vehicle.
I would like a macro to extract all transactions in the "transaction" sheet into a new file (see "unit" sheet), with the relevant company name, report dates, vehicle number, card no, date, time, polling point name, volume, unit rate. The "Amount" column will be the volume X unit rate.
Vehicle numbers can only extracted from "card list" sheet, and not found in the "transaction" sheet.
these are the details I need:
- all company's transactions details (not sorted in the "transaction" sheet) should be extracted into new individual company's excel file
- headings I need are shown in the "Usage" sheet (company name, vehicle number, etc)
- depending on the number of transactions, there should be a " subtotal" row below the last transaction (as shown) of each vehicle
- with all vehicles extracted from each company, a grand total will be added
- filename should be created with company name
View 9 Replies
View Related
Mar 17, 2009
on the "all data" sheet, there are two rows that have a Y for file missing - these are to be marked, so that they may be focused on finding their file
currently, i am doing as such and then filtering by that column to print out a list, and would like a simplified solution (populate sheet "missing") with just those rows who have missing files - so that another user may easily view such data without having to fondle around with the master list
View 2 Replies
View Related
Apr 4, 2014
spliting of main data By column criterias which start from column E1:L1 something like Pivot Table in new worksheet, but based on formula functions.Each worksheets have to based in one of this column D1:K1 headings. That also have to rapidly change with main data table.
View 9 Replies
View Related
Mar 12, 2014
We are an office furniture company and we want to hold a master list of all stock. The columns we require are:
Product ID
Product Description
Product Grade
Product Price
Sold
The Sold column would be a YES or NO answer, and based on that I want the data to split into two additional worksheets, one with all the unsold items and one with all the sold items. So really I need to know how to split the data based on whether the line has a YES or a NO in the sold column.
The data needs to update instantly so if I change a product from unsold to sold it then comes off the current available stock tab.
View 3 Replies
View Related
May 22, 2014
I need to split the data into worksheets (see attached) by the Advertiser column and then by the deal year and deal code columns. I need each worksheet to be named per advertiser and deal year_deal code. I took off and replaced the data since it is sensitive information.
creating a macro or implicating one into the workbook to run for future reporting.
View 13 Replies
View Related
Jan 17, 2014
I have tried to split using KuTool and ASAP Tool but they dont work. They can only split base on #of row. Between each of the group of my data, there is a empty row that split them. I want to split every single row from A:H to a new workbook. Why A:H becuz from A1:A8, the A5 is empty. It has to be in range, specify by me, so if the condition is met as empty from A:H, then split from there and keep going on the whole spreadsheet.
View 8 Replies
View Related
Feb 16, 2010
I have data on a worksheet (Interface) that I need to copy over 7 other tabs for analysis.
("Module_1", "Module_2", "Module_3", "Module_4", "Module_5", "Module_6", "Module_7").
The linking Field on the Interface tab defines what row I need to paste the data to on Module 1-7. The column headers are the same on both the interface and other tabs (YES, NO, N/A, BLANKS, STANDARDS MET, STANDARDS NOT MET, NOT APPLICABLE)
The tab is selected based on the row
Module 1Medication Management
Module 2Privacy
Module 3Process
Module 4Patient Safety
Module 5Medical Records
Module 6Infection Control
Module 7Environment of Care
Im trying to modify code written for a similar purpose with no luck. This is the code I'm trying to modify. I know I'm not on the right track yet.
View 2 Replies
View Related
Apr 11, 2012
I am looking to split data in a worksheet into separate sheets based on Column C which contains Either Babycare or Homecare or Industrial or Professional. Eventually there maybe more names. If possible I would also like to name each sheet.
My VBA skills is limited to this particular task.
View 2 Replies
View Related
