Display cell/sheet information depending on data entered

[scribbler]

Can anyone please help me with Excel. I have 7 spreadsheets (1 per day of week)I also have another main sheet which I want to display data from just one of the days sheets but the selection of the day sheet data to display to be dependent upon the date or day entered into the title of my main sheet. Sounds a bit long winded but basically if I enter Monday on my main sheet I want to display the relevant data from cells on the Monday's sheet.

I presume VBA is required but haven't used it for many years and only then using guidance from a book for some minor stuff.

 

[xlbo]

I will 1st raise an issue with your data layout. you would be far better off holding ALL your data on 1 sheet with an extra column to identify the date / day of week. If you do this, what you require will be very straightforward

If this is not something you want to do (I strongly advise you to do it) you may get around your current situation by utilising the INDIRECT function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[scribbler]

Thanks For the reply Geoff

I note your comments about keeping data on the same sheet.

This is a project I've inherited and I think the reason for being on different sheets is to do with different users using their own individual days/sheets in an attempt to stop them entering data into the wrong days cells in error. I looked at the indeirect function but am unsure of how that will help.

 

[xlbo]

in what way are you "not sure" ?

It takes a literal STRING piece of data and converts it to an OBJECT / REFERENCE (ie a range / worksheet etc) so you are referring to an object indirectly (hence the name of the function)

You may therefore write a formula such as:


with "Sheet4" in cell A1
=INDIRECT(A1 & "!D10")

will return the value in cell D10 of sheet4

You may hold different sheet names in different cells and use a lookup or IF statement to choose which one to use

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[scribbler]

Ok Cheers Geoff I'm away to try this out.

Thanks for your help