Dynamic Referencing
Dec 1, 2009
I have a worksheet titled "Systems Estimate" that will dictate values on a second worksheet called "CSI Estimate". They're the same values in each cell and column, just in a different order row wise. I would like the values in each row in the A column in the "CSI Estimate" sheet to dictate columns B through W, with the values being referenced from the "Systems Estimate" sheet. I tried using an exact match with vlookup since the data isn't sorted, however it just returns the formula I enter, not a value.
View 3 Replies
ADVERTISEMENT
Mar 16, 2014
I am trying to pull data from different Tables within a workbook by typing the name of the referenced Table in a cell rather than leaving it static withing a VLookup formula.
So far I have this:
=VLOOKUP($B6,OctTable,MATCH(D$4,$D$4:$V$4,0),0)
What would I do if I wanted to be able to dynamically change the "OctTable" portion with the name of another table which I type in call A6? I have researched as best possible and think that it might have something to do with INDEX, but I have failed to figure it out.
View 1 Replies
View Related
Feb 16, 2010
1. I have a worksheet with subtotal and total row above all columns
I currently use SUBTOTAL(9, A6:A6000) and SUM(A6:A6000)
however the no of rows is dynamic based on imported data so I want the range end (i.e. A6000) to also be dynamic i.e. refer to the last cell with data in the column.
Something like SUBTOTAL(9, A6:LastCell in A)
2. I also use SUMPRODUCT to do calculations
e.g. SUMPRODUCT (($B$6:$B$6000 = "ABC") * ($A$6:$A$6000))
again how can I make the sumproduct dynamically use all rows to the last active row.
3. I have tried just setting the second part of the range reference to a very large number but am worried that this slows down the calculation. Does it?
e.g. SUMPRODUCT(($B$6:$B$100000 = "ABC") * ($A$6:$A$100000))
View 9 Replies
View Related
Mar 2, 2014
I'm trying to reference text from one worksheet to another. The data is all in column A on the "Template" worksheet, however the row varies (ie starts at row 5, then 7 the 9 etc). The cell row is always +2 rows on the previous row, and column A.
What can I use to pull across the cells with the information that I want, onto the "Report" worksheet?
View 3 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
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
Dec 22, 2009
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
View 9 Replies
View Related
Jun 21, 2009
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
View 2 Replies
View Related
Jun 24, 2009
I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
View 5 Replies
View Related
Oct 31, 2013
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
View 1 Replies
View Related
Oct 1, 2008
I was wondering if using INDIRECT() function, or something like it in VBA, I could set a range as a reference instead of a fixed value... example below: Instead of the range being fixed on the first line,
View 4 Replies
View Related
May 30, 2007
I have a drop down menu where the user can chose between a,b,c and d. I would like to use this further in a formula. The formula is defined by me, although I dont think its relavant.
Lets say the drop down appears in B2, and I have got a formula called
Test1(B2). However this retunrs me an error. The contents of B2 is in string format.
- Test1(B2) does not work
- Test1(a) does work (assuming a is what B2 shows)
I would like to know how I can reference B2 as an argument in Formula, so it does not show error.
View 9 Replies
View Related
Oct 21, 2008
I have 2 columns of info. 1 is a complete list. The other has many of the same values, but is missing a lot of the values in column one. I am marrying the two lists to see where the data gaps are. Both lists are from different databases and I need to know which data the second DB is missing. Currently I am lining the 2 columns up and doing it manually. Is there a way to do this quickly as there is about 1000 records.
View 2 Replies
View Related
Dec 2, 2008
looking for a formula that will get me the sheet name.example: I have 3 sheets each named X, Y, Z. in cells A1 I want the name of sheet 3, which would be X
View 2 Replies
View Related
Dec 19, 2008
I need to create a worksheet whose sole purpose is to return the values from every 17th row from another worksheet in the same workbook.
I have spent a few hours trying to research the correct answer and OFFSET comes close but either I'm doing it wrong or it's not what I need.
I want to be able to drag the (every 17th row from sheet 1) formula down about 100 rows.
Assume A1 as starting place. I would prefer a formula answer as opposed to a macro or VBA.
View 13 Replies
View Related
Jan 22, 2009
I have a workbook (Workbook A) which I need to lookup ( Vlookup() )something in a different workbook (Workbook B). In workbook A I need to lookup many (I mean hundreds) of values.
My question is: What do i put in the function tab (where we put the formulas and references) to lookup values in different workbooks? And if possible, is there a way to define a workbook A to ALWAYS look up values in a defined workbook B.
View 3 Replies
View Related
Jun 24, 2009
way to reference the name of a worksheet in a cell, or even better, use a cell reference to dictate the name of a worksheet.
In other words, I would like to create a new sheet, and set it's name ='Sheet1'!C1 so that if I changed C1, the worksheet would automatically be renamed to whatever is typed in C1.
If that is not possible, I would like to have a cell be tied to the name of a given worksheet, so that if I renamed the worksheet, the cell would change accordingly...similar to the &[Tab] function used in headers/footers.
View 13 Replies
View Related
Dec 23, 2009
I have an XLS spreadsheet which updates cells through an ODBC connection to a database. The information is pulled across ok and 90% of the Vlookup and IF statements work. The problem arises on a summary page.
When i start off the summary page has cell references in numeric order to another spreadsheet and pulls back the information to 4 cells Across and 1000 Down. ='Campaign Responses - Cash Break'!A2
This is a spreadsheet that changes once the queries have run and a number between 1-1000 records are returned. However once the data has changed and the page refreshed. Not all the results have been pulled across to the summary pages. A few are there but the numeric sequence jumps from......
View 2 Replies
View Related
Oct 23, 2013
I have several options in one list, call it MASTER.
When I pick Item A from the MASTER, I want to show all available options for that Item A in another list, called OPTIONS.
I have 10 Items, and they all have a variation of 6 different options, some Items have 1 option, some have all 6.
Anyway, the worksheet I found is attached, but cannot figure out how this is working.
View 14 Replies
View Related
Oct 7, 2008
I have made 2 macros that run one after the other to perform a certan task. My issue is that I want to run Variants that I have defined in Macro1 In Macro2 but I dont know how to reference them macro 2, or if there in the same module do they recognize the varients anyway??.
I'll list the variants defined below; could someone provide me with the proper code to reference the variants form macro1 in macro2. I am also wondering if it would be easier to combine both macros under one macro and simply put a pause in the macro between both section and have a certain command to resume once the user decides to( so it would have to be a user command such as a button). If you think this would be easier could you please provide the code to pause the macro until A button is hit. both these methods woud be useful to me elsewhere as well so if it's not to much to ask could you provide both methods.
View 2 Replies
View Related
Aug 20, 2009
First of all, I'm completely new to both this forum and VBA. I have just done a programming course in java before. I hope you will forgive me if this have already been posted. No to my problem.
I am tryring to use different projected values to project other subparts of those units, which is done in the sheet mean needed weekly. Tje values are taken from projections. The answer that I get from mean needed weekly should be copied to a third sheet.
This is the code that I am using right know, but I cant get the for loop to work (or more exactly to be able to place the "i" in my text without making compilation errors).
View 9 Replies
View Related
Dec 3, 2009
Relative referencing in VBA. I have this formula in one of my modules:
View 4 Replies
View Related
Jun 26, 2006
I have a cell that mentions the year (G7) and the sheet mentions the pay period beginning (ex: January 1).
I would like to have a different cell (B9) combine the two into a date that other cells would recognize as such.
For instance, if the sheet name is January 1st and cell G7 has "2006" as a value, then I would like B9 to read "1/16" (year not shown). From there another cell (A13) would show that day/date as "Sunday, January 01, 2006".
View 11 Replies
View Related
Feb 8, 2012
Here is a formula I am trying to use to access another woorksheet to compare the values in J13 in each worksheet.
=IF(J13=INDIRECT("'"&$O$4&"'!"&"J13"),"â–¬",(IF(INDIRECT("'"&$O$4&"'!"&"J13")=" ","â–¬",(IF(J13=" ","â–¬",(IF(J13>INDIRECT("'"&$O$4&"'!"&"J13"),"â–¼","â–²")))))))
Where O4 contains the name of the first worksheet.
I want the formula reference [J13] to chage to J14, J15 and so on.
View 3 Replies
View Related
Nov 30, 2006
I'm dealing with a DDE link and have a cell that works when I type in the value =WINROS|AVEVOL!XOM . But I don't want to manually enter each value. So I have tried to use =INDIRECT("WINROS|AVEVOL!" & A2) where A2 contains the value 'XOM', but I get a #REF! error.
View 9 Replies
View Related
Dec 29, 2006
here's a simple discription of what I would like to do:
I have two worksheets open, SHEET1 and SHEET2
In SHEET1 I have numbers 1,2,3...10 running in cells A1.....A10
What I would like to do is add up a certain range of rows in
SHEET1 from target values I place in SHEET2
example:
in SHEET 2:
A1 = 3 ( I insert begining row )
A2 = 5 ( I insert last row )
A3 = equation to add up rows 3 to 5 in SHEET1, ie: A3+A4+A5
What is the equation I should put in A3? This driving me nuts!!
View 9 Replies
View Related
Apr 12, 2007
I have 8 sheets, named cpt1 to cpt8 in vba.
Now i need to loop through these 8 sheets, storing the same range of each sheet into a matrix. I have the following
dim test () as variant
For i = 1 to 8
(1) test = sheets(i).Range("b2:u21")
(2) test = cpt1.Range("b2:u21")
next i
Now (1) doesn't work, but (2) does. However i do not know how to reference the cpt1 sheet name using the for loop (ie "cpt" & i) Can anyone help me with getting this to work?
View 9 Replies
View Related
Aug 20, 2007
I am trying to create a macro that will do some basic math using J, I as referencing.
Sub Table()
Dim Temp
J = 2
I = 1
For Temp = 1 To Question
Cells(J + 35, I).Formula = (Cells(J, I + 2) + Cells(J, I + 2) * Cells(J, I + 3) - Cells(J, I + 4))
J = J + 1
Next
End Sub
how do I get the math to work without the R1C1 referencing?
View 9 Replies
View Related
Jul 20, 2008
I have this excellent code which I found on here that looks at the data in column A, looks at the data in column B & then returns the differences ie.
A_____________B_________C___________D______________E
John________Jimi_________________Not in A_______Not in B
Paul________Sporty________________Jimi____________John
George______Ginger________________Eric____________Posh
Ringo_______Scary_________________T-Bone__________Baby
Posh________Eric
Scary_______Ringo
Sporty______Paul
Ginger______George
Baby________T-Bone
Here is the code ....
View 9 Replies
View Related
Dec 22, 2009
Trying to build a form that helps assemble parts a pieces based on filling out just a small section of information. Here just a short example:
Tower Type: Type 1
Coax Length: 100 ft
Based on the above criteria, I am going to have a line item that auto fills specific parts and pieces based on the answer. The main problem I have is the coax length answer will fit within several ranges and I need a cell to be able to identify and autofill ALL of those ranges.
Specifically, if the coax length is between 0 and 100, I need to autofill the below cell with Coax 1, from 101 to 200 Coax 2, and so on and so forth. Lets say I have 5 ranges of "greater than, less than" values. how do I make a formula that will consider all of these in a VLOOKUP scenario?
If I haven't asked the right questions, or phrased them incorrectly, please let me know....I'm not the most excel experienced person...hence my visit to this forum!
View 9 Replies
View Related
