Call Group Of Data Under Defined Name Depending On Value Of Cell?
Feb 13, 2013
I have several blocks of data that I have defined under several names to make data processing much easier when writting formulas. They problem that I have right now is that depending on the value of one cell, I need to call up a given defined name block of data to be used on the calculations. These blocks are labeled as DATA400, DATA500, ... user will input information that will allow me to determined what block needs to be used, user will input data like 400, 500, ..., but to be able to call a block of data I need to reference the complete label DATA400. I have tried to concatenate "DATA" to whatever value is input by the user and use the concatenated value to call the specific blocks of information but it doesnt work.
View 8 Replies
ADVERTISEMENT
May 16, 2014
I am trying to add a macro to the ACCT DATA sheet, which calls one of two subs (AddCarriertoChecklist() - or - ClrPolChList() located in seperate modules). The macro should call them depending on whether "X" is entered in a cell in column "E". The issue lies in that I have macros doing a few things to this sheet already, and I am unable to tie this one in.
I have the two subs (AddCarriertoChecklist() - or - ClrPolChList()) working. I just can't seem to get them called. This small bit is how I was attempting to call them.
[Code] ......
Below is how I currently have it tied in with the rest of the code for this sheet:
[Code] .....
What is it that I am missing?
View 2 Replies
View Related
Jun 25, 2007
I'm trying to find a way that I could get the MAX value in a group of cells depending on a critieria. The situation is that I have a list of numbers and a list of dates parallel to it. I would like to find the MAX for, say, the numbers corresponding to the month of June. I would like to be able to change the month and get the MAX for each month on request.
Eg.
18-MAY-2007 600
01-JUN-2007 500
02-JUN-2007 400
28-JUN-2007 600
12-JUL-2007 700
17-JUL-2007 200
I need formula that will give me "600" if the month I want is June. Ideally I would not want to have to filter the data, so SUBTOTAL would probably not work for me.
View 10 Replies
View Related
Oct 18, 2013
I have a ton of worksheets that I maintain for Budget, LE, LE2, LE3. Every time we complete a Budget I need to group the LE Columns, and once we complete an LE, the budget column needs to be grouped and LE column ungrouped. So twice a year I need to make these changes. It is quite cumber some to change this for multiple tabs.
I already have a technical worksheet to dynamically change the date, naming etc. I want to be able to add a cell that will either read 200 (budget), or 300 (LE). Depending on what his cell reads, I want to group and ungroup their respective columns.
View 2 Replies
View Related
Nov 10, 2006
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
View 5 Replies
View Related
Nov 22, 2008
I have a very strange problem in DEBUG mode, because i get this error "Application-defined or object-defined error" when referring to a cell and assigning it a value so it goes to my error handler and i have a Resume Next there. It continues to go through the code whilst continuing to go to the error handler but when i step out of the function it restarts again from the beginning on the called function and then on the second run of my code it seems to WORK!?! So i'm thinking what the hell is going on, it falls over and fails the first time round and works the second time round?
In free-run mode from excel i just get a #VALUE!
View 13 Replies
View Related
Feb 10, 2010
This is the last task in a project I inherited. I have included a sample sheet with 50 rows of data which represents a real sheet with 185,000 rows. This sheet has seven columns, but only two of which apply to this task, columns A and B. Column A is a list of parcel numbers which can, and in many times does, repeat multiple times. Column B is a list of the 'sellers' name for the respective parcel number. This sheet has been sorted by parcel number, then date.
As the current 'seller' was the previous 'buyer', I need to move the 'sellers' name down one row, if the parcel number (Column A) is repeated. If the parcel number is not repeated, the name (Column B) will need to be deleted. This will leave the 'top' line for each unique parcel number blank, which I can work with. As far as the bottom name for each unique parcel number, it will need to also be deleted as there is no where for it to be transferred to. Only the name will be deleted from the 'bottom' row, not the rest of the data. The end result should not change the amount of rows of data.
View 5 Replies
View Related
Jan 7, 2009
I want to add up items in column H, but only if Column J says "yes" in the same row.
I tried a few methods as I always try to do, but I came up short with this one.
View 2 Replies
View Related
Mar 6, 2009
I have a file consisting of two columns, called, "bom ref" and "material"
I need to show the data as indicated in the file, headed required format required.
In effect where I have a 0, that is the material I need to show for every row with a bom ref. of 1,2 or 3, down as far as the next 0 but not including the 0, when I reach the next 0, the material is a new letter, and that letter needs to repeat down as far as the next 0, but not including the 0 and so on.
View 4 Replies
View Related
Jul 21, 2008
I currently written the following formula, but always have issues when adding additional depending on selection.
D8 is the selection ( Validation ) on PERF EXP FORM Tab, Once you enter the selection... you then go to the Accounts per hour Worksheet
=IF('Perf Exp Form'!$D$8="Test1 14-18 station",IF(('10220DBHE'!B9=A6),IF('10220DBHE'!C9 > 11000,11000,'10220DBHE'!C9),"N/A"),"N/A")
Perf Exp Form
Perf Exp Form *ABCDEFGHIJKLMNOPQ4 *1. GENERAL INFORMATION****************5Customer***Date / Name(s)*6ApplicationSheet Size1UP/2UPOrientationSOG/EOGFold TypeFold Plate# CH***78 1/2 x 142 UPPortraitSOGLetter/ZHeavy1***8Machine / model**Enduro 4-6 station9ConfigurationCHANNEL 1CHANNEL 2CHANNEL 310590-4720-4910 Dual DeckNONENONE11Date / Name(s)*** Excel tables to the web >> Excel Jeanie HTML 4 ......
View 9 Replies
View Related
Sep 11, 2006
i have 20 different materials which have codes assigned to them
wh = white
ivory = ivory
db = duck egg blue
etc etc
when i create an order some of the codes will be called up
i want to be able to put them codes plus the description into the worksheet Ideally into the header. if not call a1
View 9 Replies
View Related
Jan 2, 2009
I have created a color coded calendar on a spreadsheet for work that shows when a book will be published depending on when it comes into our warehouse, this is on sheet one.
I would like on sheet two to have some kind of filter or statement that would eliminate the need to look at the calendar, I.E. the user would just have to type in the date or select the date from a list and excel would show you what day the book would publish on using the data from sheet one.
I can imagine that this is hard to picture so i have attached the file as well.
View 13 Replies
View Related
Feb 10, 2014
I have 2 worksheets, 1 with a table sorted like this (in a row):
component | start date | end date | assigned to
Second worksheet with a table like a calendar with dates and people (dates in the columns and people in the rows), every component is assigned to each person by dates.
............ | 01/01 | 02/01 | 03/01
---------|----- --|-------|-------
person 1 | comp1 | comp1 | comp2
-------- |--------|-------|-------
person 2 | comp1 | comp1 | comp3
I want the assignment from worksheet 1 to worksheet 2 to be automatically. i will set the start and end date next to the component in the first table and assign it to a person/s and it will be automatically get filled in the second worksheet (the calendar) under the person/s and under the same dates as set in the first worksheet.
You can see a template here: [URL] ........
View 9 Replies
View Related
May 18, 2007
I think this is possible but cant find anything on it ........
View 9 Replies
View Related
May 18, 2007
I need a formula that displays the word "Day" or "Days" depending on cell entry i know the formlula =IF((B12)>0,"Day","") to show the word day but i want it to show "day" if a 1 is entered or "days" if any number over 1 is entered.
View 2 Replies
View Related
Jan 12, 2014
Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:
If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.
View 2 Replies
View Related
Aug 12, 2014
I have drop drown menus that allow you to select 1 of 3 choices and based on those a second set which lists the options for the first three (subset). Now depending on the second value I need to pull a set of x y coordinates that can be of varying length. '
Example
Drop down menu 1 1560, 1669, 1554. Drop down menu 2 1560-1,2,3,4 1669-a,b,c,d. This has been done now lets say I select 1560-1. I need the following to be pulled from a list, the data is a block.
1.00 1.543
1.23 -1.25
0.54 -2.75
This can be of any length.
View 6 Replies
View Related
Apr 4, 2013
I have a sheet with a country in it in A1 (validation list).
Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)
I taught to do it like this : =IF($A$1="Spain";$A$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.
I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..
View 5 Replies
View Related
May 21, 2006
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
View 4 Replies
View Related
Oct 15, 2011
I would like to know if there is a vba code doing this I have eg 100 columns starting from the column B. At the first 3 rows there are some data. Now im using the data from the rows 2 and 3. The row 2 has column numbers (TEXT formatted) from the user. The numbers starts from the 01 and goes on. But it mights be more columns numbered with the 01 or 02 or 03 or.. The row 3 has consecutive numbers and stars. The numbers start from the 1 and stop when the row 2 changes the number. The star appears when the user insert a new column (to the following example are the columns J, K, L &M )
B----C-----D-----E----F-----G-----H-----I------J-----K------L-----M---
null null ....
01---01---01----02---02----03----03----03----03----04-----04----05---
1----2-----3-----1----2-----1------2-----3-----4------*-----*----*----
I want a vba code that it has to look for the stars and then to change them to consecutive numbers depending on the above row. So, the previous exmample it would be after running the code like this.
B----C-----D-----E----F-----G-----H-----I------J-----K------L-----M---
null null ....
01---01---01----02---02----03----03----03----03----04-----04----05---
1----2-----3-----1----2-----1------2-----3-----4-----1------2-----1----
View 7 Replies
View Related
Aug 25, 2012
Just want to know that can a formula call data from one workbook and paste it as a value in another workbook? Can it be done through VBA?
View 4 Replies
View Related
Oct 1, 2009
I keep running into this error when I try to create a pivot table.
The debugger highlights the following line:
View 4 Replies
View Related
Oct 9, 2012
Trying to convert an Excel 2003 macro to work in Excel 2007.
The problem line is
Dim MyDataObject As DataObject
I suspect the problem is a Missing Reference, but I cannot figure out which one. I have the same ones (in 2007) as 2003 except for one which is not showing
Microsoft Forms 2.0 Object Library
Is this the one it needs? It is called something else in 2007?
The ones I do have ticked are
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Scripting Runtime
Microsoft XML v2.6
View 8 Replies
View Related
Aug 25, 2006
'Code1
Call movedata(1, rrow, ecol, erow)
'Contact Person
Call movedata(26, rrow, ecol, erow)
Worksheets("new").Activate
Worksheets("new"). Range(Cells(erow, ecol), Cells(erow, (ecol - 2))).Font.Bold = True
'Name
Call movedata(2, rrow, ecol, erow)
'Street Address
Call movedata(3, rrow, ecol, erow)
'city
Call movedata(4, rrow, ecol, erow)
' zip
Call movedata(5, rrow, ecol, erow)
Worksheets("new").Range(Cells(erow, ecol)).Font.Bold = True
' speed dial
Call movedata(6, rrow, ecol, erow)
the first time font.bold is set to true, it completes w/o error. The bolded line returns 'application defined or object defined error 1004'. Ive tried activating the new sheet immediately before setting bold (like the first time it gets set) but it still errors.
View 5 Replies
View Related
Oct 4, 2006
Trying to add a named range at run-time
Here's what I have so far ...
View 9 Replies
View Related
Nov 10, 2006
I have problems with setting Validation input title and message. When in column "C" I type product code, data validation of corresponding cell in column "D" changes to list of diameters allowed for that product. That works fine. But i also want to set validation message to show allowed diameters.I set it in VBA using named ranges.
But for every second line (13, 15, etc on attached file) I get an application-defined or object-defined error. The rest (14, 16 etc) works ok.
View 4 Replies
View Related
Jul 29, 2014
I am using Microsoft Excel 2010 and Microsoft VBA 7.0 on my system. I would like to eventually create a PowerPoint and insert charts generated in the Excel workbook. In the meantime, I cannot get the basic PowerPoint created.
The line in red is highlighted blue when the compile error "User-defined type not defined" message box appears.
Public Sub TryAgain() Dim myPowerPoint As PowerPoint.Application
'
' do nothing for now
'End Sub
I have set the references such that Microsoft Project 14.0 Object Library is indeed checked. The Excel file only contains this code in a module. All sheets are blank. Nothing else is written yet.
View 1 Replies
View Related
Jan 13, 2009
I had to change sheet one but everytime I open this workbook, it keep saying "Application-defined or object-defined error"
but I cannot find what is causing the bug. I have checked the name of sheet and it seem correct for me in the codes.
View 7 Replies
View Related
Jun 9, 2009
I am having some trouble with a variable range selection within a regression. I keep getting an "application-defined or object-defined error." I've isolated each statement to find that the code that is causing the regression not to work is below (the error for that line of code states that the Select method of Range class failed):
View 6 Replies
View Related
Aug 12, 2009
I'm trying to enter a series of formulas into a worksheet using vba. However, this code is giving me Run time error '1004', along with the description in the title. This is the first formula (they're all relatively similar).
View 2 Replies
View Related
