excel 2003 summary page

[people3]

Hi All,

My knowledge of excel is fairly limited but I have been asked to create a workbook for quotes.

We need it to have two tabs - the first tab will have all the information required on but with sensitive data eg buy price and profit.

The second tab needs to show just the information that can be printed out to be given to the customer.

Doing this I have come across two issues

1, when I use the formula =tab1!d3 if the value is blank on tab1 then it shows a 0 on tab2 and I would like it to be blank.

2, every quote is of a different length so the number of rows is not fixed which relates to the issue above but also the total cost cell moves so I cannot duplicate onto the second sheet - is it possible to reference a moving cell.

Hope this makes sense

 

[xlbo]

1: Use an if statement to check what is in the cell 1st:
=IF(tab1!d3="","",tab1!d3)

2: Have a look at VLOOKUP. This will look up a specified piece of text / number and return a value associated with it:

=vlookup("Total Value",tab1!D:E,2,false)

will give you the value in column E next to a cell with the text "total value" in it in column D

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[people3]

They worked a treat,

One last question if on tab1 the value is 0.00 how can i get it to insert blank on tab 2

Many thanks

 

[xlbo]

Use the same logic

=IF(tab1!d3="","",IF(tab1!d3=0,"",tab1!d3))

Have a look at excel help for IF statements


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GSCaupling]

Hi People3,

Another solution to achieving a blank when the cell value is Zero is to use a custom number format.

The following should work: $#,##0.00_);[Red]($#,##0.00);""

Good luck.

 

[people3]

Hi All,

Many thanks for the replies - the summary sheet master piece is nearly finished.

My last issue is making sure the total column is the last thing on the last page (the summary page).

As I’m not to sure how many pages tab1 will be , is there a way to add the total field to the last page only on tab 2

Many Thanks

 

[SiliconXu]

If you want EXCEL not to show 0 value, you can do like this:
Tool -> Option -> View(tab) -> Window options -> uncheck the "Zero value"

Your last question:
Without seeing your file, it's difficult to tell you how to do. You can use COUNTA to calculate the num of the non-blank column because a field should already contain something.