Referencing issues when inserting a row

**thebottomline** · 02-02-2010, 10:06 AM

Hi there,

I'm relatively new to Excel, but have tried my hand at creating a little spreadsheet that helps make mini-report writing easier in my job. What I have is one sheet with customers and their products (call it Products Sheet), one sheet (let's call Input Sheet) which has an input row (locked to the top of the page), whereby someone can select a customer and a product, enter the quantity ordered and it pulls the relevant data from the the products sheet. This is all working fine.

My issue comes from this; I have a third sheet, in which certain products need what we call a "pick list" for the warehouse, so they know what products and quantities to "pick" out of the warehouse. I have a macro called "Add another" on the Input Sheet in which it takes the information in the input row and moves it further down the sheet (to row 6), clears the contents of a few of the cells but keeps cells like customer, order number and delivery date. This is for some of the people at my work, making everything as simple as possible. For the sake of being able to add multiple products to an order "pick list", the macro copies the entire row, I right click the 6 on the left hand side and click "Insert Copies Cells", as I felt this made the macro writing easier as then column 6 would move down to 7, and 6 downwards would always be the products ordered.

In my third sheet, it is what I call the "cover sheet". In it it has cell references that check if specific cells in row 6, 7 etc. are blank and if not, put the relevant data in them. It is this sheet that will be printed so the references must be correct. My problem is, if it says to look up B6, and I run the macro and insert a new row, it changes the reference in the third sheet to B7. I've added $'s before and after the B and the 6, but that doesn't seem to have helped. The formula in the cell now reads:

=IF(ISBLANK('Input Sheet'!$B$7),"",'Input Sheet'!$B$7)

That was, if B7 is blank, it shows nothing, if it has something in it, it shows what's in it.

Can anyone see why this would be a problem, and how I can rectify it? Thanks, any help is much appreciated.

**DonkeyOte** · 02-02-2010, 10:10 AM

Without knowing the full ins and outs you could use INDEX to keep the references Fixed, eg:

=REPT(INDEX('Input Sheet'!$B:$B,ROWS(A$1:A7)),1)
copied up / down

In the above I used REPT - assumes Products are always Text where entered.

(you could also use INDIRECT rather than INDEX but INDIRECT is Volatile unlike INDEX).

In general though it's often better to copy & move values rather than physically alter the sheet by adding / removing rows - for (now) obvious reasons.

**thebottomline** · 02-02-2010, 11:41 AM

That didn't work, as it gave me the result as simply "True". But i removed the REPT part and it was excellent, thank you. I have another dilemma now though, I only want it to show if the column has anything entered. For example, it has certain cells which the text will only show if there is an order in that column. I don't know how to make them not show up if there isn't an order.

To give you an example, if B6 is blank, I want to leave a certain cell blank, otherwise I need it to say "GRN:" but, if my macro adds another row (i.e. another order), it puts this reference off. I tried it based on one of the index cells, but it didn't work, as the cell doesn't count as blank as there's formula in it..

Please help as I don't want the sheet to have 0's and the like in cells which I'd rather have blank. Thanks.

**DonkeyOte** · 02-02-2010, 12:03 PM

That was the point behind the REPT, which I should add would only return TRUE were that the content of the cell referenced - if not the content then I would suggest checking the entry again such that it matches the earlier suggestion.
If still incorrect please post a sample with formulae in place where incorrect results are generated.

If however you are keen to double evaluate simply repeat twice over, once to test for Null and once to return value where not Null.

=IF(INDEX('Input Sheet'!$B:$B,ROWS(A$1:A7))="","",INDEX('Input Sheet'!$B:$B,ROWS(A$1:A7)))

**thebottomline** · 02-02-2010, 12:16 PM

Ok, thank you. That formula is confusing, which elements would I go about changing if I were to have one cell wanting C7 instead of B7, and same again but for B8 and then C8, etc.? Would it be as simple as $B:$B becoming $C:$C and (A$1:A7) becoming (A$1:A8)??

Similarly, the cell underneath the one generating this result needs to say "GRN:" if there is an order in row 6, but not if there isn't. Right now I only had a simple formula of:

=IF(ISBLANK('Input Sheet'!C6),"","GRN:")

But obviously this doesn't work with the new macro. Apologies if this is mundane and simple, I'm trying to learn gradually!

**DonkeyOte** · 02-02-2010, 12:23 PM

I would suggest posting a sample file - it's not very clear what it is you're trying to do or at least it's hard to visualise the setup.

Regards returning C8 rather than B7, yes it's as simple as

i) altering the INDEX range from B to C

ii) changing the row_num such that instead of returning 7 it returns 8

I used ROWS as I had presumed from your original post that the intention was to copy the formula down to return the products as listed on your log sheet
the ROWS construct is such that as the formula is copied down so reference to rows $1:7 will become $1:8 thereby generating 8 rather than 7 and so on and so forth

**thebottomline** · 02-02-2010, 12:58 PM

Hi there,

Thank you so much for all your help. It appears to have worked now. Please see the attached for the visibility issues. I'm trying to stop everything you can see in the "Cover Sheet" sheet from showing unless there is an order in that row/column. Thanks.

**thebottomline** · 02-03-2010, 06:04 AM

Anyone have any idea how I can do this please?

**DonkeyOte** · 02-03-2010, 07:43 AM

Sorry, I couldn't tell from your penultimate post whether your required further assistance or not...

When I looked at the file I couldn't make a great deal of it given there was little data nor expected results.
If you provide a more details sample and outline expected results given sample data I'm sure as as Board we can resolve your issues.