Referencing Worksheets from the value of a cell.

[BrianClough]

Hi,

I'm sure this is probably very easy to do, but I have an inventory spreadsheet, which is duplicated each day. The sheet follows the date. 26112018 27112018.

The cells which reference the previous days data, are linked to the previous sheet for the date in question.

Once I duplicate the sheet the following day, all of the links are for the day before yesterday, and need to be changed so they match yesterdays date.

Is there anyway to make a formula so it builds the sheet automatically, just by changing the required date in a single cell?

Kind regards,

Brian C.

 

[mrshl9898]

Something like this?

=HYPERLINK("#'"&TEXT(TODAY()-1,"ddmmyyyy")&"'!A1")

This will go to sheet 27112018 (Today -1) cell A1

Or to build it off a date in a cell, where B1 is a date.

=HYPERLINK("#'"&TEXT(B1,"ddmmyyyy")&"'!A1")

 

[Richard U]

Use the INDIRECT function

Have the date value in a cell Say... "B77" where "B77" contains "26112018"

then modify the formulas to use the indirect function.

=INDIRECT("mybasesheetname" & B77& "!A1")
=INDIRECT("mybasesheetname" & B77& "!A2")

et cetera

That way, each day, all you need to do is change the value in cell B77, or whatever one you put the date value in

 

[BrianClough]

Use the INDIRECT function

Have the date value in a cell Say... "B77" where "B77" contains "26112018"

then modify the formulas to use the indirect function.

=INDIRECT("mybasesheetname" & B77& "!A1")
=INDIRECT("mybasesheetname" & B77& "!A2")

et cetera

That way, each day, all you need to do is change the value in cell B77, or whatever one you put the date value in

---

Perfect. Thank you.

=INDIRECT("" & D3& "!D6")

That got it working how I needed.

 

[BrianClough]

I also have another formula, which I could do with being able to combine with an INDIRECT if possible. Where it links to the 26112018 page on the sheet, to the date I needed?

The formula is: =IFERROR(INDEX('C:\123\[RECEIVINGS.xlsm]26112018'!$K$2:$K$133,SMALL(IF('C:\123\[RECEIVINGS.xlsm]26112018'!$D$2:$D$133 = $E6, ROW('C:\123\[RECEIVINGS.xlsm]26112018'!$D$2:$D$133) - ROW(INDEX('C:\123\[RECEIVINGS.xlsm]26112018'!$D$2:$D$133,1,1)) +1), $B$6)),"0")

Would that be possible at all?

Regards,

Brian C

 

[BrianClough]

With a bit of playing. Success.

=IFERROR(INDEX(INDIRECT("'C:\123\[RECEIVINGS.xlsm]" & $D$3 &"'!$K$2:$K$133"),SMALL(IF(INDIRECT("'C:\123\[RECEIVINGS.xlsm]" & $D$3 &"'!$D$2:$D$133") = $E6, ROW(INDIRECT("'C:\123\[RECEIVINGS.xlsm]" & $D$3 &"'!$D$2:$D$133")) - ROW(INDEX(INDIRECT("'C:\123\[RECEIVINGS.xlsm]" & $D$3 &"'!$D$2:$D$133"),1,1)) +1), $B$6)),"0")

Thanks again guys.

 

[Richard U]

Do some research on the EVALUATE function, that's another powerful one as well.