Excel 2007 : Date comparison and data transfer

**amandeepsharma89** · 06-27-2012, 01:48 PM

Hello Seniors,
I am in a novice in excel and have a problem in hand and I am in a real fix. I have 3 sheets in one workbook.
In sheet "summary" I will enter the date manually everyday under the Actual column e.g. 05/24/2012. Now, I want to copy data from "Actual" sheet under the same date 05/24/2012 that I have entered under the "Actual" column in "Summary" sheet. So according to my excel sheet value of Org 1,5,7,8 under date 5/24/2012 should be copied from Actual to Summary.

Similarly, I have to copy the data of the same Org 1,5,7,8 from the forecast worksheet for the next 7 days. I will again enter these 7 dates manually. So, whatever date is there under the 7 forecast column, I need to copy the corresponding values from "Forecast" sheet, Similar flow to the above "Actual to Summary".

So I again have compare each of 7 dates under forecast and copy corresponding data.

I need urgent help, please help me it will be really helpful and will save a lot of time.

Amandeep

**Banuchander** · 06-27-2012, 02:19 PM

Here is the attached for your purpose

**amandeepsharma89** · 06-27-2012, 02:27 PM

Hey thank you for the reply, if I change the date in the Summary worksheet the data below it is not changing. I will manually change the dates everyday. All other values should be filled on its own. And this will be a year long process. Also, can you please explain me the formula

Amandeep

**mshale** · 06-27-2012, 03:05 PM

eh, I think it might be better with an hlookup anyway:

Put this formula in the summary tab in cell E5, then you can fill it down:
=HLOOKUP(E$4,Actual!$B:$XFD,RIGHT($D5,1)+1,FALSE)

And then put the following in cell F5:
=HLOOKUP(F$4,Forecast!$B:$XFD,RIGHT($D5,1)+1,FALSE)
Then fill that down and over.

It won't let me upload the file for some reason... Gives me Error 2038

**Banuchander** · 06-27-2012, 03:18 PM

Hi Aman, Actually we need to matching by asssigning numbers to each date and after that made vlookup. The thing is we need to take each org and date in our vlookup formula, so that if you change date the data also automatically changing.

Formula is==IF(ISNA(VLOOKUP($D5,Actual!$A:$N,VLOOKUP($E$4,$N$2:$O$19,2,FALSE), FALSE))=TRUE,0,VLOOKUP($D5,Actual!$A:$N,VLOOKUP($E$4,$N$2:$O$19,2,FALSE), FALSE))

I attached excel with vlookup formula for better understanding.

**amandeepsharma89** · 06-27-2012, 03:37 PM

Hi mshale,
It worked perfectly. However, I will have max 120 days (for each quarter) in actual and forecast worksheet where dates start from B1 and the Org name starts from A5 to A150. What will be the code for it.
I may have few more questions.
Waiting for your reply

Amandeep

**amandeepsharma89** · 06-27-2012, 03:39 PM

Hi Banuchander,
I will try that code. But don't you think I will have to hardcode it every time I change the date?

**amandeepsharma89** · 06-27-2012, 03:43 PM

mshale,
Can you also explain me the what your formula is doing so that I can modify if need arises.
Thanks for the awesome work

Amandeep

**Banuchander** · 06-27-2012, 03:48 PM

yes you are right. I was trying to help you. But mshale done excellent work

**amandeepsharma89** · 06-27-2012, 03:50 PM

Banuchander I really appreciate your help, I was not even able to do that.

**amandeepsharma89** · 06-27-2012, 04:11 PM

What do
Actual!$B:$XFD,RIGHT($D5,1)+1 parameters mean?
In =HLOOKUP(E$4,Actual!$B:$XFD,RIGHT($D5,1)+1,FALSE)
Can someone explain it!

Amandeep

**amandeepsharma89** · 06-27-2012, 04:27 PM

When I tried it in my original sheet it is giving me #VALUE! error. Can anyone please let me know the solution?

Amandeep

**mshale** · 06-27-2012, 04:46 PM

Originally Posted by amandeepsharma89

Hi mshale,
It worked perfectly. However, I will have max 120 days (for each quarter) in actual and forecast worksheet where dates start from B1 and the Org name starts from A5 to A150. What will be the code for it.
I may have few more questions.
Waiting for your reply

Amandeep

I made the formulas so that all you would have to do is fill them down and over for however many orgs and days you have. There should be no change to the formula.

Originally Posted by amandeepsharma89

mshale,
Can you also explain me the what your formula is doing so that I can modify if need arises.
Thanks for the awesome work

Amandeep

All it is doing is looking up the date you type in, in your actual/forcast sheets, and reporting a value based on the org number. Very similar to an index formula which is what banuchander was going for.

Originally Posted by amandeepsharma89

When I tried it in my original sheet it is giving me #VALUE! error. Can anyone please let me know the solution?

Amandeep

I'm not sure what your original sheet contains. If it is different from the one you posted, you will need to change the refernces.

**mshale** · 06-27-2012, 04:49 PM

Originally Posted by amandeepsharma89

What do
Actual!$B:$XFD,RIGHT($D5,1)+1 parameters mean?
In =HLOOKUP(E$4,Actual!$B:$XFD,RIGHT($D5,1)+1,FALSE)
Can someone explain it!

Amandeep

This is taking the number of the org (right($D5,1)) and adding one to it to create a row number to look up in the entire sheet Actual (Actual!$B:$XFD), just in case you wanted to keep track of this over years to come

**amandeepsharma89** · 06-27-2012, 04:51 PM

Hi mshale,
My original sheet have a different name (org) like Lending, Balance, Deposits etc and they are arranged in alternate rows. How should I solve it? Please help me out really close to solve it.

And my actual org are in column A and are spread across different row number

Amandeep

**amandeepsharma89** · 06-27-2012, 05:15 PM

I figured out the solution and solved it. Now I have one query. I am using the formula

=HLOOKUP(E$4,Actual!$B:$XFD,6,FALSE)----this works fine

For one row I need the following hlookup

=HLOOKUP(E$4-1,Actual!$B:$XFD,6,FALSE)--where I need the previous date.

However if it is monday it take the previous day value, therefore it gives me an error as there is no Sat and Sun date and date in my actual and forecast worksheet. So, if it is monday I need fridays data. How to solve this. This is the only problem that I have right now, please let me know how to solve it.

Amandeep

**mshale** · 06-27-2012, 05:58 PM

Well, If you insert a column before the "actual" clolumn in the Summary tab and type the date on the same row as everything else, then you can take the "$" in front of the "E" out of the formula you mentioned, and fill it over, and then down to get your orgs.

I'm leaving for the day, so I hope that was enough info to help you out!

**djmatok** · 06-27-2012, 06:02 PM

you can use this formula = IF(WEEKDAY(A1)=6,A1+3,A1+1) I believe it will exclude weekends. However you must manually type in the first date on cell A1 = 5/24/12
and then on cell A2 use the formula above. I hope this helps!

**amandeepsharma89** · 06-27-2012, 06:03 PM

Hey mshale,
Thanks a lot for your help really appreciate it!! Perfect solution for the given problem. You rock! Have a nice day.

Amandeep

**mshale** · 06-29-2012, 10:47 AM

Originally Posted by amandeepsharma89

Hey mshale,
Thanks a lot for your help really appreciate it!! Perfect solution for the given problem. You rock! Have a nice day.

Amandeep

Glad I could help! Don't forget to mark this thread as solved

Excel 2007 : Date comparison and data transfer

LinkBack

Thread Tools

Rate This Thread

Display

Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Re: Date comparison and data transfer

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions