Hello Everyone,
I have 8 different excel tab that each of them containing 2 NL fuctions.
I would like to get that 8 tabs information into one tab.
I have tried using " ='sheet1'!C4 " etc ,but this did not really worked.
Is there any way to do this please?
Thank you.
Hello
Hello Hazal -
I've put together a rudimentary example for what you describe.
First, I have three identical sheets. Each sheet has two NL(Rows) functions, some value for on each line, and a count of how many total lines are on the sheet:
It's important that my three worksheets have names that only differ by the number (this makes the Combined sheet much easier to design).
The real magic is on the Combined sheet.
First, I have to know how many sheets I have (in my example, this is always three). This info is in cell C2.
Next, I use an NL(Rows) function (in cell C3) to create a replicated area for as many sheets as I have. Column C will always contain the number corresponding to the sheet number. 1 for Sheet1, 2 for Sheet2, etc. I'm hiding row 3 because I don't need to see that NL(Rows) function when I run the report.
Within that replicated area, I have another NL(Rows) functions to create the same number of rows as are on the sheet that corresponds to that area. In other words: Area number 1 will have the same number of rows as Sheet1, area #2 will have the same number of rows as Sheet2, etc.
Then, in column E, I go to the corresponding sheet and get the value that is in column D from that sheet.
So, if I run the report, here are my numbered sheets:
As you can see, each NL(Rows) function replicated, there are values associated with each row, and I have count of how many rows are on each sheet.
Now let's look at the Combined sheet:
You can see the sheet number in column C and the values from each sheet in column E.
This demonstrates how to use Excel's abilities (especially the INDIRECT function) to get information from other sheets in a workbook.
You can adapt this to fit your specific needs. I hope that helps.