absolute reference... i think.

[szpon90]

ok, excuse me if my technical language isn't too good, but i'm only a student. I have a spreadsheet with 3 worksheets. First is a questionnaire, users are filling it in. Second one is a results worksheet, where all data from questionnaires is stored. Data is being transferred there after user is finished filling in questionnaire and he/she presses the button that has recorded macro assigned to it. The macro takes whatever is in specific cells in worksheet 1, copy them, move to worksheet 2, move all records by a one row down, and with free space made by that action, it inputs the data from worksheet 1 into specific columns. all work so far. on worksheet 3 however i have few tables that analyses this data. i use SUM functions with multiple criteria. it all worked at the first time as i had my data already in place when i was working on worsheet 3. but when i insert new data all of my functions' range changes and it doesnt work anymore.

examplar sum function:
=SUM((Results!$C$9:$C$200="Year 8")*(Results!$D$9:$D$200="Male"))
it changed to that when i insert new data:
=SUM((Results!$C$11:$C$201="Year 8")*(Results!$D$11:$D$201="Male"))

please request code for macro as it's too long to put in this post if it's not gonna be useful.
thanks for your help. kk

[Paul]

Hi Szpon, welcome to the forum.

Instead of inserting rows of data at the top of the sheet, is it possible to just add new data to the bottom of your data, starting in the next row? It's easy enough via code to do this, something along the lines of:

Please Login or Register  to view this content.

[szpon90]

that's the thing i'm not too good with a code... :S sorry. i had created the macro by recording it within the excel and i was looking for answer to mention something in lines of absolute referncing so my functions doesnt change. though i appreciate ur answer paul as i see how it would work, i just dunno how to use it. im sure someone with similar problem that knows how to work with code and macros will find ur answer helpful.