Referencing Cells In Other Sheets Using INDIRECT
Aug 6, 2014
Applying my indirect formula to the rest of the cells in my sheet properly.
I am getting the values of cells in other sheets, using INDIRECT to use cell values to name the sheets. Here is an example of how the cells are now...
A
B
1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))
[Code] ....
The formulas in B1 are going to return the value of A5 in "Sheet 1" (or whatever value is in A1) and B2 will return the vale cell A5 in 'Sheet 2. The next line will be the value of A6 in Sheet 1, and the next will be A6 in Sheet 2 and so on.
So I have quite a few rows to get through I want to be able to autofill so it looks like this...
A
B
1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))
2
Sheet 2
=(INDIRECT("'"&A2&"'!$A$5"))
[Code] ......
As it is now, when I autofill, it autofills the Sheet name part of it. It is looking like this...
A
B
1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))
[Code] ......
Excel isn't intuitive enough to know I want to move cells in the referenced sheet.
View 5 Replies
ADVERTISEMENT
Apr 17, 2008
I have a sheet that needs to pull data from diferent cells and sheets at the same time. If I have to have all the data on one sheet I can change that as this sheet isn't in production yet.
On Sheet1 cell D6 needs to be able to get data from Sheet2 Cell C2 or Cell N2 or Cell Y2.
On Sheet1 cell F6 needs to be able to get data from Sheet2 Cell C3 or Cell N3 or Cell Y3.
While doing the above it this also needs to be accomplished.
On Sheet1 cell D9 needs to be able to get data from Sheet3 Cell C2 or Cell N2 or Cell Y2.
On Sheet1 cell F9 needs to be able to get data from Sheet3 Cell C3 or Cell N3 or Cell Y3.
Is Indirect able to do this or do I need to use something else? I am trying to do this without macros if possible.
View 9 Replies
View Related
Oct 14, 2007
I currently have 3 sheets 1, 2,and 3.
Data is input onto sheet 1, this is copied into sheets 2, no real problem there. However i need to enter data onto sheet 3 and if that data appears on sheet 2 i want the line, not just the cell to be coloured on sheet 2. The data on sheet 3 will not be in any specific order in the column
I have tried conditional formatting but keep getting a message to say it cant be done and offering a formula which i dont understand.
View 9 Replies
View Related
Jul 24, 2009
I am trying to find a way to create new sheets in a workbook that when created will reference a new row on a data sheet in that workbook. Each workbook could end up containing varying numbers of sheets (anywhere from 2 to 150+) based on size limitations. There are some lookups and referenced data on each sheet that would be copied as is and not reference the data sheet. I have not included these in my example.
I'm trying to avoid having to change each cell's reference manually for all of these sheets (which is one of the solutions I've come up with so far). My current method of creating these workbooks does not involve the data sheet and each workbook starts off with data only on sheet1. I select and copy that sheet into sheet2 and every subsequent sheet. I want to incorporate the data sheet so it can be filled out by someone else and copied into that tab to populate all of the data sheets.
View 13 Replies
View Related
Feb 7, 2012
I'm building a table that references a dynamic range on a pivot table. So far I've gotten the function to return the address on the pivot table but it wont return the value. I'm doing this so that I can both refresh the two source data sets and summarize (pivot table) and also graph these two summarized data sets since you can't graph two different pivot tables together.
This is my code so far:
Iferror - To return zero if the source data doesn't exist yet
Address - To concatenate the match functions
Match - to find the row and column numbers on sheet "B" that match the x,y call on the original sheet "A"
=IFERROR((ADDRESS((MATCH($A2,'Install Data Pivot'!$A:$A,0)),MATCH(B$1,'Install Data Pivot'!$4:$4,0),1,1)),0)
Returns $B$5 since this is the cell where the information lies.
I've tried just adding the sheet name like this...
=IFERROR('Install Data Pivot'!(ADDRESS((MATCH($A2,'Install Data Pivot'!$A:$A,0)),MATCH(B$1,'Install Data Pivot'!$4:$4,0),1,1)),0)
But that doesn't work. I've also tried using different variations of Indirect and Index but I still can't get it to reference the other sheet.
View 1 Replies
View Related
Oct 4, 2005
I have tried using PULL from Harlan Grove's posts to workaround this but am coming up with #VALUE errors.
Here's what I have:
Column B contains the acct # being referenced e.g. 5230
Column E="_"&Br where r is the row #
Column F=MATCH($B$3,INDIRECT(Er&"Rows")) where _acct#Rows is a named
range referring to an external workbook e.g. _5230Rows
Columns G-R=INDEX(INDIRECT($Er),$Fr,COLUMN(G$6)) where_acct# is a named
range referencing an external workbook e.g. _5230
I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R however it returns #REF!
I need a solution to replace INDIRECT so I do not have to have both workbooks open together. The named ranges are static, but reference external workbooks.
View 13 Replies
View Related
Jun 27, 2007
I wonder if there is any easy way of findinig (numerical) duplicate entries in a column? Some cells are empty, in case this might cause a problem. I do not wish to delete duplicate rows automatically, just to find them. Why not just sort it? Because indirect referenceing is used where each row corresponds to a separate spreadsheet in the workbook. What I need is to find the duplicate so that I manually can erase one of the spreadsheets for the particular case and adjusting a reference list.
View 3 Replies
View Related
Nov 16, 2007
I have 40 sheets with info and 2 summary sheets. One summary sheet will summarise the data on sheets 1 to 20 and the other 21 to 40.
Im using the following:
=INDIRECT(ROW()-9&"!X10")
This works fine for my 1st summary sheet enabling me to display the vlaue of X10 in sheets 1 to 20 in D10:D29.
However in the 2nd summary sheet I wish to display X10 in D10:D29 but only using sheets 21 to 40.
Is there a way to eliminate sheets 1 to 20 and just use 21 to 40.
View 9 Replies
View Related
Aug 14, 2008
I have a sheet with tabs Jan - Dec
My goal is for a user to specify a 3 letter Month in Cell A1 (i.e. Jun) and for my sheet to calculate all cells C5 from Jan - Jun.
I have tried using the following formula =SUM(INDIRECT("Jan:"&A1&"!C5")), but the indirect function does not seem to span across workbooks!
View 9 Replies
View Related
Oct 9, 2006
i encountered a problem with using the Indirect formula. it gives #REF error when i use it to refer to a sheet with brackets in them for example i want to refer to sheet "Data 101(1)" =INDIRECT(A1&"!A1"). I'm not allowed to change the sheetnames. is there a way around this using formula or vba?
View 6 Replies
View Related
Jan 19, 2009
My sheets are all categorized by date, and I want to be sum individual cells on each worksheet on one final summary worksheet. For example, if cell A1 on each sheet was units sold and I wanted to see how many total units were sold between Jan-04-08 and Jan-16-08 my formula on the summary sheet would be:
=SUM('Jan-04-08:Jan-16-08'!A1)
But I want to be able to easily modify what dates my summary sheet shows so I tried using the function:
=SUM(INDIRECT("'"&N14&":"&N15&"'!A1"))
where N14 was the starting sheet and N15 was the ending sheet. It keeps giving me #REF! however and I can't figure out why and don't know if this is something I can fix.
View 9 Replies
View Related
Nov 9, 2008
I am trying to do a time analysis by placing an X in a square based on the hours an employee worked during a day. Time is in 24 hour clock time in 30 minute increments.
What I need to do is to populate the correct squares with an X based on the start and end times an employee worked each day. I have 20 different locations and will need to use the indirect function to point to the correct sheet and cell. For example I have store #5 and the time sheet for store number 5 is on the sheet and the information I need to return is in cells d4 and d5. Start time was 5.52 and ending time was 10.97.
Based on this information I would like the closest start and ending time on the spreadsheet below to show an X.
View 9 Replies
View Related
Mar 21, 2014
There are 4 sheets in workbook.
Sheet name is 3-12, 3-13, 3-14, 3-17.
that 3-15, 3-16 is not because of holidays. But, I want to display in 3-17 sheet sum of A1 cell from 3-12 sheet to 3-16 sheet.
nearly, =SUM('3-12:3-16'!A1)
=SUMPRODUCT(N(INDIRECT("'3-"&ROW(INDIRECT("12:"&RIGHT(A1,2)-3)) &"'!A6")))
is well work.. but
=SUMPRODUCT(N(INDIRECT("'3-"&ROW(INDIRECT("12:"&RIGHT(A1,2)-1)) &"'!A6")))
do not work..
I want to sum dynamic skip error value. namely, calculating to yesterday(3-16) from first day(3-12)...
View 11 Replies
View Related
Jun 4, 2014
I am currently referencing data from 20 different sheets (11 columns each, varying amount of rows) all on one sheet. I intend to edit random data on each individual sheet with the added possibility that I will be adding or deleting rows....
So my question is, how can I set up this consolidated sheet so that when I delete/add a column in the referenced sheets, it will automatically add or delete these (additional) rows?
I have it set to the range of rows that I currently have, but all together I have about 1500 rows and I do not want to go through all of these rows deleting anything blank or adding in more. This consolidated data also is also filtered so finding out where to add to any ranges would be difficult unless I wanted to "un-filter" them (which I do not).
View 1 Replies
View Related
Nov 16, 2006
I am having trouble with the following formula in my workbook with many sheets: ="='"&MONTH(A6)&"-"&DAY(A6)&"'!"&"$B$13". I entered this formula into B6 of the current sheet. Cells A6:A33 contains a date (02-Jan-07). So what I'm trying to do is retrieve the value in B13 of the sheet corresponding to the month and day in A6. If I hard code this formula into B6 ='1-2'!$B$13 it works but I don't want to edit each of the formulas in column B to correspond to the correct sheet. When I type in the above concatenated formula it just returns text (='1-2'!$B$13). Is there a way to do this so the formula will actually execute?
View 3 Replies
View Related
May 2, 2006
In my application, I am reading a barcode label whose first three digits are a code for the model number (stored in Sheet1, Column C). On sheet 2, this 3 digit code appears in Column C also, and the exact model number for each 3 digit code is stored in Column A. I would like to display the information from Sheet 2 Column A in Sheet 1 Column H, depending on the value of Sheet 1 Column C. (for example, Sheet 1 Column C contains the code 011. On Sheet 2, Code 011 is the code for Model # HTE14WABWW. I would like to copy HTE14WABWW to column H on every row where the row's column C equals 011.
I see several examples of sorting data across sheets but nothing that really answers my specific question.
View 5 Replies
View Related
Jun 13, 2013
I have attached a file for reference. I have another file I use that is similar to the one attached, however it has 500+ rows and is still growing. The issue I am having is that in this file I want to make a "Template" worksheet. Basically I need 50ish sheets that are more or less the same. I have a column that describes a item, a items weight, a row with headers for the columns that I want to appear identical on every worksheet. I want the variables to be the count. These will change from sheet to sheet with formulas to than update the totals.
My issue I am having is that I constantly find myself needing to insert new rows for new items. In my real worksheet I have rows grouped together in a logical way so the rows cannot just always be placed in the next available row at the end of a sheet. How I can have sheet 1 be my "template" and the others take their item lists and weights from sheet 1. In the example file I created sheet 1. I than created sheet 2 and selected a cell entered "=" and selected the cell that I want it to have the same info from on sheet one. I fill handled these items down to generate a list. I than held down control and dragged a couple of new sheets. This process works, and is what I am currently doing, but what do I do when I wish to add a new rob between row 4 & 5?
View 2 Replies
View Related
Oct 3, 2008
I have a workbook with probably 60 tabs/sheets at the bottom, each titled with a date, like 10-2-2008. In each tab/sheet, I have information for that day. What I am trying to do is on a new sheet, have all those sheets listed down column A. Next, in column B, I would like the value of cell D1 from each tab/sheet.
Is there a formula I can write that will lookup the value of D1, for each date listed in column A? I have tried doing =INDIRECT(A1&"!D1"), where A1 is a date/tab name, but it will not work. If however I rename the sheet "5", then have a "5" in cell A1, it will give me the data listed, but for some reason if I have a date with dash's, 10-2-2008 it will not work.
View 4 Replies
View Related
Jan 24, 2014
Code:
Dim flag As Boolean
Private Sub UserForm_Initialize()
Dim myWorksheet As Worksheet
flag = True
[Code] ........
I made a user form that, with the click of an item in the listbox (which is populated with the worksheet names), will show or hide the matching worksheet. I was using indices, but a problem occurs when there are some hidden sheets to begin with, because the listbox is filled with only the visible worksheets (that's part of the functionality required) and so, when there are hidden worksheets the indices of the listbox are totally different. So, to avoid that kind of bugs I thought I'd use the sheet + listbox names to show/hide the worksheets, but that is proving to be a very hard task.
How I can use the listbox item names + worksheet names to make the "mapping" correctly?
View 5 Replies
View Related
Jan 16, 2014
I'm working with several sheets, each containing databases of customers that bought different products. I'm trying to do a cross reference so I know which customers has already bought multiple products.
English is not my first language and I might not be able to clearly express what my problem is, so I included a worksheet example.
In the example, you can see that Lea bought an apartment coded with APRT-001 from the first sheet. Lea also bought a condo coded with COND-002. What I'm trying to do is to put a note beside the her entry in the apartment that she has bought COND-002. And vice versa on the COND-002 entry on the second sheet.
What I usually do is combine all the data in one sheet, use Conditional Formatting to point out the duplicate name entries, and work through the whole thing manually. The thing is, there are thousands of names and I need to update the list regularly
View 5 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
Aug 18, 2009
I am trying to develop an Indirect Indirect Validation drop down list. Example, Building - Floor - Room, i.e. Select Building from a Validation drop down list. Then based upon the Building selected, select only the Floors applicable to the Building Selected. I am able to achieve this via an Indirect Validation drop down. However, when I attempt to then select the Rooms applicable to the Floor of the Building I selected, I can not produce an Indirect Validation off a previous Indirect Validation.
In the attachment, I have used Plant - Location - Room. I have name ranged the selections, and have used Validations Lists for Plant, and Indirect Validations for Location. The error occurs where I attempt to do an Indirect Validation for Room.
View 3 Replies
View Related
Sep 27, 2006
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
View 3 Replies
View Related
Dec 18, 2012
I have 2 drop down menus based on car make and model.
The first contains Make and the second contains the models of that make using the indirect function (All through data validation (this may be my problem))
Now what I'm wanting is that when the Make changes, the Model will automatically changes to either the top (or any really) model from the list for that make or to change to blank. So can this be done without VBA?
View 4 Replies
View Related
Oct 9, 2008
=SUM(INDIRECT("'Sheet2'!$C$1:$C$5"),INDIRECT("'Sheet3'!$C$1:$C$5"))
These do not:
=SUM(INDIRECT({"'Sheet2'!$C$1:$C$5";"'Sheet3'!$C$1:$C$5"}))
=SUM(INDIRECT("'"&Sheets&"'!$C$1:$C$5"))
Where Sheets refers to =Sheet1!$A$1:$A$2
[A1=]Sheet1, [A2=]Sheet2
For the last two ONLY Sheet2 is summed, not sheet3
View 5 Replies
View Related
Mar 18, 2014
As you would normally use indirect formulas so the cell references don't change. Which that is what I want in the end, but I need to copy them to an indefinite number of cells first and would like to not do it by hand. I have found some solutions to similar questions/problems but cannot figure out how to make them work for me. So, what I am looking to do is this... (I have also attached the spreadsheet for reference)
I have gotten the information in columns A through F on the first sheet to update as rows are added, moved, deleted on the second sheet using Indirect range. Also, I could do this for Column I (Copmleted Proj. Avg. Terminations) but I would have to do it manually (as I began doing in I3, I4 & I5) but that would be time consuming. So I am hoping there is a way I can copy the formula down the cells are updated for the initial copy but then don't update if the referenced cells are moved or deleted.
View 1 Replies
View Related
Dec 15, 2008
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies
View Related
Mar 26, 2014
I have a long list of amounts one per day. Sun-Sat repeating. I Total the amounts for each week and place it next to Sun. now i have the total for that billing week.
I need to reference these numbers from a different sheet that only has one row per week. I tried to do ='sheet1'!a2 then next row down ='sheet1'!a9 and ='sheet1'!a16 etc then fill down but when i do it seems to start again and do a3, a10 and a17.
Is there a way I can get it to only use cells with figures in them? IE if cell is empty then move down one else use the amount in cell... or how do I get it to reference 7 cells below each time rather than the next immediate cell.
View 8 Replies
View Related
May 2, 2009
For example in this formula I typed in WWW as the max:
=SUMPRODUCT(($F$5:$WWW$5>=C2)*1,($F$5:$WWW$5<=C3)*1,ABS($F$6:$WWW$6))
If I try to type in a value higher than WWW it will give me an error. I was just trying to put in an amount that I would never hit within the row.
View 5 Replies
View Related
Jun 8, 2009
I would like a formula that checks for blank cells only between C1:C10 and E1:E10 and then insert the first and last names of the person associated with these blank cells (which are located in A1:10 and B1:B10) on another worksheet, which for the sake of argument can be called 'sheet2'.
View 2 Replies
View Related
