PDA

View Full Version : copy Past with matching ceriteria in other sheet



nhkhurum
2012-06-14, 07:17 AM
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

Ken Puls
2012-11-13, 07:00 AM
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?

nhkhurum
2012-11-13, 07:19 AM
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?


if no value found then cell remain blank.

Ken Puls
2012-11-13, 07:58 AM
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.

nhkhurum
2012-11-13, 08:08 AM
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.

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)

Ken Puls
2012-11-13, 08:28 AM
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.

nhkhurum
2012-11-17, 06:53 AM
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.



if we want to extract data of June 02 on report sheet. You can see on report on issuance table it shows only one record {73523-buss} Actually there is two record in red table (Issuance)

kindly see and advise

Ken Puls
2012-11-24, 04:42 AM
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.