So... you just want to look up the values from other tables where the date matches? We can do that with a VLOOKUP() funciton. What do you want to do if there are no matches with the date provided?
I have attached a work book containing 3 sheets
Reciept
Issuance
Report
Data is maintain on daily basis in Receipt/Issuance sheet and summary comes on report.
I want when i change date on report some information copy from Receipt and issuance and past on Report.
further more is it possible when I write any criteria at the end of row and row is locked for editing.
File is attached.
Kindly resolve this issue
Regards
NH
So... you just want to look up the values from other tables where the date matches? We can do that with a VLOOKUP() funciton. What do you want to do if there are no matches with the date provided?
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
I'm not seeing that...
With your initial table, I can see that the Item Code, Item Description and UOM are all in the Receipt table. I'm not sure where your SR#, Opening Balance or other fields pull from.
In the second table I can locate all information in the Receipt table.
In the third table though... you have this filled in using the records for June 1, where all other records are for June 10. Did you want the Issuance information table left blank then, or are there different rules to populate it?
PS: I've moved your thread to a more appropriate subforum as well.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Let me explain you.
First sheet is report sheet with daily receipt & issuance summery as well as detail as mentioned below 2 table like receipt record & issuance record.
We need data from Receipt sheet and issuance sheet.
When date change on report sheet reciept and issuance record changes as per posted in two sheet (Rec/Issuance)
I got that part, but you really didn't answer my questions.
Here, I have formatted both of your tables as Excel Tables, and plumbed in the VLOOKUP(MATCH()) combinations where I can.
The yellow cells do not have formulas, as I cannot figure out where those values come from.
The third table has formulas, but they are returning blank because there are no records for June 10 in the tlbIssuance (red) table.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Alright, so will there only ever be a maximum of 2 records to be pulled back, or could there be more?
If only 2, we can do this with a formula. If the number of records will vary, I'd recommend that you go with a couple of PivotTables, and tie them together using a macro. The second is a lot more work than the first.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Bookmarks