May 15 2020 02:32 AM
I'm trying to do the data storage and a clear data display in 2 sheets, in which it will work as example below:
Sheet 1(details| revenue| outlay repeat about 20 times)
invoice| details| revenue| outlay| details| revenue| outlay| details| revenue| outlay...
IData1 |DData1|RData1 |OData1|DData2|RData2 |OData2|DData3|RData3 |OData3...
Sheet 2
invoice IData1
details| revenue| outlay
DData1|RData1 |OData1
DData2|RData2 |OData2
DData3|RData3 |OData3
...
SumOfRData|SumOfOData
I used define&name and data validation to finish the invoice part, but I'm stuck at the [details| revenue| outlay], as the vlookup could not support such operation. Is there any way I can do this, showing the data according to the "IData" value?
Moreover, for some cases, those [details| revenue| outlay] may not be filled by 20 different items every time, sometimes may be only 4 to 5 would be filled, so any way make sheet 2 be smart, hiding the empty rows?
May 15 2020 05:41 AM
Tony
can you supply an example spreadsheet as it is difficult to understand your problem based on just the text.
Peter
May 15 2020 06:26 AM
it's something like this, sheet1 store the data horizontally, and sheet2 display the data according to the job number selected in the drop-down menu.
sheet3 is the example using jn1 as the selected one
many thx
May 16 2020 05:21 AM
Hi Tony,
I have come up with a possible solution using the OFFSET command and a rather convoluted formula - but it seems to work.
I am not sure how you get your source data (is it manual?) but you might want to consider changing the format to make it easier to manipulate?
Anyway if you are happy with the solution please mark it as complete.
many thanks,
Peter
May 17 2020 10:46 AM
If you have 365, this is the way to go:
=SUBSTITUTE(INDEX(FILTER(Master!C:BJ,Master!B:B=List!$B$1),1,SEQUENCE(20,3,1,1)),0,"")