Microsoft Excel 2007 home and student

I am using two worksheets: Sheet1 is for data, Sheet2 is a daily sales summary report. The daily sales summary includes number of items sold at each specific location, average price received at each location, and total sales revenue by location. I am trying to create a daily summary report that allows me to pull information from the data sheet based on the date I enter. For the date, I am simply using a number (example: "5" for September 5).

I'd like to pull data for September 5 at Store A. Is there a formula that when I enter the number "5" on my summary sheet, the cell reference will update automatically? A formula like: =A2+"5" (where +"5" moves the cell reference 5 columns to the right (or 6/7/8/etc columns depending on number I enter).

Example of data sheet:

A B C D E F

1 9/1 9/2 9/3 9/4 9/5

2 Store A 5 2 24 10 5

3 Store B 2 3 11 5 7

4 Store C 1 7 29 3 4Thanks for the help!

Put this formula in Sheet2!B1: =OFFSET(SHEET1!A1, 0, A1)

If you enter 5 in Sheet2!A1, B1 will display the value from Sheet1!F1 which is 5 columns

offsetfrom Sheet1!A1.Review the Help files for OFFSET for more options.

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History