referring to a cell on another sheet using the contents of a cell

[merlin777]

[TABLE="width: 64"]
[TR]
[TD][TABLE="width: 975"]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]I have a sheet which I want to populate with data from a day on someone's timetable. [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]For instance, if A1 contains 'joe bloggs' and a2 contains 'Tuesday', I want to fill the sheet with joe bloggs' schedule for Tuesday.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]There are 20 sheets with a timetable on each, labelled with a persons name and each sheet has 5 day columns.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]I guess i'm looking for the correct way to construct a formula that says 'return the value in a column specified by the value in a2, on the sheet named by the value in a1.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]I'm just not sure of the way to construct it.[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[/TABLE]

 

[Oeldere]

You could do that with VBA. Your talking about worksheets; so I suppost all workers use the same file?

 

[Derek Brown]

You need the INDIRECT function.
Have a look at:
INDIRECT Function
How to use the INDIRECT function to create references in Excel
INDIRECT function - Excel - Office.com

 

[merlin777]

Thanks. How do I reference a cell in another sheet?I understand that INDIRECT(A1) will return the value in the cell A1. I can use =indirect(address(2,1)) to return the value of cell A2 (confusingly because the row and column seem reversed!)Now I need to specify the sheet and column, e.g. on sheet 'joe bloggs', the contents of row 1 of the column labelled 'tuesday'.Please could you get me another step closer?

 

[Derek Brown]

Follow this example:
In Cell A1 of a worksheet with the name "Joe Bloggs" enter the value 5.
In Cell A1 of a worksheet with the name "Sheet2" enter the name "Joe Bloggs" (without the double-quotes).
In Cell B1 of a worksheet with the name "Sheet2" enter "A1" (without the double-quotes).
In any cell of a worksheet with any name enter the following formula:
=INDIRECT("'"&Sheet2!A1&"'!"&Sheet2!B1)
The formula should return the value of 5.
Use the "Evaluate Formula" feature to step through the formula to see how it works.

 

[merlin777]

OK, I've managed that and I can see how the concept works. I'm having trouble understanding all the punctuation though.I'm not sure what the single and double quotes are for or what the sheet address format is. I thought I did but when I try to replicate what you've done with my own data I just get errors.

 

[Derek Brown]

=INDIRECT("'"&Sheet2!A1&"'!"&Sheet2!B1)
is the same as
=INDIRECT("'Joe Bloggs'!"&Sheet2!B1)
is the same as
=INDIRECT("'Joe Bloggs'!A1")
Without the single quotes:
=INDIRECT("Joe Bloggs!A1")
an error would be reported because there is a space between "Joe" and "Bloggs".
If you remove the space from the worksheet name then the following would work:
=INDIRECT("JoeBloggs!A1")

You need to look at the examples in the links that I gave (particularly the second one) and also use the Evaluate Formula to look for the reason for errors when they occur.

 

[merlin777]

many thanks Derek.