consolidated sheet

[msbk]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am trying to make a consolidated sheet that contains information from a variety of other sheets.

The summary sheet has 5 basic columns (name, key, date, person, grade). I want these to fill automatically when someone fills in the appropriate information on a corresponding sheet. The way I know how to do this is to type "='sheet name'!cell reference" for example "='Project A'!B4

When I do this on sheet number 1 and the columns share the same letter (B and B in the example), it works, but when I try to use the same formula in any other column of sheet one, the error message "#VALUE!" is returned.

thanks so much!

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am trying to make a consolidated sheet that contains information from a
variety of other sheets.

The summary sheet has 5 basic columns (name, key, date, person, grade). I want
these to fill automatically when someone fills in the appropriate information
on a corresponding sheet. The way I know how to do this is to type "='sheet
name'!cell reference" for example "='Project A'!B4

When I do this on sheet number 1 and the columns share the same letter (B and
B in the example), it works, but when I try to use the same formula in any
other column of sheet one, the error message "#VALUE!" is returned.

thanks so much!

You may need to use an absolute reference,!$B$4 instead of !B4. Are you
copying the formula to different cells? Exactly what are you doing when the
#Value error occurs?

 

[msbk]

Using an absolute reference doesn't seem to make a difference. It also doesn't seem to matter if I copy the formula or do it from scratch.

Here are my exact moves.

On 'sheet 1" I select the cell I want and type =
then I click on the tab for the second sheet, 'A'
then I click on the cell in sheet 'A' that I want to appear in the 'sheet 1'
then I press return

thanks for your help. i appreciate it.

 

[msbk]

Hmmm, this may have something to do with it. The "cell" I was clicking on in sheet 'A' was merged. When I unmerged the cell it started to work, but that still doesn't explain why it shouldn't work with merged cells?

 

[JE McGimpsey]

Hmmm, this may have something to do with it. The "cell" I was clicking on in
sheet 'A' was merged. When I unmerged the cell it started to work, but that
still doesn't explain why it shouldn't work with merged cells?

Since you didn't say which cells were merged, I won't hazard a guess as
to why whatever you did didn't work.

BUT, that's yet another exemplar for why friends never let friends merge
cells. They're BAD to the bone and should never be used. Not even once!