Variable Data Referencing
Apr 23, 2009
I have a word table(s) which I need to paste into excel, its a quotation sheet and this document always has the same basic format. I then need to copy across specification items to another sheet between 2 points in column A ie between points TEXT1 (A25) and TEXT2(A40) so cells A26:A39 in this instance but this can be between 1 or 100 items.
Text 2 is the start of a costing section and wouldn't be transferred.
What i am looking to do is write a formula which will look in sheet1 at column A and find "TEXT 1" at A25 then copy items across until it comes to "TEXT2" at A40 but is flexible enough to cope with various numbers of items.
View 9 Replies
ADVERTISEMENT
Sep 11, 2009
need to solve the problems in this
View 2 Replies
View Related
Jan 22, 2006
I have a SS I would like to reference a cell in a column which changes in
length. In this case, I would like to reference the most current date in a
column.
Is there a simple method for doing this, as the data will routinely be
different lengths in that column.
Further, there is Data on the Horizontal that I would like to reference to
another cell in another sheet that would correspond to this date. Can anyone
give any suggestions as to the best way to accomdate this?
View 11 Replies
View Related
Nov 29, 2012
I have a challenge with Vlookups!
I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02
I then have another workbook which has employees in column B and the client code in C3.
The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.
I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.
View 3 Replies
View Related
Jan 18, 2009
I am trying to clear the contents in a rows reference by a variable (rownum) using the
View 2 Replies
View Related
Oct 19, 2012
I have created a userform that once the user clicks the okay button, the information is transferred to a worksheet template named 'PO Template', copied, and renamed. That portion works great. I then have code for a summary worksheet named 'PO Log' that finds the next empty row on the log and enters a formula referencing back to the newly created 'PO Form' (note - 'PO Form' is a variable as the actual worksheet name changes with the user input). The formula on the 'PO Log' references the 'PO Form' literally, which gives me a REF! error because there is no sheet actually named that. I've tried defining POForm in the code as a string so I can enter it into the formula code but I get an error because it's already defined as a variable. I've entered the sub routine for clicking the okay button below:
Code:
Sub OkayButton_Click()
'Make PO Template Active
Sheets("PO Template").Activate
[Code]....
View 2 Replies
View Related
Jul 18, 2006
I have a set of worksheets (Sheet31 through Sheet49 by codename) produced by a machine in the lab. I want to create a summary sheet that references cells in those worksheets.
Rather than go through the annoyance of doing this manually 40+ times I'm working on creating a macro to populate the summary sheet for me.
This is what I have so far
For J = 31 To 49
ResultSht = SheetJ.Name
Sheet1.Activate
ActiveSheet.Range("A4").End(xlDown).Offset(1, 0).Select
It hangs up on the "SheetJ.Name" however. My objective is to be using the object SheetJ, where the object SheetJ is the codename of the results sheet I'm trying to reference (for example, in the first pass through the loop it would be Sheet31). Could someone point out my mistake in this object reference?
View 9 Replies
View Related
Jul 11, 2014
I've only done a small amount of macros with VBA.
I want to create a macro to insert 12 cells left of a column labeled "This Year" As the spreadsheet grows (by 12 columns @ year) the "This Year" column moves to the right. Thus I need to reference the range off of that column and then insert 12 columns directly to the left of it each year. Can I somehow reference the label "This Year"?
I then need to enter the month labels in the new columns row 8.
My problem is trying to reference off the "This Year" column.
View 8 Replies
View Related
Oct 30, 2008
I have this Macro for Excel which imports 1 file C:datafilesuser1-data-1.txt into cell E52:
View 6 Replies
View Related
Jul 9, 2012
I have some VBA code that formats my sheets and works fine, but the issue is that I run this same piece of code more than once in different sheets with the only difference being the formula references a different cell.
' Green Cells indicate that the supervision is within the year
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=Datedif(D8,today(),""m"")
View 4 Replies
View Related
May 3, 2012
I have several non-modal userforms in my App, some of them have date-fields that require manual entry typing of dd/mm/yy etc (No single userform has more than one date-box in it, this I think may be pivotally useful)
Now the Userform 'Calendar' that is built on the class of the same (cCalendar) name, has the write value line 'ActiveCell.value = theCal.value'
I'm looking to change this to refer to the correct userform.Textbox value, depending on which form is open.
I would imagine I could simply have a global string, whose value is set (or re-set) whenever a Userform is initialized (some sort of 'ActiveUF.value = Me.Name), where I get lost is referring to the components by name, so as to have a case statement by where I go:
Code:
Private Sub theCal_AfterUpdate()
Select Case ActiveUF
Case "AddForm"
application.vbe.components("AddForm").controls("AddFormDatePicker").value = theCal.value
Case "EditForm"
'.... etc
end select
end sub
better way of doing this (instead of passing around the userform name as a variable) - or proper syntax for referring to controls outside of the 'active' userform (but an open userform nonetheless)?
Every time I have to do this particular thing with userforms, I completely forget how, and the object browser always leads me on an infinite loop of Application.vbe.activevbproject.vbcomponents.vbe.active....
PS - there may be one slight complication to the process - one of the forms, has a 2-tab page in it, each page having similar (but named differently) fields. So I may need to be able to throw in 'Activepage' or whatnot
View 5 Replies
View Related
Mar 8, 2009
Need to produce a formula to reference a large amount of data and pull one month at a time:
View 7 Replies
View Related
Mar 31, 2007
I have a table where the first column contains the date and the second column contains number of calls answered on that day. I have all the dates from Jan 1, 2007 - Dec 31, 2007 listed in the first column. I would like to be able to show the last date where data was entered at the top on my page where I have my Year-to-date running total. I would like the date to be automatically updated to the last datewhere data is entered.
View 2 Replies
View Related
Jan 17, 2014
How come doing this doesnt work?
=GETPIVOTDATA("Payment",PivotTables!$B$25,"Year",Formulas!J4)
I want to have the item at the end link to a cell reference so it can change.
how to make that work?
View 8 Replies
View Related
Jan 3, 2014
I'm attempting to list my data... I have a dynamic table that has the following columns:
number of countries
country 1
country 2
country 3
1
Egypt
3
USA
Egypt
Scotland
2
Scotland
USA
As you may have guessed, I want trends on these countries. That would be easier to do if I had a single "Country" columns but I have to work that way.
I would have wanted to work with a pivottable (because they're so "user-friendly" (not always though^^)) but I didn't find a way to do it.
The reason why I want to work with a pivottable is to be able to link my countries results to the rest of the table.. If that's not possible, I'd still want to be able to reference them and say "USA, egypt and scotland pop up 2 times"
I found the following formula (in E2):
Code:
=INDEX(Table1[country 1]:Table1[country 3];MOD(ROWS(E$2:E2)-1;ROWS(Table1[country 1]:Table1[country 3]))+1;INT((ROWS(E$2:E2)-1)/ROWS(Table1[country 1]:Table1[country 3]]))+1)
Which works but I have to manually click-drag that cell down to complete my list. I'd rather have it grow automatically if I have to work that way.
View 1 Replies
View Related
Sep 16, 2006
is it possible to reference part of a data series from a chart to a combo box?
View 3 Replies
View Related
Jun 28, 2014
I am working with two spread sheets; one is my take-off/summary sheet which is where I populate all the data for the project I am working on, the second sheet is also a summary sheet but it has also other functions; hence, I need to have two spread sheets.
I need to link/reference the data I have on my take-off sheet row 119, column K through ACL (lots of columns) to my other sheet but it now needs to be referenced in a column direction. At first, I enter = (sign) in T9 (the sheet I need to bring the data in) and then I go to the take-off sheet select N119 and hit enter, this first data is good, but then I try to drag it down to populate all the data from the other sheet but even though I lock the row (N$119) and let N free the formula doesn't populate this: =!N$119, =!O$119, ='!P$119 and so on so for...all what it gives me is =!N$119 all the way down...
View 13 Replies
View Related
Feb 23, 2014
I am writing a macro to consolidate data from different worksheets in more than one Summary Sheets.
My workbook has quite a number of worksheets, from different department e.g. OPS001, OPS002,OPS003, ADMIN001, ADMIN002, ADMIN003 and so on.
I want data from OPS001, OPS002 and OPS003 to go on one sheet e.g. "Summary-OPS" and data from ADMIN001, ADMIN002 and ADMIN003 to go on the other sheet name "Summary-Admin"
When I am working on Summary-Ops sheet I want to copy data from sheets starting with name "OPS" and so on.
View 3 Replies
View Related
Jun 15, 2012
I have a a spreadsheet that pulls user information from our corporate address book. I would then like to copy that data to another worksheet for additional data scrubbing. The problem I am facing is that, I can get the reference to work the first time but if I pull new data down, the destination spreadsheet now shows #REF!
I've tried using the below formulas but they all wind up the same way. ' Results' is the name of the spreadsheet I am making the reference to which has the dynamic data in it.
=Results!#REF!
=INDIRECT("Results!$B3")
=indirect (cell("results",B3)
View 1 Replies
View Related
Jul 22, 2014
I want to be able to paste the large amount of data I get each day into the first sheet in my workbook ("data dump" sheet), the data will be formatted in the way i have used in the example workbook.
I then would like to be able to just have to enter 2 values into columns A and B of "1st output" sheet (Label in A, and Item in B), then have it look up the data that corresponds to those 2 values that are both in the same row in the "data dump" sheet, and have it copy and paste all of the relevant data in that row into the row in the "1st output" sheet.
The data I would want excel to find and place for me in the first sheet is highlighted in yellow
As a way of keeping track of this every day, I then need the data from the "1st output" sheet, to be fed into the "2nd output" sheet. This sheet would have all of the peoples' names already in it, but would just need to look in the "1st output" sheet for references to these names. If one of the names pops up, I'd need the date shown in the row of that instance to be placed after their name. This will allow me to keep a log of how many times their name gets flagged up, as well as the dates on which it happens. Again, the data I'd need excel to look up in the "2nd output" sheet is highlighted in yellow.
If there was a way of having the 2nd sheet add new names it finds on the 1st output sheet, that aren't already referenced on the 2nd output sheet in column A, that would be amazingly handy if it could then enter that name into column A and start a new row for them
Example file :
Attachment 334122
Alternative link [URL]
View 1 Replies
View Related
Jul 22, 2014
I want to be able to paste the large amount of data I get each day into the first sheet in my workbook ("data dump" sheet), the data will be formatted in the way i have used in the example workbook.
I then would like to be able to just have to enter 2 values into columns A and B of "1st output" sheet (Label in A, and Item in B), then have it look up the data that corresponds to those 2 values that are both in the same row in the "data dump" sheet, and have it place all of the relevant data in that row into the row in the "1st output" sheet.
The data I would want excel to find and place for me in the first sheet is highlighted in yellow
As a way of keeping track of this every day, I then need the data from the "1st output" sheet, to be fed into the "2nd output" sheet. This sheet would have all of the peoples' names already in it, but would just need to look in the "1st output" sheet for references to these names. If one of the names pops up, I'd need the date shown in the row of that instance to be placed after their name. This will allow me to keep a log of how many times their name gets flagged up, as well as the dates on which it happens. Again, the data I'd need excel to look up in the "2nd output" sheet is highlighted in yellow.
If there was a way of having the 2nd sheet add new names it finds on the 1st output sheet, that aren't already referenced on the 2nd output sheet in column A, that would be amazingly handy if it could then enter that name into column A and start a new row for them
Example file : ExampleExcel.xls
Alternative link : [URL] .....
View 5 Replies
View Related
Nov 16, 2008
When I write "=A2" in e.g. cell A1, Excel returns a "0" even if cell A2 has no data. Can it be changed so that Excel doesn't return anything (=empty cell) if there's no data in cell A2?
View 6 Replies
View Related
Feb 5, 2013
In cell H1, I want to multiply the value in cell B[x] by $G$1 where x is given in cell A1. For example, if A1 is 4, I want to to multiply B4 by $G$1 and have the result in H1.
View 2 Replies
View Related
Mar 18, 2013
I manage a team that quote for business. They are targetted on number of quotes per month but then also wins per month, however a win may come a number of months after the original quote was generated.
They work off a spreadsheet where they log:
Quote date (And month)
Quote Value
... customer info etc
Win month (against orginal quote information)
Win value (against orginal quote information)
I started writing my pivot using the Quote month as the main reference point, IE Quote Month in the Row, and then put all the data in the central drop data section... However, there are two immediate problems with this:
They are targetted on quotes generated per month, some business won has come from last year, which they are lifting from the old spreadsheet and puttin in my new one... meaning that quotes they generated in Oct 2012 are now showing up as quotes for October this year.
If the wins span a number of months they are showing in January (if quoted in January) and not in March (when actually won)
Is there a better way of writing my pivot table??? There must be... ideally what I would like is half the table referencing the Quote date and half referencing the order date... but I don't know how to do this? I could probably do this with pages but I would like to show all on one sheet...
View 2 Replies
View Related
Jun 4, 2007
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"
Sub test()
Dim StartRng As Range
Dim Buffer As Range
Set StartRng = WorkSheets("Sheet1"),Cells(1,1)
StartRng.Activate
ActiveCell. CurrentRegion.Select
Buffer = rngStart.CurrentRegion.Copy
' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub
View 9 Replies
View Related
May 24, 2007
I have recently discovered the "data table" tool on Excel and have found it to be extremely useful for sensitivity analysis. The Excel help describes how one can create 1 and 2 variable data tables. Now I would like to create a data table using three variables, however. (I would like to vary three different variables and see what effect they have on one formula.) Is this possible with Excel? Can pivot tables be used this way?
View 2 Replies
View Related
Jul 11, 2014
I am working on a set of Books to be published together. I am attempting to count the number of times a Book contains a glossary word that only appears once in the whole series. Conceptually I need to check if a word in A1 == any word in Sheet2 A:A, if so then does the value in Column B == 1, if so increase count by 1; if not proceed to A2 == Sheet2 A:A, and so on.
If this were a basic program I would do as follows:
C==2 **place count
s==size(A:A) ** check for length of column A (which I know is 179)
if C<=s
match (A'C', Sheet2 A:A)
if B'C' = 1
COUNT == COUNT + 1 ** COUNT is the number of times a book contains a word that only appears in that book.
Else
[Code]...
Here is a sample set up
Screen.jpg
View 5 Replies
View Related
Jan 9, 2014
concatenating a variable range of data. Attached are examples of what I have and what the desired outcome is.
View 2 Replies
View Related
Dec 22, 2008
Is it possible to have the value in one cell determine which cells to "data validate" for a drop down list?
Example - I have three different Ports of Entry with different arrival dates.
When the user inputs the Port of Entry into a cell can I create a drop down list that shows the data related to that individual port?
View 10 Replies
View Related
Dec 18, 2012
want to get a formula where I can get the details of City if searched Brand wise and details of brand city wise. I also want option to search say five city names at once and get the brands available in all those cities. I have updated a excel with some dummy data.
View 7 Replies
View Related
