Spreadsheets problems

sorryitsme

isplumm wrote: »

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

Hi Thanks for responding, I need all the info from the 2 columns from sheet1 not just one cell, so does your tip still apply?

bigpat

Hi

Based on your explanation, I think the simplest thing you can do is:

1) Create your "master" sheet. You can copy/paste from other sheets if you already have a lot of the info you need, which will save retyping.
2) Now go to a brand new sheet. If you don't have enough spare emtpy sheets I *think* you can create new ones by right-clicking on an existing worksheet tab. Might depend on your version of Excel.
3) Select a cell where you want the first entry in the relevant column of the master sheet to go.
4) Type the = sign and then go back and select the first entry you want to link from in the master sheet. Your formula bar should now say something like "=Sheet1!A2" (assuming you're linking from cell A2 on the worksheet called Sheet1.
5) Press Enter or click the green tick on the formula bar. This cell should now update with the correct value. You need to get this step right before your proceed further.
6) Assuming all is OK above, position your cursor at the bottom right of the cell until you get a "crosshairs" cursor.
7) Now click and drag all the way down as far as you need to go. This will replicate the formula you just entered, so the cells will all display the correct values.

Repeat steps 3 to 7 for the second column that you want to go in this sheet, selecting the appropriate cell at step 4

Repeat steps 1 to 7 to create another new sheet and link a further two columns there.

Now, whenever the master sheet is updated, the others will too, because they don't hold "data", they hold "formulae" that *refer* to your data.

I hope this helps!

Pat.

arbroath_lass

If you type in = then click the cell you want on the sheet you want you'll get what you need. You can do this from different workbooks too. You'll need to copy it down each column though.

sorryitsme

Thank you for your replies I am going to slowly follow your instructions and will update you later.

Many thanks:beer:

sorryitsme

Hi I have tried to do what you have suggested and it didn't work.

My worksheet is mainly text and dates, is this the reason why it didnt work?

If you are able to assist in any way that would be great.

bigpat

I've sent you a private message. Can't do attachments on this forum for some reason but we'll figure out a way round this. It's difficult to explain without showing you something!

Owain_Moneysaver

If you select the cells in Sheet 1 you want to copy across, and click "copy"

Go to sheet 2, put your cursor in the top-left cell of the area you want to paste in. Then click on the small arrow at the bottom of the "Paste" button to open the drop down list. Click on Paste Link in the list.

The cells should be copied across, but all in the form of links back to cells on Sheet1. So when you update Sheet1, the cells in Sheet2 should follow.

That's for Excel 2007, other software should be similar.

If your cells on Sheet1 have special cell formatting for dates etc, you might have to reapply the formatting to the cells in Sheet2.

bigpat

Paste Link. Darn it I should have thought of that! Does the same thing, just a quicker method of creating the same formulae. Good tip!

sorryitsme

Can I just thank you.:A

You have made my life so much easier.:j

bigpat

Glad you got it sorted!