Help to create forumla that automatically fills a row of info when conditions match

Hi Guys,

I have attached the sheet I am working on with a description of what I am trying to do.

When I enter an invoice number on sheet 2, I want the information on sheet 1 that has the same invoice number next to it to transfer onto sheet 1 in a list, bringing across the information that relates to that same invoice number. i.e. floor, house type, plot, stage, amount.

It's difficult to explain without looking at it, and I have explained more clearly on the spreadsheet attached (I hope).

I'm not sure if this can even be done, or if Im asking too much from excel...

Any help would be very much appreciated as I have only basic skills at writing formula and I'm really stuck

Thank you in advance to anyone who can help.

Kate

Hi Kate

When you type in the invoice information on sheet 1, in sheet 2 are you wanting to keep the information on a running basis, or is it a new invoice to be generated each time and then this is sent to the customer? Then go back to sheet 1, do another one and so on?

I'd imagine the best way to approach this would be to rearrange your data into something more manageable but I think you might be able to use something along the lines of:

=INDIRECT("'Sheet 2'!" &ADDRESS(6,MATCH(G5,'Sheet 2'!A2:O2,0)-3))
in sheet1 A4

=INDIRECT("'Sheet 2'!" &ADDRESS(6,MATCH(G5,'Sheet 2'!A2:O2,0)-2))
in sheet1 B4

and so on.

Hiya, thanks for the reply. No I want to generate a new invoice each time to send to the customer, it doesn't need to be a running total.

Kate

Hi Kate

Based on your current set-out, the below Macro should work. It's a pretty simple copy and paste macro, which when run will just copy what you have input on sheet 1, and put it into sheet 2. If you have a look through it, if you wanted to make the sheet look different, you should be able to change the code to work with the new layout. You can either apply this to a button, or just 'Run' macro through the developer tab

Please Login or Register  to view this content.

Oh wow, ok so I haven't a clue about Macros, never ever used them or wrote one

Edit: I've just re-read what you are asking and I misunderstood things, you should probably use macros for that sort of thing.

It's OK - can walk you through it (hopefully)...

If you've not used them before, it's quite likely you dont have the Developer tab available. So go to Excel Options (File - Excel options), under Popular, tick 'Show Developer Tab in Ribbon'.

Then back in your Excel workbook hold down Alt and press F11

This should then bring you to the Visual Basic Screen - copy and paste the code above into there. Then exit that (just the red cross in the top right hand corner.

Then go to the Developer tab. Click 'Insert' from the tab and select the first thing under Form Control. Then drag out the shape of the button where you want it on your form. Right click on the button, select 'Assign Macro', select the macro you just saved and click OK.

Now, pushing that button should run that macro

Hi thanks so much. Firstly do I put the Macro button in sheet 1 or sheet 2?
Secondly the worksheet I attached 'sheet 1' is just a snippet from a much much larger sheet, i.e. there are 250 plots, and 10 stages. I obviously need to change the 'G5' etc on the macro, can I put in ranges? i.e. 'G5:G270'?

Sorry if this sounds dense, I'm not good at this lol

Kate

Hi Kate

No question is dense, it's a quest for knowledge

Put the button in Sheet 1, this will then mean that you put your information manually into sheet 1, then press the button and it puts it all into sheet 2 for you.

The ranges bit is difficult - the code that I sent you only copies the first one into one invoice template. So, I'm assuming that, as you have 250 plots, you'd need a total of 250 invoices?

I'm not overly sure the best way to 'attack' it would be. With the information you get for the plots, and how much to invoice, are all 250 plots manually input, or are they generated from a report or something?

My thinking is, it might be easier to have all the information as a table - so column headers of reference number, plot, billed etc with all the information you need.

Then having one button which puts them all into separate invoices - but this is a much bigger task. There could be quicker and simpler ways round it.....just not sure at this second......will have a bit of a think, but maybe post any information you can that could be relevant to getting all the data together

Hi and thanks for all your help, truely I really appreciate it. No, there can be up to 50 items on a single invoice, for example :-

Plot 1 Stage 2
Plot 2 Stage 1
Plot 12 Stage 3
Plot 15 Stage 4
etc etc

Each Invoice number input onto sheet 1 will be used and input against various plots and stages, so generating an invoice with lots of different plots and stages on it each time.

The information on sheet 1 is automatically transfered over from another sheet (the quote sheet) the actual worksheet is already fairly large and complex (15.0MB), and every page relates to another in one way or another.

Kate

Please Login or Register  to view this content.

I think that would give generate invoices for an arbitrary number of floor/plot/units and stages. Although you might eventually run out of columns I guess.

Got ya - think I see how it all works.

Yudlugar's code above looks like would be your best bet for now, but as said within message you may well eventually run out of columns.

My suggestion would be use this for what you need right now, but the best bet could be a friendlier re-design starting with the quote sheet to bring everything through in a report style, and perhaps look at invoices for each reference number, or the possibility of building one for each so they update when new parts are added.

I always start with a bit of paper, and just right down where I want to get to, it;s easier to build and design it that way and may help to make it more friendly in the end of it

Hiya, that last code sort of works. It generates the invoice, but doesn't distinguish between the invoice numbers i.e. it ran an invoice for all of plot 1, whereas I have actually invoiced the two stages on two separate invoices, invoice 455 and 456 (cells G5 & J5 respectively). It also didn't take into account any of the other plots or invoice numbers.

When I run the button I also get an error message with the red X saying '400'... any ideas?

Am I making sense???

Kate

I can see the issue with the first one, I hadn't noticed you could have different invoice numbers for different stages.

When I run it on your example spreadsheet it generates the other plots and invoice numbers though, not sure what the error message would be. Could you upload the workbook with the code and button as it generates the error?

Hiya,

Have attached the workbook with my attempt at putting in the macro. When you press the button, you should see the error message, and what I mean about it generating only one invoice.

Thanks again for your help.

KLate

Ok try this one:

Please Login or Register  to view this content.

Hi Yudlugar, I have tried this one also but still not quite doing what I want, it's only doing an invoice for two out of the 5 invoices... and all the invoices are appearing on one page also.

Sorry

Kate

Could you upload your workbook again with the macro in? I tried running it and it seemed to work ok...

Do you want a separate invoice on each page?