Question about Excel...?

Question

I have multiple data sheets that are formatted the same. Each one corresponds to a month of data.

I need to take this data and, on a separate sheet, do a comparison of one field across time.

For example, cell B4 on each data sheet contains "Price of Widgets". Each data sheet covers a single month so I have one for January, one for February, etc.

I need to create a separate worksheet that pulls the data from each B4 cell from each sheet and displays all "Price of Widgets" from January onward. Is there an easy way to do this?

Answer 1

Two ways to do this:

1 - If you only want to report on widgets then you will need 12 formulas in the worksheet. (I am assuming you named the sheets Jan, Feb, Mar)
=Jan!B4
=Feb!B4
=Mar!B4
etc...


The problem is that if you need to report on data in B5, you need to change all the 12 formulas. Here's how to make the change very, very simple.

2 -
Choose a cell in the sheet that will store the cell to retrieve. I am using the cell A1 to store the text B4 (i.e. type in B4 in cell A1)
Now the formula used to get the values from your monthly sheets will be:
=INDIRECT("Jan!"&$A$1)
=INDIRECT("Feb!"&$A$1)
=INDIRECT("Mar!"&$A$1)

When you want to change & get values from cell B10 in your monthly worksheets, type in B10 in cell A1. The formulas will switch and get the correct value.

Hope this helps.

Answer 2

Yes. It's been a little while since I did anything with Excel, so I won't be able to give you any of the syntax. But it's actualy pretty easy. You just need to search on the help for the function syntax, or mess around with it some.

Oh, you can try copying a macro as well.

LOL! Wait just a second and I'll get it to you. I just remembered I had the workbook right here!

Ok, got it, but I can't send it to you like this. So, go to http://www.vawg.cap.gov then on the TOP LEFT HAND SIDE click on Cadet Programs. Scroll down until you get to the table containing "Physical Fitness Challenge". Go there and download the Spreadsheet. (I created that back in December.) Check out the functions for the cells on the last Summary sheet.

Answer 3

i am not so sure if that can be done.

did you try this site www.excelgoodies.com , may be they can help you on it. there are consultants online who provide excel support. if they dont give u a solution for it, i fear it would be impossible.