Reference Sheet Based On Cell Value?
May 12, 2014
I have several worksheets and I want to summarize all of their data on the main page. The second worksheet is called Grady, the third worksheet is called Stella and the forth worksheet is called Westminster.
On the Main Page cell B1 is, "Grady", C1 is, "Stella" and D1 is, "Westminster". On B5 I need the value of cell C5 on Grady but I don't want to have to type it in as this formula will have to be carried over for several columns.
Currently I have:
Code:
=Grady!C5
In cell B5 on the main page but I want:
Code:
=B1!C5
But this only gives me #REF!
Can this be done without using VBA?
View 5 Replies
ADVERTISEMENT
Oct 29, 2013
In Sheet2 I have a list of cell addresses showing values such as D5, D32, D59, D221, D869 stored in cells AB7:AB16. In Sheet1 the "D" column holds dates. I want to return the corresponding date for each D# cell into Sheet2 in column AC7:AC16. I'm unaware of the proper syntax for this. I though it would look something like:
='Sheet1'!('Sheet2'!(AB7))
however that doesn't work.
View 2 Replies
View Related
Jan 8, 2012
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
View 4 Replies
View Related
Jul 25, 2013
Sheet1 is a report that expresses 90 days of information and Sheet2 has three columns of 365 day information. Sheet1 has a title, a today function for the date (cell B2), two columns (A5:A94 and B5:B94), and then some other info not relevant to the problem. Sheet2 has three columns (A1:A365, B1:B365, and C1:C365) with the date in column A starting at the first of the year and then two lists of information in columns B and C. I am wondering if there is any way excel would be able to recognize the Date (B2) on Sheet1 and be able to pull the information from Sheet2 starting on that date for the next 90 days.
For example:
My report always starts on the day I am accessing it. So... Say it is 1/15/13. My today function in Sheet1 (B2) would express 1/15/13. This date would be equal to A15 on Sheet2. So I would want A5 & B5 on Sheet1 to return B15 & C15 from Sheet2 respectively.
A5 Sheet1 = B15 Sheet2 & B5 Sheet1 = C15 Sheet2
A6 Sheet1 = B16 Sheet2 & B6 Sheet1 = C16 Sheet2
...
A93 Sheet1 = B103 Sheet2 & B93 Sheet1 = C103 Sheet2
A94 Sheet1 = B104 Sheet2 & B94 Sheet1 = C104 Sheet2
With that being said, the next time I open up my report, say a week from the 15th or 1/22/13 (expressed by my today function in B2), I would want A5:A94 & B5:B94 to reference the new set of information:
A5 Sheet1 = B22 Sheet2 & B5 Sheet1 = C22 Sheet2
A6 Sheet1 = B23 Sheet2 & B6 Sheet1 = C23 Sheet2
...
A93 Sheet1 = B110 Sheet2 & B93 Sheet1 = C110 Sheet2
A94 Sheet1 = B111 Sheet2 & B94 Sheet1 = C111 Sheet2
Is this at all possible?
View 8 Replies
View Related
Nov 1, 2009
If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?
As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:
View 2 Replies
View Related
Nov 7, 2012
Basically, I need to copy and paste upon certain condition (references) and paste related-datas in another sheet corresponding with these references. Every references are organized by lines with indicators on columns.
In attached file, see:
- worksheet ANALYSIS:
* datas in E2, G2, D18, D19, D20, D21, D22 that needs to be copy based on reference in C2,
* then need to find this reference in worksheet called GROUP SAVE,
* and based on this reference, paste above cells value in corresponding "Indicators" column number
THEN, I will have to do pretty much the same with subgroup data. But I can figure out for this second step that request same manipulation.
View 3 Replies
View Related
May 22, 2012
How can this be done?
E.g. say I want to create a list of cells each referencing to the first, second, third sheet, and so on.
Say, on one sheet cell A1 references to the second sheet's A1, cell A2 references to the third sheet's A1, and so on.
Ideally I'd love to be able to write something like
=Worksheet(1)!A1
=Worksheet(2)!A1
=Worksheet(3)!A1
and so on.
View 1 Replies
View Related
Jun 4, 2007
Is it possible to have a sheetname reference in a cell?
A1 = "Exhibit A"
and have a formula as such?
=IF($A4"",(IF(ISNUMBER(MATCH($D$2,' Exhibit A'!38:38,0)),"x","")),"")
where the sheetname in bold is pulled in by a reference to cell A1?
View 9 Replies
View Related
May 7, 2008
I have an IF statment that looks at a sheet but I would like it to look at certain sheets I.E. sheet 2,4,5,6.
Sub SelectPrintArea2()
For Each ws In Worksheets
If Range("Sheet4Q5").Value > 0 Then
Range("A1:AA47").Select
ws.PageSetup.PrintArea = "$A$1:$AA$47"
ElseIf Range("C5").Value > 0 Then
Range("A1:M47").Select
ws.PageSetup.PrintArea = "$A$1:$M$47"
End If
Next ws
End Sub
But it will only look at the sheet it is in and not any of the other sheets. I've tried to tell it which sheets but I just get errors.
View 9 Replies
View Related
Nov 18, 2008
can i take reference from another sheet to formatt the cell? When i tried to format cell taking reference from another sheet, it says that you can not do that. Is it possible or not?
View 14 Replies
View Related
Jan 13, 2009
I'd like to be able to refer to a cell or local scope defined name within my sheet that references another sheet, or accomplish the same functionality in some way.
For example, if I have a sheet MyInformation and then another sheet MyPrintedInformation, where the printed information sheet shows MyInformation in a more printer-friendly format, I might have this in MyPrintedInformation:
a cell or defined name that says (this doesn't work, but just as an example):
let's call the named variable TheSheetToPrint
='MyInformation'. Then from within MyPrintedInformation all of my references look like this
Some cell :
=TheSheetToPrint!A2
View 2 Replies
View Related
Oct 28, 2009
I have a problem how to do the next:
A1 cell contains title of some test
The next cells on bottom line (B2:B10) contains the results of the this test.
On another Sheet i want to analyze those results, thus I need the results from Sheet1 be copeid to sheet2. I want just to indicate the test name (A1 location) and the rest of the cells will be copeid automatically(relative reference, relatively to A1).
View 14 Replies
View Related
Jan 10, 2010
I have a formula that pulls data from another sheet using standard reference of sheetname! I want to build this forumla to use several sheets, and would like to pull the sheet names from other cells.
For example... instead of LEMONS!a1 and LIMES!B2
I would like to use REF1!a1 and REF2!B2
where REF1 and REF2 are pulled from cell Z1 which contains test 'Lemons' and cell Z2 which contains text 'Limes'
How can I do this?
View 9 Replies
View Related
Jan 13, 2011
We have a sizeable Excel workbook that contains many worksheets for various things, everything works smoothly except for one minor niggle.
I am looking for a formula that references to a cell on a previous worksheet, the worksheet could be named anything (eg A1, B, 2, 3.4, etc). Then to add 1, ideally the first worksheet inserted must equal, say, 0 BUT can reference the sheet previous as this is a constant; so something along the lines of...
Cell A1 =magic formula
Cell A2 =A1+1
This is to get a vlookup to work consistently.
OR another option is, for the worksheet to reference an already populated worksheet and read down a table by adding one to reference the cells. Eg first sheet reads ='schedule'!A13, the next sheet added would read A14 then A15 etc...
OR a worksheet (like page numbers) count into a cell onto each worksheet inserted, eg on worksheet 6 in cell A1 the number 6 would be inserted then worksheet 7 would have 7 inserted into A1 etc..
View 14 Replies
View Related
Aug 19, 2009
Is it possible for the VBA code to capture the open workbook name and sheet name from a cell?
I've been trying to tweak this ...
View 6 Replies
View Related
Nov 2, 2009
How do you define the syntax to copy a cell reference from one worksheet to a different cell reference in another worksheet.
This did not work for me:
Worksheets("Sheet1").Activate
Range("A1").Copy Worksheets("Sheet2").Range("C5")
View 10 Replies
View Related
Mar 5, 2008
Im currently located in Sheet1. I want to reference a cell in Sheet2.Range("B2") using a R1C1 reference.
View 9 Replies
View Related
Jan 21, 2008
After having searched for a while I can't seem to find any formula that will look at a cell for reference as to which sheet to go to and return a certain cell's contents.
For example,
in column A, I have the text "Sheet1" and in column B, I need the formula to return the contents of cell B15 from Sheet1, based upon column A.
View 7 Replies
View Related
Mar 6, 2008
I have Excel 2007, here's what i want to do. I've got a workbook created with several worksheets in it. I want to input the name of a project in say, cell A1 on sheet1. Then on sheet2 cell C4, I want the text I input into the afore mentioned cell to show. I know how do do this with numbers (using the autosum button), but I can't figure out how to do it with text, though it's probably very simple & I'll be embarassed when I get an answer. Following is EXACTLY what I'm trying to do if the above example is confusing.
I have a Project Cost sheet and on it I input (text) a description of a building to be constructed. The cells next to it will be the associated costs of that particular building. There will be several buildings listed on the sheet. On a Sales Figures Sheet in the same workbook, I want to list those same buildings and then their corresponding sales prices. I want these descriptions to be automatically pulled from the costs sheet, so that I only have to input the sales figures.
View 2 Replies
View Related
Aug 13, 2008
Im trying to copy a formula to a cell via VBA.
Range("A3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!A3"
Ive tried all sorts of ', ", &, combinations, I cannot find it.
View 4 Replies
View Related
Jul 3, 2014
I am trying to create a formula that references information on two different sheets, but i want it to show me the values that are newly created when a new row is inserted. The two current sheets are Master and Data.
I have a formula on Master that reads ='Data'!$K$3 to reference the value on Sheet data in cell K3. When a new row is inserted showing me the current readings, I want the Master sheet to show me the NEW value in cell K3 on the data sheet. The problem is, the insert of a row moves the formula on the Master sheet to now be ='Data'!$K$4. How can i STOP the insertion of a row from changing the formula so i continue to see the value i need?
View 3 Replies
View Related
Jun 9, 2009
I have this formula in excel 2007:
=COUNTIFS(Blad1!W:W;"1";Blad1!X:X;"H")+COUNTIFS(Blad1!W:W;"6";Blad1!X:X;"H").
Blad is Dutch for sheet, by the way.
In this formula, I want to let the ranges in Sheet1 be dependent of values in Sheet2. The formula itself is in Sheet2.
W must be replaced by the value of Sheet2!B12 and X to be replaced by the value of Sheet2!B9.
I tried this by using the INDIRECT formula, but the quotation marks of the search values are giving errors.
View 6 Replies
View Related
Dec 12, 2012
In workbook 1, I have summary sheet with columns
A -> SNO
B -> Customer Name
C -> Product Name
D -> MODEL
E -> Quotation Rate
I have to retrieve MODEL & Quotation Rate .
and also having seperate sheet for each customers say X1 , Y1 , Z1 ( so other sheet names as X1, Y1 & Z1)
In X1 sheet , I do have following columns
A-> Product Name ,
b-> Model (as of now limited to 1 per product name)
c -> Rate
Now my request is as follows Based on the Column b value in Summary sheet - i have to goto respective sheet and do vlookup for the respective product name and retrieve model and fill it in Column D. I heard i can use INDIRECT function and Vlookup in this junction .
View 5 Replies
View Related
Apr 5, 2006
I have a worksheet(Shed) and the range where numbers will be entered are B3:E46. In another sheet(Location) I have a in column A "Shift #", B "Shift Bus #" C "Location". what I am attempting to do is when a bus number is entered into Shed! ie.. b3=900, d15=350 etc.... Location! would lookup ie.900 in the Shed! and match it to Location! (column B) and from there provide the cell ref in column C. So I could then print Location! that would give me in Shift # order where each Bus # the shift is assigned to.
View 13 Replies
View Related
Oct 7, 2013
Within one workbook I have 15 sheets, 13 are for separate divisions within the company, 2 are used to present sums across the 13 sheets. All 13 sheets have identical columns and rows, with unique numbers in each cell. Right now I am manually selecting the cells to reference in my master sheet, =sheet name!cell+sheetname!cell+sheetname!cell - so and and so on. How can I reference the sheet name once, and then all of the cells to SUM? =sheetname!(c4+c14+c24+c34) etc?
This would let me be able to copy the formula from cell to cell, and only have to change the sheet name each time.
View 3 Replies
View Related
Jan 13, 2008
I'm in the leadup to my A2 ICT controlled conditions piece where i have to make an Excel system. My chosen subject for this is to use the school house points system. I'm going to make a system where the results of house matches are inputted and points are awarded accordingly.
I just have a couple of queries after looking at my initial designs.
1. Is it possible to get excel to look through a sheet, find a text reference, then bring up something in a cell located in the same row? For example, for it to locate one of the House team names, then go along the row and find the ammount of house points they have in order to make a total for them. Is this also possible over a number of sheets? (In order to keep a running total on a separate sheet).
E.G. House, Games Played, Wins, Losses, Draws, House Points
2. For the house cross country event they use a different system which differs from everything i have designed. What i basically need to do is be able to use a drop down against each finishing position where you can select one of the four house teams, then it takes the points won for that finishing position and puts them into a table.
View 12 Replies
View Related
Jun 10, 2008
I have a UDF in Cell Sheet1!A1 that inputs some ranges from Sheet1 and calculates a number. Then I have a sub that calculates the value of this function with Evaluate(Sheet1!A1.formula). However, this only works when I am in Sheet1. Otherwise, I get the wrong calculation. How can I avoid this? I will post the code in the open source forum as it otherwise is quite good and automatically resizes array functions to their proper size.
View 2 Replies
View Related
Jun 23, 2009
HTML Imagine the following table (Ax, etc is the cell position):
A1=John C1=Name D1=Date E1=Version F1=IP
A2=10/2/2008 C2=A1 D2=A2 .... ....
A3=Client version 7.01 C3=A1+5pos D3=A2+5pos
A4=172.24.3.5 C4=A1+10pos D4=A2+10pos
A6=Michael
A7=10/2/2009
A8=Client version 7.03
A9=172.24.3.80
A11=Susan
A12=2/2/2007
A13=Client version 7.02
A14=172.24.3.200
I have a table of data on the left side and I want to re-organize just like the right side. Let's take C2 as an example, the value for that cell = A1;
Now, C3 in the other hand, the value for that cell = A1 + 5 positions down;
C4 = A1 + 10 positions down;. The same type of formula applies for the remaining columns on the right. This seems like an easy solution, but it's not easy when you don't know it... :
View 4 Replies
View Related
Mar 6, 2014
I want to save my file as a pdf based on cell reference A4.
This is what i have
[Code] ....
View 2 Replies
View Related
Mar 21, 2014
The first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.
Example 1:
If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’
Example 2:
If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’
Example 3:
If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’
View 9 Replies
View Related
