Formula help with cell references

[jlancton]

We have an Excel workbk tracking newspaper charges for our residents. Each monthly sheet has the following:

Col A has the date, beginning with the first Sunday of the month, and incrementing by one day per row.
Col B has the corresponding day of the week beginning with Sunday.
Col C has a dollar amount.

Then there's another sheet with just the Sundays for an entire period.

Col A has the date, beginning with the first Sunday of the period, and incrementing by seven days per row.
Col B has the corresponding day, which is always Sunday.
Col C has a dollar amount.

What we need is a way to make each Sunday amount in Col C on the monthly sheets equal to the appropriate Sunday amount from Col C on the Sunday sheet. In other words,

Sheet1:A2=Sheet2:A2
Sheet1:A9=Sheet2:A3
Sheet1:A16=Sheet2:A4

etc...

I've tried making a formula, but it only works right for the first Sunday of the month. When I copy the formula, and paste it to the next Sunday, the cell reference is wrong. Excel wants to make Sheet1:A9=Sheet2:A9, etc.

Is there a way to make a formula that will work the way we need it to when it's copied to each Sunday on Sheet1?

Thanks,

-Jeff

 

[SkipVought]

Hi,

Do you know that if you have entered actual dates, then they can be formatted almost any way you migh like using menu item Format/Cells/Number and selecting the desired date format.

The ddd format will display abreviated day of the week.
The dddd format will display the day of week.

You could even format the date like this...

dddd, mmmm d, yyyy

which would display like

Wednesday, April 9, 2003

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[GlennUK]

Try this in cell C2, which looks up the correct Sunday ...

=INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0))

and then copy it to the other Sundays.

Glenn.

 

[Zathras]

I would suggest the simplest way is to use VLOOKUP. Assuming the first date is in Sheet1 Cell A2 (Allowing row 1 for headers), you can set up Sheet2 this way:

A2: 1/5/03
B2: =A2
C2: =VLOOKUP(A2,Sheet1!$A$2:$C$367,3,0)
A3: =A2+7

Format Cell B2 with Custom Format

 dddd

( or

 ddd

) to see day of week.

Copy B2 and C2 to row 3 and then copy all three cells from row 3 down for the 52 weeks.

It would be possible to create a formula that jumps by 7 days as you suggest, but using VLOOKUP keeps everything in exact synch.

 

[jlancton]

Thanks for the reply, and yes, I understand how to format the date so that we would only need one column to display the date and day name. However, the issue is not with the dates per se. On sheet one, the dates increment one day per row, therefore Sundays are every seven rows. On sheet two, dates increment one week per row, therefore every row is a Sunday. Now, the next column on both sheets is the dollar amount spent on papers. So how do I get Excel to make sheet one's every seventh row's Col C equal to sheet two's every one row's Col C?, as in:

Sheet1:C2=Sheet2:C2
Sheet1:C9=Sheet2:C3
Sheet1:C16=Sheet2:C4

-Jeff

 

[GlennUK]

Look at my response.

Glenn.

 

[SkipVought]

jlancton,

What's the relationship of the data on the monthly sheet and the data on the sunday sheet?

If they are related, perhaps using Excel's Subtotal Wizard and/or the Pivot Table Wizard and/or Data Filters, could prove to be extremely helpful. It might just reduce the maintenance to a single sheet, believe it or not! All these techniques provide great power and flexibility for analyzing and displaying data results. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jlancton]

Thanks to all for the replies.

GlennUK: I put your code in, and it puts Sunday in the cell, and works when copied to another cell, which is getting closer. But what I need is the value in the next cell to the right of the Sunday on sheet two, which is the dollar amount.

-Jeff

 

[jlancton]

Skip: The relationship is that sheet one has the whole month, one day per row, with the dollar amount in the next column:

A2: 4/6/03
B2: Sunday
C2: Needs to equal Sheet2:C2

A3: 4/7/03
B3: Monday
C3:

...

A9: 4/13/03
B9: Sunday
C9: Needs to equal Sheet2:C3

Etc.

and sheet two has the same period, but one week per row, with the dollar amount in the next column:

A2: 4/6/03
B2: Sunday
C2: $2,650

A3: 4/13/03
B3: Sunday
C3: $2,540


Sorry to all if I'm not being that clear. I really appreciate the help.

-Jeff

 

[SkipVought]

Since not all of the lookup results will return a valid number, you might want to use this...

=IF(ISNA(VLOOKUP(A2,LookupRange,3,FALSE)),"",VLOOKUP(A2,LookupRange,3,FALSE))

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[GlennUK]

My formula gets values from column C of sheet2, so are you sure that you typed my formula in correctly?

Glenn.

 

[jlancton]

GlennUK: Yes, I had a typo. Thanks so much, that works perfectly! Thanks to all for your help.

-Jeff