# Excel Formula

#### Chard9401

##### Board Regular
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?

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

##### Well-known Member
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

##### Well-known Member
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

##### Legend
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

##### Board Regular
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

##### Legend
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.

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

#### Chard9401

##### Board Regular
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

##### Legend
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

##### Board Regular
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

Replies
5
Views
103
Replies
11
Views
332
Replies
3
Views
96
Replies
8
Views
123
Replies
4
Views
261

1,186,808
Messages
5,959,933
Members
438,455
Latest member
Beverly Jarrell

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back