Copy Sheet1!:Column A to Sheet2!:ColumnB 1

[attrofy]

Here is my scenario:

I have a spreadsheet for inventory purposes. Sheet1 is the Data Entry sheet. I am copying certain headers and coresponding data (all info from the same row relates to each other) to my Price List sheet. Then I am conditionally formating the Price List sheet, to use as handouts to salesman/customers.

The problem I am running into is if I go to the Price List page, and highlight Column A, then enter

=Data Sheet1!H:H

It copies the cell value of to Row 1, but nothing for the rest of the rows. Now if I drag the data down, I get the corresponding data. However, on some columns where the info is blank, it is returning a "0". So if I drag down 500 or so rows, I may have 100 with a bunch of 0's.

This doesn't look nice for printing purposes, especially if my data ends at row 399. How can I keep this from happening, and how can I set the sheet up so that if I add new rows to the Data Entry sheet, they reflect on the Price List sheet?

Should be simple, but I can't seem to get it.

Thanks for the help.
Russell

 

[DaleWatson123321]

Russell,

I think I'll be able to come up with a solution for you. But it would of course be preferable (easier and faster) if you could send me the file - or a scaled down version if data sensitivity is a concern.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Nikita6003]

Hi Russell,

Dale's solution will be much more elegant than this (and will cost *much* less space as you're filling the entire column where it isn't necessary!!), but try this for a quick fix (here's hoping I've understood what you're trying to accomplish):

when you highlight the column, enter

=IF(Data Sheet1!H:H="","",Data Sheet1!H:H)

, then press [Ctrl-Enter] - this'll add the formula to all highlighted cells & will ensure that if any cell in column H on the Data entry sheet is blank, the corresponding cell on your Price List sheet'll be blank as well

This'll work - but *please* use Dale's solution once you've got it - you really don't want loads of formulas on your sheet unless absolutely necessary ;-) as Excel'll think you're using the entire column - one of the problems with this it that when printing the sheet Excelt'll want to print rows 1 to 65536 & you'll end up with loads of empty sheets

Cheers
Nikki

 

[GlennUK]

Whenever I do this, I format the area with a custom number format that suppresses zeros.

E.g. #,### shows non-numeric entry fine, and shows numbers with thousands separators, and also suppresses zeroes.

Glenn.

 

[attrofy]

Nikita,

I tried that, and open up my Windows Explorer, and tried locating a document....strange...

Glenn,
Although I agree that would work, won't I have to go back through and "un-edit" those areas once I have data in them? By the way, some of the data is zeros

Dale, I am forwarding you a copy of the spreadsheet.

Thanks all for your help.
Russell

 

[attrofy]

Thanks Dale, Fantastic job once again. This seems to have more than solved my problem...