Excel Formula

Chard9401

Board Regular
Joined
Jan 7, 2004
Messages
53
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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
Joined
Apr 11, 2002
Messages
2,314
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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Jan 7, 2004
Messages
53

ADVERTISEMENT

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
Joined
Sep 3, 2002
Messages
10,473
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 7, 2004
Messages
53
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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Jan 7, 2004
Messages
53
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,681
Members
425,229
Latest member
Rashid mahmood

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top