Formula to extra info from another sheet

[Jet Fusion]

Hi

I would like to have a fomula that when
1. I select a number from the reference sheet2 (highlighted in yellow in attached file) it fills out the info from sheet1 (highlighted in blue in attached file) and
2. In Rev sheet2 it should fill in the latest number in that row (highlighted in orange in attached file) according to the info in blue. aswell
3. In the QTY sheet 2 I would like a formula to extract the number from sheet1 (highlighted in green in attached file) according to the info in blue.

All this above depends on wheather there is info in the Issue History eg: a date and in Rev History a number when selececting the number in reference on sheet2.

Thank you in advance
Jet

 

[deciog]

Jet Fusion

If I select the reference 62 what to bring, if I select 49 what to bring, type in the spreadsheet the expected result because it is generating a lot of doubt to assemble the formula

Decio

 

[Jet Fusion]

Decio

Hope this makes sence.

Jet

 

[deciog]

Jet Fusion

Doubt in the attachment

Decio

 

[Jet Fusion]

Decio

Have updated.

Jet

 

[deciog]

Jet Fusion

Okay, I'm starting to understand.
I ask, in cells G16 to G18, do cells B16 to B18 contain value will they also be listed in worksheet 2?

Decio

 

[Jet Fusion]

Decio

Correct but only if G4 has a date if no date then no data will be copied to sheet2.

Data that should be copied from sheet1 if there is a date will be
1. Sheet1 B15
2. Sheet 1 C15
3. Sheet 1 D15
4. Sheet 1 G15

Jet

 

[deciog]

Jet Fusion

I'm not done yet, but check if it's correct so far, do several tests

Decio

 

[Jet Fusion]

Decio

Thank you that is working just for a few things I picked up and made notes in workbook.

Thank you

 

[deciog]

Jet Fusion

Now in the afternoon I go to a client I must return only at the end of the day, continue tomorrow ok

Decio

 

[Jet Fusion]

Decio

I understand, thank you. Tomorrow we continue

 

[Jet Fusion]

Decio

It will work similar to the attached template - so when you selcect the Invoice number it popluates the entries for that invoice number.

Jet

 

[deciog]

Jet Fusion, Good Morning.

This spreadsheet is getting beautiful tf03107658_win322

Note that I made an auxiliary column (cell L14: L20) that can be hidden

Do several tests again,

Decio
Note: I am in Brazil in the city of São Paulo, different time zone than yours

 

[Jet Fusion]

Good morning Decio

I changed your formulas back to previous version Issue Decio 2 because Issue Decio 3 was not working correctly only (Cell L14:L20

=IFERROR(INDEX(???,SMALL(IF(???[???]=???,ROW(???)-ROW(???[#Headers])), ROW(1:1)), MATCH($?$?, ???[#Headers], 0)),"")

This is the code that is used in the template, but I don't know how to update it to my spreadsheet becaue it depends on if there is a date and a value.


Ah okay, we have 5 hours differnce, here is afternoon already.

Thank you in advance.
Jet

 

[deciog]

Jet Fusion

Corrected the formula

Do you have Excel version 365?

Now here in Brazil it's 10:26:00 AM

Decio

 

[Jet Fusion]

Decio

Eu uso 365.

Ainda não está funcionando. Talvez eu não tenha explicado corretamente, desculpe.

Tentamos novamente na próxima semana, tenha um bom fim de semana.

Obrigado pelo seu tempo e ajuda.

 

[deciog]

Jet Fusion

I made several changes to the formula now check if it is correct

Careful you have a lot of merged cells so check the formula that I force to get the data of the first value of the merged cells

Decio

 

[Jet Fusion]

Hi

I checked the file and there are 2 issues:

1. The sheet2 is showing entries that should not be shown - col B, col D and col E should update like col H14 (This is correct way)
2. Sheet2 col J not updating to the same amount of entries in col B, col D and Col E should update like col H14 (This is correct way)

If we can get this right then it will be

 

[deciog]

Jet Fusion

So that I have no doubt, you can type all the expected results for reference 52, so I can assemble the formula

Decio

 

[Jet Fusion]

Decio

Please see changes in file.

Thank you.

 

[deciog]

Jet Fusion

Now I understand, do several tests

Decio

 

[Jet Fusion]

Decio

Thank you, its working now, maybe I should have said it like that before then we wouldn't be so long on this **


YOU ARE


Thank you so very much for your patience and help.

 

[Jet Fusion]

Sorry one thing I've noticed is that the the formula limits the entries to 4 rows, there will be times where this may vary to many entries in some instances and some only be 1 entry, sometimes 2 entries and so on.

Is it possible to change the formulas in B14, D14, E14, H14 7 J14 to not have a limit on how many rows?

Thanks

 

[deciog]

Jet Fusion

I agree always post a model with expected result so it is easier to understand and test the formulas

All cells that have this value {1;2;3;4} changed to this ROW ('1'!$G$15:$G$18) -14, so when you add a line automatically the formula is correct

Decio

 

[Jet Fusion]

Thank you will do.