populating data in blank cells

[battenborrow]

Hi,

I hope someone can help as this has baffled me so far for 48 hours

I have a workbook with a few sheets that populate each other, depending on what is entered in the input page.

On this input page I have an itemisation sheet for quoting customers for work carried out. An example of what can be on this sheet is below

Product - QTY- Price - Frequency

Installation - 1 - £30.00 - One Off
Rental pt1 - 1 - £13.99 - Monthly
Rental pt2 - 1 - £15.99 - Monthly
Hardware - 1 - £40.00 - One Off

this data is then passed onto another sheet to create a quotation, with monthly and one off charges seperated by an if statement

=IF(Input!R57="One Off",Input!B57,"")

This will import the one off charges through to a seperate part of the quotation, but obviously leave the monthly charges blank so the quotation would look like

Installation - 1 - £30.00
[BLANK]
[BLANK]
Hardware - 1 - £40.00

What I need is these blank fields removing so it would show the following

Installation - 1 - £30.00
Hardware - 1 - £40.00

Any ideas, but remember it must include the IF statement to enable it to only show the one off charges.

[battenborrow]

If it is easier, I have added the workbook, with the 2 sheets required left open.

On the input page under the title order itemisation is why the data will be typed, and this then passed through to the signum quote sheet.

As you can see I have done the formula to only show the items listed one off, this is correct but I want to know how to copy it to the signum quote sheet leaving blank spaces where the monthly payment should have been

Your help will be appreciated as this is hurting my had

[DBY]

Hi
Please see attached file for possible solution. I've created a new named range on the Input sheet (RowRef) in column S which is hidden. On the Signum Quote sheet I've used INDEX functions to return the values. The formulas look a little long but I've had to use the ISERROR function to clear blank entries. I've also changed the formula in the Total Price column to account for blank entries. I've been doing this in Excel 2003 so I hope the conversion doesn't affect anything. Hope this is what you're looking for.

[shaukat]

Hello your merged cells are not identically sized in both sheets, it is difficult to copy and pate data using loop control.sheet "input" Cell B is merged with 14 cells. while other sheet cell B is merged with 10 cells. I have made a macro, but it is not working because of this difference

[battenborrow]

Hi

Sorry for the late reply

I have created a macro for this, and it works perfect, I have used snippits from other codes and merged them together and this works fine.

I did have to make the merged fields the same size, so thank you for that tip.

The code I created for this is 13 pages long so I will not post it all but below is the code I used, repeating for all fields that needed moving over

Please Login or Register  to view this content.

[battenborrow]

Sorry DBY

I had not seen your post before I had 'solved' this myself, but many thanks for your help