Excel Formula

[Chard9401]

Is there a way in which if i have a sheet 'Main Page' and then 10 other sheets numbered 1 to 10. When on the 'Main Page' i enter number 10 in cell A1 i want whatever details next to it i.e cell B1 to be displayed on sheet 10. Or if i entered number 3 in cell A1 on the Main page the details willl be displayed on sheet 3?

 

[onlyadrafter]

Hello,

Do you wish this data on sheets 1 to 10 to be permanantly displayed or just while then sheet number is in A1 on the MAIN PAGE?

 

[jimboy]

You could enter this formula into Sheet 1 and copy down, the only problem is you will have loads of blank cells...

=IF(Main!A1=1,Main!B1,"")

You can do it by VB (worksheet change event) but it's a bit messy...

 

[just_jon]

Main Page A1 MUST be formatted as Text. Then, in sheets 1 - 10, in the cell of your choosing --

=IF('Main Page'!A1=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))),'Main Page'!B1,"")

 

[Chard9401]

thanks alot i will give it a go. So whatever number is diplayed in A1 will take the data in B1 to the relevant sheet. This is how i wanted it to work.

Example.

Headings A1: Truck Number B1: Job Description
This is where all data will be entered.

So if a job comes in for Truck number 1 the job description will display on sheet 1 as that is the number which has been entered.

 

[just_jon]

Yes; but when you change the Main Page A1 value the old value will disappear from the old sheet. That is, the combined formulas will show only one value at any given time.

 

[onlyadrafter]

WHich is why I asked an hour or so ago whether the data on sheets 1 - 10 needs to be permanent or not.

 

[Chard9401]

yes it does! sorry, it needs to be permanent on all sheets.

A B C
1 Truck I.D Job Description Time
2 14 Replacement Door 14.23
3 22 Rplace Sub Frame 15.16
4 14 Gear Box 20.24
5

now whatever number is entered in column A the details will automatically go to the relevant sheet. It must stay permanent on all sheets.

So whatever number is entered in A5 the job description and time will go to that sheet number

Sorry about the confusion and delay in responce.

 

[just_jon]

You'll want an event macro, then. Where in sheets 1-10 does this data go? Down a column, across a row? Which one{s} All cells?

 

[Chard9401]

the same as on the main page but with out the truck I.D because that is not needed as you are on that trucks sheet! I don't know how to sort it i know it can be done though