Formula not collecting correct information

[Ms2BSwagg]

Hi All,

I have a formula and it keeps returning the wrong info or nothing at all.

I would appreciate if somebody could assist me to get the formula to return the info when an invoice number is selected like the name only (address line collects the right info) and also that the corresponding invoice number returns any info on the invoice main and invoice deatails in the description. I do have a formula but it keeps bringing up an error - see here

=IFERROR(INDEX(invDetails,SMALL(IF(invDetails[Invoice '#]=rngInvoice,ROW(Invoice!)-ROW('Invoice Details'!)), ROW(1:1)), MATCH($C$8, invDetails[#Headers], 0)),"")

I am not very good at formulas so i'm sure you'll find more errors

Thanking you in advance.

[FDibbins]

Have not opened your file (yet), but did you enter that as an ARRAY formula using CTRL SHIFT ENTER?

[Ms2BSwagg]

Hi,

Thanks for responding, to be honest no i didn't just typed it in and when hitting enter it brings up the error.

[FDibbins]

That is an ARRAY formula ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Ms2BSwagg]

Ive tried to do that but it still brings up an error, not sure if i'm doing it correctly

[FDibbins]

Just noticed you are from SA? Waar omtrent? PE old boy here, from Klerksorp, now living in USA

Where is this formula located?
I see you have a lot of REF errors in your file?

[FDibbins]

OK let me take a look at your file

[Ms2BSwagg]

"Awe ma se kinders" wat maak jy daar? Awesome, yeah i'm from Dbn so yeah, nice to meet a fellow country man.

Yeah that whole file is a mess lol, took it over from somebody and now i'm tyring to get it to work by adding formulas but ja nee :P

basically what it needs to do is the invoice main and invoice details will have info and then all corrosponding info needs to be transferred to the invoice when the invoice number is selected.

[Ms2BSwagg]

the ref error is because i deleted the worksheets that had personal info on but they don't have anything to do with the invoice

[FDibbins]

moved here to marry a woman I met on the net - 15 years ago now Sister lives in Durbs, too.

Its kinda later here now (3:30 am) and i can hear my bed calling.

I will take another look later, and see what I can come up with for you?

In the mean time, can you walk me through what you are trying to do?

[Ms2BSwagg]

Oh wow, nice. Maybe i should try that :P

Sure no porblem thanks any way. Lekker Dux

basically what it needs to do is the invoice main and invoice details will have info and then all corresponding info needs to be transferred to the invoice when the invoice number is selected.

Dankie, gaan slaap nou.

[JohnTopley]

Can you file an invoice as it is not clear what you require in the invoice detail. Yoy have one description but multiple charges/fees..

The "Owner ID":

=VLOOKUP(rngInvoice,'Invoices - Main'!$B$8:$K$13,4,0)

but you have a formula which obtains this from another source.

[Ms2BSwagg]

Hi John

Thanks for the reply.

Ah, so I had the table as a reference, not the worksheet, see this is where I get confused when to use the worksheet or the table. Okay, so that worked.

The other issue is that when I select the invoice number is should transfer all the information at corresponds with that invoice number to the invoice under the description, amount and however many rows that invoice number has. So bascially when you select the invoice number it will show all relevant info, you wont need to input anything besides the total which will be done when valuse are there.

And the same will be for the Statement

[JohnTopley]

What goes in "Amount" for description "Invoice KC01-0001 Data 1": "levy Charges", "Parking "Charges" or "CSOS" ?? Or is "TotaL" in Invoice Details.

Please as requested fill in dummy invoice with several lines and post a file.

[Ms2BSwagg]

Herewith updated file, I've changed a few things don't worry about ref errors I deleted sheets with info on, but they don't affect what I'm trying to achieve.

Thanks in advance for any help.

[JohnTopley]

Now confused more: why have you a drop-down list id "Description"?

I assume we require all records for"Owner ID" in G11.

And it (drop down) uses function not available in 2010: are you using a later version of Excel;? If so, please update your profile.

As I don't have a version other than 2010, I'll leave this to others.

And judging by the comments in your file, you may need VBA.

[Ms2BSwagg]

That's why I wasn't sure how to get to show the information in the description, did it that way just to show how I need it to look if you confused then I'm lost:

yes, we need anything that has the Owner id or invoice number needs to correspond.

yeah I use 2016 I have saved it to the earlier version but I'm guessing most if not all the formulas have gone:

Hope somebody can help with the VBA

thank you for helping out tho much appreciated

[Ms2BSwagg]

@JohnTopley just saw you helped me out a few years back