Filling a cell from one worksheet to another

Topic

New Reply

I use a spreadsheet for conducting inspections on multiple items. At some locations I have to use as many as 15 worksheets to complete all of the inspections. I can copy the basic page from one sheet to another, but I often have to fill in the same data on every page such as the date, or the date the next inspection is due or some other common data such as the name of the inspector (which may change from one inspection to another). I’ve tried to link the cell on page two with the equivalent cell on page 1, but it’s not working. Can anyone help out with the formula or solution for this.

Thanks

Reply | Quote

Replies

Not fully sure what you are trying to do but if the cell on page 2 should reflect what is in page 1 then:
go to the cell on page 2 type an equals sign, go to page 1 click on the cell you want to reflect and press enter.
This will take you back to page two with the finished formula.

Reply | Quote

Can anyone help out with the formula or solution for this.

A little more explanation with stripped down copy, if possible, is required to know what exactly you are looking for.

Reply | Quote

I’ve tried to link the cell on page two with the equivalent cell on page 1, but it’s not working. Can anyone help out with the formula or solution for this.

on Sheet2, cell A1 enter
=Sheet1!A1
and it will return the contents of cell A1 on Sheet1

stuck

Reply | Quote

I use a spreadsheet for conducting inspections on multiple items. At some locations I have to use as many as 15 worksheets to complete all of the inspections. I can copy the basic page from one sheet to another, but I often have to fill in the same data on every page such as the date, or the date the next inspection is due or some other common data such as the name of the inspector (which may change from one inspection to another). I’ve tried to link the cell on page two with the equivalent cell on page 1, but it’s not working. Can anyone help out with the formula or solution for this.

Thanks

I can’t explan why you are having a problem with linking the Sheet 2 cell to that on Sheet 1, but the basic process is
Go to Sheet 2
Click on the cell you want to link
key the = sign
click on the Sheet 1 tab and select the cell you want to link to
hit enter – focus will go back to Sheet 2. Enter data into the cell on sheet 1, If data doesn’t get copied through, hit F9, if it now shows, go into Tools | Options | Calculations and check that the ‘automatic’ option is selected.

A simple (but potentially hazardous) way to fill in the same cell on multiple sheets without having to set them up in advance is to Ctrl + click on the tabs so that you are ‘grouping’ the sheets. Data etc. that you enter into a cell on the visible sheet will be entered onto ALL the other sheets.

However, keeping track of what sheets you have selected can be tricky, and I have sometimes overkeyed data inadvertantly, because I wasn’t sure which tabs were selected.

Reply | Quote

A much more efficient method is to define each cell on Sheet1 as a Named Range. Then, on Sheets 2 through N, use the range in the formulas. For example.

1) In Sheet1, cell A2 is the date of the inspection. On Sheet1, highlight cell A2, and choose Insert, Name, Define. Type a name, such as InspectionDate, into the text box, as the name of the range, and press OK.

2) On Sheet2, select cell A2, and enter the following formula.

=InspectionDate

Once you have Sheet2 populated, use it as the template for successive sheets.

This works because, by default, named ranges are visible, and have fixed addresses, of the form Sheet1!$A$2, which means that they work in formulas, even if the cell containing a formula that references the range is copied.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote