Spreadsheets problems

sorryitsme

Hi

I am looking for some help with some spreadsheets I have.

Basically I have about 4 spreadsheets of which the info is interlinked.

I would like to merge the information via creating a main spreadsheet page and then create 4 separate page(tabs) at the bottom of the work sheet which would update every time I update info on the main page. The info the 4 pages are the 4 original spreadsheets

I know I may not of explained myself properly so if you require more info please ask.

Many thanks in advance

12bdebt3

The best thing I could advise you to do is create a new spreadsheet with a hyperlink to the other documents. Type something in a cell and right click, insert hyperlink and choose the document radio tab and choose the other excel file you wish for it to open.

Alternatively you could copy and paste the tabs into one of the spreadsheets, depends how you want it.

fwor

So you have four spreadsheets in separate files that refer to one another by filename?

What to do probably depends on what software you are using, but in Excel or OpenOffice, you just need to select of the cells in use in one file, right-click and "Copy, go to your new "all in one" spreadsheet, create a new sheet as a tab, go to that tab, choose "Paste Special" and make sure you are pasting everything, and paste it all in.

Repeat for the other 3.

Then you will just need to correct the links so that they refer to other tabs, rather than other files, which in both Excel and OpenOffice you can do simply by pointing to the relevant cell that you want to make reference to.

sorryitsme

Thanks for your replies, I am using Excel. I am going to try and link them using your suggestions, I may have to come back to you as I am not technically minded.

12bdebt3

That's fine I'll try help. I'm better with VBA code and working with buttons through macros, but you probably don't know what I'm on about there haha.

sorryitsme

arrontdep wrote: »

That's fine I'll try help. I'm better with VBA code and working with buttons through macros, but you probably don't know what I'm on about there haha.

Come again lol:rotfl:

kmmr

So you want to move all the sheets into one document? And then maintain the links from one sheet to the other rather than between files?

Well, simple way to do step one (rather than copying and pasting) is to right click on the sheet name, and select 'move or copy'. When I say sheet name, I mean the actual name on the tab down the bottom left of the screen. Normally it says 'Sheet 1', or you may have renamed it.

When you have selected 'move or copy' you get a little menu box up. If you want to keep the copy where it is (which is a good idea) click the box marked 'make a copy', and then in the dropdown list you can choose the other file you have open to move a copy to. If you don't say 'make a copy' if might automatically move the links, which would be nice, but if you do that I suggest you save a copy first before playing around!

I don't have excel on this computer, so I am not 100% sure of the menu names, but I have done this a million times so should be close.

I think there is a link manager somewhere in Excel, so if you find they are still linking to other sheets you may be able to update them elsewhere. But... as I said, can't check it now, so perhaps the move/copy option above will sort it for you.

12bdebt3

kmmr wrote: »

So you want to move all the sheets into one document? And then maintain the links from one sheet to the other rather than between files?

Well, simple way to do step one (rather than copying and pasting) is to right click on the sheet name, and select 'move or copy'. When I say sheet name, I mean the actual name on the tab down the bottom left of the screen. Normally it says 'Sheet 1', or you may have renamed it.

When you have selected 'move or copy' you get a little menu box up. If you want to keep the copy where it is (which is a good idea) click the box marked 'make a copy', and then in the dropdown list you can choose the other file you have open to move a copy to. If you don't say 'make a copy' if might automatically move the links, which would be nice, but if you do that I suggest you save a copy first before playing around!

I don't have excel on this computer, so I am not 100% sure of the menu names, but I have done this a million times so should be close.

I think there is a link manager somewhere in Excel, so if you find they are still linking to other sheets you may be able to update them elsewhere. But... as I said, can't check it now, so perhaps the move/copy option above will sort it for you.

You are completely right, I must have been half asleep when I was posting last night, I referred to a worksheet as a spreadsheet and I called the spreadsheets tabs hahaha!

kmmr is right you can copy the spreadsheet from the tab at the bottom of the page, go into one other worksheet and paste it there, they will all be on the bottom of the page in tabs, sheet 1, sheet 2, sheet 3, etc. If these aren't showing up go into tools - options - then make sure there is a tick in the box next to 'sheet tabs'.

P.S. VBA is basically a form of coding and script for Microsoft Office applications. Similar to what HTML is to web browsers...

sorryitsme

Hi all just to let you know I haven't started creating the new spreadsheet and plan to do so on Wednesday.

If I have any problems I will let you know.

Many Thanks

sorryitsme

Hi everyone I tried to create the spreadsheet today using your tips, but it didn't work out.

I do not think I explained myself fully and still need help.

I am going to scrap the 3 other worksheets as alot of the info is repeated and want to create a new one to do the following:

On sheet 1 I have several columns with information

What I would like is for sheet 2 to have two of the columns of sheet1 info

Sheet 3 to have two columns of sheet1 and the same again for sheet4.

Basically I would just like to enter the details on sheet 1 but it automatically updates the 2 relevant columns on the other sheets.

Sorry If I am still not making any sense.:p

Thank you in advance:A

isplumm

Hi,

Well this is fairly easy - assuming you have sheets called Sheet1, Sheet2 do the following:

1. On Sheet2 in the cell where you want the data from Sheet1 to appear type "Sheet1!A1" Sheet1 is the name of the sheet - so can be whatever you call it. A1 is the cell reference of the cell in Sheet1.

Just repeat.

Mark