In this case, you can use the INDIRECT function, which is available in both Excel and Google Spreadsheets. I’ll show you how to use it by running through a quick example.
INDIRECT:Returns the reference specified by a text string. References are immediately evaluated to display their contents. Using Indirect to dynamically refer to a worksheet In the following example we want to look at the revenues of a specific product by dynamically referring to the data sheet of each product. We have a sheet Revenues in which we want to see the revenue for a specific product. Next, we have two sheets with sales and revenue data for two product: Product1 and Product2.
Normally you can only reference to one worksheet We want sheet Revenues to show the revenues of one specific product and to be able to update the numbers dynamically when we change cell D1. Unfortunately we can’t use a reference to D1 to specify the sheet we want in our VLOOKUP function like this: We can’t use a variable reference to D1 in our VLOOKUP For this to work, we have to use the INDIRECT function. It allows us to use the value of cell D1 for creating a dynamic VLOOKUP referring to ranges on multiple sheets. Using sheet names as variables with Indirect Now you can change cell D1 to “Product2” and the revenue numbers will dynamically update and get the numbers from the second worksheet. Indirect in Excel So to recap, you can use INDIRECT to refer to multiple worksheets variably like this: =INDIRECT('&D1&'!'
&'A3:D6') Indirect in Google Spreadsheets You can use INDIRECT in Google Spreadsheets in a similar way: =INDIRECT(CONCATENATE(D1,'!A3:D6') As always, let me know in the comments whether this approach worked or if you have any questions! Great example, thanks. I want to populate a large spreadsheet with this indirect formula, looking up on multiple sheets at once.
I have two questions: Is there a way to write the formula so it sums across the product sheets, without just doing + INDIRECT(“‘”&E1&”‘!”&”A3:D6”) – if E1 was going to contain the word Product2? This is because the formula will get really long if I use it across 20 sheets. Also, as I want to use the formula across an entire sheet, is there a way of doing this so that the cells A3:D6 are not locked as text, because they won’t change automatically when I drag it and the process will be very laborious. Thanks in advance!
Learn the basics of creating charts: (1) select the correct data, (2) use CMD to select non-contiguous data, (3) select the correct column headings and row headings, (4) choose the type of chart you want to create. Indeed, when creating charts in Excel, the source data does not always reside on the same sheet. In general, the customization options for Excel charts based on multiple sheets are the same as for To delete a certain data series from the chart permanently, select that series and click the Remove.
![How to create a chart in excel for mac 2011 using only certain rows of data data How to create a chart in excel for mac 2011 using only certain rows of data data](/uploads/1/2/5/4/125481685/125326809.jpg)
I was having the same problem as Chloe’s second question. I was trying to apply this formula across 1000 cells and dragging did not work. I just figured it out. Here’s my formula: =INDIRECT(“‘”&$D$1&”‘!”&M1) In cell M1 text says D1. Then I dragged that formula in cell M1 down to read D1, D2, D3 and so on.
Then when you drag the INDIRECT formula down it references M1, M2, M3, and so on. For yours your formula needs to read A3:D6, A4:D7, A5:D8 and so on. Excel will not recognize the pattern A3:D6, A4:D7, A5:D8. So simply create another column next to M1=A3 reading N1=D6., then adjust your formula to read. =INDIRECT(“‘”&$D$1&”‘!”&M1&”:”&N1). Hey, buddy – great stuff.
However, I came here for what is probably a simple question, but I can’t tell if you answered it yet: I have a spreadsheet to generate configurations based on named cells on another page. I have it so that the input are rows but the only row referenced is the top one, with the named cells (referenced in another tab, as my configuration). So in that first row, my first cell you enter the # of the row you want to populate your named cells with. It works well except, if I insert a column, all of my INDIRECT formulae keep referencing the column letter that they WERE, rather than dynamically remaining referencing the apppropriate cell below them.
How can I accomplish this? I have many columns, and it gets tiresome checking each formula when I insert a new column. I was having the same problem as Chloe’s second question. I was trying to apply this formula across 1000 cells and dragging did not work. I just figured it out.
Here’s my formula: =INDIRECT(“‘”&$D$1&”‘!”&M5)) In my cell M5 text says D5. Then I dragged that formula in cell M5 down to read D5, D6, D7 and so on. Then when you drag the INDIRECT formula down it references M5, M6, M7, and so on. For your formula needs to read A3:D6, A4:D7, A5:D8 and so on.
Excel will not recognize the pattern A3:D6, A4:D7, A5:D8 if you drag the formula. Simply create another column using N5, and put text saying D6, D7, D8 and so on. Adjust your formula to this: =INDIRECT(“‘”&$D$1&”‘!”&M5&”:”&N5)). Hello – very interesting stuff – I have similar issue, trying to use LOOKUP instead of VLOOKUP to reference data in other worksheets Example of Code I am trying: =LOOKUP(H10,INDIRECT(“‘”&K10&”‘!”&”$B$2:$B:$5000”),INDIRECT(“‘”&K10&”‘!”&”$D$2:$D$5000”)) Where H10 contains the value that I wish to lookup in Column B in spreadsheet name located in cell K10 and return value that aligns with matched row in Column D in spreadsheet name located in cell K10. This does not appear to be working – resulting in #REF error.
![How to create a chart in excel for mac 2011 using only certain rows of database How to create a chart in excel for mac 2011 using only certain rows of database](/uploads/1/2/5/4/125481685/437702138.gif)
Any help – greatly appreciated. Thanks, Steve. I am going round the bend trying to use INDIRECT in Google sheets. I have a stream of data in column D. The data is in banks of 8. I wish to transpose each bank into a row format.
That works OK but I want to automate and wish to use INDIRECT so that I can use a formula to transpose each successive bank. I have tried various combinations but here is a minimum trial =TRANSPOSE( INDIRECT(“E381”):D417) where E381 contains D409. Ie I wish to transpose D409 to D417 into a row. When I get indirect to work I will try to figure out how to “automate” the process. Any help will be much appreciated. On Google Sheets, how do you do conditional formatting BETWEEN sheets? For example, on Sheet1 Column A, I have a master list of guest names that were sent a party invitation.
On Sheet2 Column A, there is a list of guest names of THOSE WHO RESPONDED (this column gets filled automatically from Google Forms). If a name in Sheet2 Column A MATCHES the name in Sheet1 Column A, I want that name to be highlighted in Sheet1 Column A. For example: John Smith is on our guest list (Sheet1) and he has submitted a response (Sheet2), so I want his name to be highlighted Jane Smith is on our guest list (Sheet1) but she has NOT submitted a response (Sheet2), so her name should remain unhighlighted. Please let me know if there is a workaround on Google Sheets. I know this is an old question but here’s how I would approach that. Someone may have a better way but this should work. On Sheet1 (Invitation list), create a column in front of the names which will serve as an indicator of whether or not the name appears on sheet2 (RSVP list).
In that column your formula will look like =IF(ISERROR(MATCH(cell with name, list of names on Sheet2, 0)), “”, X). This will place an X in the box for anyone whose name appears on Sheet2. Then you simply use a conditional format to highlight the names with an X next to them. I’m sure you can put a similar formula directly into the conditional formatting but it takes so much playing around with quotes and “=” signs that I find it’s just easier to add the check column. Hope this helps. Has anyone figured out a way around the INDIRECT function returning #REF if the other file isn’t open? I have a Master file that contains a set of standard variables that I want to use across a large number of files so that if I want to change the variables in all I only have to change the variables in one (the master file) and it will propagate through.
At the moment I am using =IF(‘Master Sheet 2018.xlsxData’!A17=””,””,'Master Sheet 2018.xlsxData’!A17), however, I would like to change it so that it calculates the filename using the financial year for the file which I have already got a function for. I have tried this =INDIRECT(ADDRESS(1,1,1,1,”Master Sheet “&’Variables (Hidden)’!$B$2&”.xlsxData”)) which works as long as the Master Sheet file is open but otherwise returns #REF.