Excel Database Watch

viirkokka
Badges: 1
Rep:
?
#1
Report Thread starter 10 years ago
#1
I need a bit of help making a database for a friend..

Firstly, I want to be able to have a number of sheets joined together so that sheet 1 has columns; number, description, date, pp, sp, profit
and then sheet two has; number, description, date, sp
and when you change the data in either of the sheets, the other one automatically updates. How do I do this?

Additionally I want to be able to sort the number, date and profit columns so that the rest of the data moves with them.. at the moment the column is sorted but the rest of the data stays in the same place

Help please!
0
quote
reply
viirkokka
Badges: 1
Rep:
?
#2
Report Thread starter 10 years ago
#2
bump!
0
quote
reply
AWSLC
  • CV Helper
Badges: 14
Rep:
?
#3
Report 10 years ago
#3
(Original post by viirkokka)
I need a bit of help making a database for a friend..

Firstly, I want to be able to have a number of sheets joined together so that sheet 1 has columns; number, description, date, pp, sp, profit
and then sheet two has; number, description, date, sp
and when you change the data in either of the sheets, the other one automatically updates. How do I do this?
Ok the first thing to do is to have your empty tables created in both Sheets 1 and Sheets 2.

Should look something like THIS.


Once you've done this, select the area you want to be identical from sheet 1, then right click, and select copy.

LIKE THIS.

Go to sheet 2, in the FIRST cell of the table, right click and Select PASTE SPECIAL.

LIKE THIS.


When the box comes up, you will see this:

THIS

Click on paste link.

And it should look like THIS.

There you go, from now on, any information you put into sheet 1/2 will automatically be updated on the other sheet, for the selected fields you copied.

EXAMPLE I MADE - CHANGE DATA IN SHEET 1
AND IT WILL CHANGE THE DATA ALSO IN SHEET 2.

(Original post by viirkokka)
Additionally I want to be able to sort the number, date and profit columns so that the rest of the data moves with them.. at the moment the column is sorted but the rest of the data stays in the same place

Help please!
AFAIK, there isn't an actual function to lock data within rows. The easiest way to do this would be:

Select the column you want to sort, like THIS

There should be a prompt informing you of external data attached to the selected column, select EXPAND THE SELECTION, LIKE THIS.


Once that is done there should be a popup box, asking which column you would like to sort by, select your desired sort in the dropdown box (for the purposes of this example I chose 'number') should look like THIS.

Once this is done, it will sort the column as you asked it to, by the heading you wanted it to, while still keeping the data in the row attached to it, like in this example HERE.


Sorry for the long winded post, tried to make it as simple as possible with pics and all, I hope it helps

PS I have attached the ExcelBook I used to create my example, in case you needed or wanted to use it
0
quote
reply
viirkokka
Badges: 1
Rep:
?
#4
Report Thread starter 10 years ago
#4
(Original post by UniStudent2009)

PS I have attached the ExcelBook I used to create my example, in case you needed or wanted to use it
Thankyou SOOOOOOOOO much that is incredibly helpfull! Pictures and all.. you really outdid yourself, thankyou!!

One question however, when I paste Special so that it automatically updates, is it possible to make it so that those columns will always automatically update, e.g writing on the next row down will also cause it to automatically update.. because there will be lots of new entries added on the rows. I hope I've made the question clear-?

Again, thankyou for the help
0
quote
reply
AWSLC
  • CV Helper
Badges: 14
Rep:
?
#5
Report 10 years ago
#5
(Original post by viirkokka)
Thankyou SOOOOOOOOO much that is incredibly helpfull! Pictures and all.. you really outdid yourself, thankyou!!

One question however, when I paste Special so that it automatically updates, is it possible to make it so that those columns will always automatically update, e.g writing on the next row down will also cause it to automatically update.. because there will be lots of new entries added on the rows. I hope I've made the question clear-?

Again, thankyou for the help

Yeah, when you first copy from sheet 1, just copy a hundred or so lines down more than you need, so instead of copying down to say row 100, copy down to 200, then continue with the steps as illustrated.


0
quote
reply
viirkokka
Badges: 1
Rep:
?
#6
Report Thread starter 10 years ago
#6
I've had a bit of a problem.. when I Paste Special all of the currently unfilled boxes are then filled with a single 0. Not a huge problem but it would be nice if there's a way to get rid of it?

Additionally, I copied into sheet 2 and then sheets 3 and 4, but when paste special'd into 3 and 4 the numbers of the Date column were messed up and became things like "39448" etc, any ideas? Or does paste special not work for multiple sheets?
0
quote
reply
X

Reply to thread

Attached files
Write a reply...
Reply
new posts
Latest
My Feed

See more of what you like on
The Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.

Personalise

University open days

  • University of Lincoln
    Brayford Campus Undergraduate
    Wed, 12 Dec '18
  • Bournemouth University
    Midwifery Open Day at Portsmouth Campus Undergraduate
    Wed, 12 Dec '18
  • Buckinghamshire New University
    All undergraduate Undergraduate
    Wed, 12 Dec '18

Do you like exams?

Yes (140)
18.52%
No (459)
60.71%
Not really bothered about them (157)
20.77%

Watched Threads

View All