Function using worksheet name

[Alf123]

I'm using a worksheet with multiple spreadsheets. Spreadsheets named "Day1" to "Day10" and the eleventh spreadsheet is the summary sheet.
Each spreadsheet has its own date.
Calculations on the spreadsheets end with an colum ("AA10" to "AA100") with closing values.

Depending on the date on the "Summery Sheet", i need a formula to lookup the date on the spreadsheets "Day1" to "Day10" and return the values from this spreadsheet to cell "C10" to"C100".

thanks you!!

 

[vogel997]

Hi vogel997

i do not know how to create the tab you referring to, and to link this to the spreadsheet name eg. "Day1"

The Tabs are the Spreadsheets that you created.

 

[Alf123]

Re: Function using spreadsheet name

Hi Snakehips

thanks for the assistance. I followed the steps until the second last one. Where should i enter your section. Workbook defined names?

 

[Alf123]

Re: Function using spreadsheet name

Hi Snakehips

the formula in cell "C10" in the summary sheet is giving a blank cell. Must be because the last section has not been done.

 

[Snakehips]

Re: Function using spreadsheet name

Alf,

Are you planning on using Summary M2:M11 as the named range?

If so select M2:M11 then in the Name Box, up top to the left of the formula bar you will see M2
Select/Highlight that M2 and type 'TenDates' and hit return.
If you have done it correctly then if you click the down arrow on rhs of name box you will see TenDates in the drop down list.

 

[Alf123]

Re: Function using spreadsheet name

Thanks you Snakehips, it worked!!!

 

[Alf123]

Re: Function using spreadsheet name

Thanks you Snakehips, it worked!!!

Is it possible for you to help me read the formula in layman's terms?

 

[Snakehips]

Re: Function using spreadsheet name

Is it possible for you to help me read the formula in layman's terms?

Alf, glad it works. Here is an indication of how.

Firstly, let me say that we could have done it 'simple but ugly' just by using 10 nested IF's in the Summary sheet formulas. eg....

=IF(E6=Day1!B4,Day1!AA10,If(E6=Day2!B4,Day2!AA10,If........ 8 more...) !!!!!!

In looking for a less ugly solution we need a way to relate the date in Summary E6 to the sheet that has the date match in B4.
Given that your sheet names end in numbers 1 thro' 10 we can exploit that.

The named range effectively lists the B4 date for each of the Day sheets. Again we could have put 10 discreet formulas in that range.....
in M2 =Day1!B4 in M3 =Day2!B4 etc.

I used =INDIRECT("Day"&ROWS($A$1:$A1)&"!B4") just because it is a single formula that when dragged down will give us the required result.

I suggest you do a YouTube or Google search if you want a better understanding of the INDIRECT function but basically it allows you to 'construct' a string that resolves to a valid worksheet reference. If I break down the above then we have "Days" .
Then ROWS($A$1:$A1) where the $ sign makes absolute or fixes a column or a row. So ROWS($A$!:$A1) = 1 When dragged down to row 2 it changes to ROWS($A$1:$A2) = 2. So that generates the day number. Tack '!B$" on the end and we have strings "Days1!B4", "Days21B4" etc. With those 'constructed' strings inside the INDIRECT function they are treated as a reference.

=INDIRECT("Days1!B4") is like =Days1!B4

So now to the main formula which again uses the INDIRECT function.

I chose to name the range TenDates for clarity as much as anything and also just in case you might not have wanted it in the Summary sheet. You could forget the named range and refer to that range by it's reference if you wished.

=IFERROR(INDIRECT("'Day"&MATCH($E$6,TenDates,0)&"'!AA"&ROW()),"")

The IFERROR wrapper should hopefully not need explanation. If it does then Google.(As with MATCH function below)

Then we have the INDIRECT("xxx") which is saying, give me the value of the sheet / cell reference that equates to the string "xxx"

Again we have "Day"

Then MATCH(E6,TenDates,0) looks to see if it can find the Summary E6 date in the list of dates ,'TenDates' and it returns a number that is it's position in that list. If it matches with the second date down in the list it returns 2 which is the date from B4 of the Day2 sheet!!

&"!AA" adds the necessary "!" that denotes a sheet name reference and "AA" for the column reference.

&ROW() adds the row number that the formula is in. As the data in all sheets, Summary and Days is like for like row-wise, that gives us a good row number.

So our Indirect reference string builds up to be.... e.g. "Day2!AA10"

Inside INDIRECT that returns the value of sheet Day2 cell AA10.

I hope that makes sense and is of some value.