Which formula would work?

[AV1D]

Hi All,

Im currently working on a project in which has a spreadsheet with 14 columns with various rows of data (The spreadsheet will either increase or decrease depending on how much data has been logged by our weather station).

What im trying to do is have excel calculate the data that we need in a summary. So the user copy and pastes the RAW data created by the weather station logger and puts into a sheet to which another sheet extracts the information that we actually need, and does any changes that we have configured it to do.

On the top of the sheet of where excel grabs the data that we need theres a box that asks for a start time and end time for the relivant run that our site team have done.

So for example:

Site team setup up data logger at 09:00, and proceed to location setup (Sensative,Upwind,Downwind Receptors) so Upwind start time 09:30 - End Time 10:15. Is there away to get excel to lookup start and end time on the main sheet, and then do any calculations we want from the start time to the end time??

So we have our start time of 09:30 and end time of 10:15, and in column E is N Component in which i need to calculate E starting from 09:30 to 10:15.

With different teams doing it in different ways between (Sensative,Upwind,Downwind) so having excel grab the data starting from the start time and ending on the end time.

Can this be done?

I Hope i havent confused anyone, and that everyone can understand in what i am trying to achive.

I have attached a copy of the spreadsheet that i am currently working on. If you look in the automated tab and go to colomn O and scroll down slightly this is what im trying to achive on sheet2 but from looking at the data from start time to end time.

Thanks

AV1D

Weather Data Calculation1 (DO NOT ALTER).xls

[ChemistB]

I used INDEX and MATCH to create a defined name that represents your selected data. Then use this newly defined range with INDEX and MATCH (and ISERROR) to pull your data over to the new sheet.

The defined name is
=INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$K$2,'RAW DATA'!$B$3:$B$107,0),1):INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$L$2,'RAW DATA'!$B$3:$B$107,1)-1,35)

Then the formula in 06.03.12 Automated A6 is
=IF(ISERROR(INDEX(SelectedData,ROW(A1),1)),"",INDEX(SelectedData,ROW(A1),1)) dragged down.
You'll need to modify the 1's to appropriate column numbers. For example in E6, it would be
=IF(ISERROR(INDEX(SelectedData,ROW(A1),6)),"",INDEX(SelectedData,ROW(A1),6))
Any questions?

[AV1D]

I used INDEX and MATCH to create a defined name that represents your selected data. Then use this newly defined range with INDEX and MATCH (and ISERROR) to pull your data over to the new sheet.

The defined name is
=INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$K$2,'RAW DATA'!$B$3:$B$107,0),1):INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$L$2,'RAW DATA'!$B$3:$B$107,1)-1,35)

Then the formula in 06.03.12 Automated A6 is
=IF(ISERROR(INDEX(SelectedData,ROW(A1),1)),"",INDEX(SelectedData,ROW(A1),1)) dragged down.
You'll need to modify the 1's to appropriate column numbers. For example in E6, it would be
=IF(ISERROR(INDEX(SelectedData,ROW(A1),6)),"",INDEX(SelectedData,ROW(A1),6))
Any questions?

Hi, firstly thank you for your reply.

Im just currently trying to get my head around this. Would i be right in saying that when the user puts in the start time and end time would the spreadsheet populate the following cells? or would this be wrong?

Thanks

AV1D

[ChemistB]

That is correct. Is that what you are looking for?

[AV1D]

That is correct. Is that what you are looking for?

yep, however when i put say 09:00 as the end time it imputs the minute before hand 08:59 is there anyway to have it so it shows the end time rather than a minute beforehand?

[ChemistB]

Sorry, with the defined name, remove the "-1"
So
=INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$K$2,'RAW DATA'!$B$3:$B$107,0),1):INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$L$2,'RAW DATA'!$B$3:$B$107,1)-1,35)

becomes
=INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$K$2,'RAW DATA'!$B$3:$B$107,0),1):INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$L$2,'RAW DATA'!$B$3:$B$107,1),35)

Sorry for the delay.

[AV1D]

Sorry, with the defined name, remove the "-1"
So
=INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$K$2,'RAW DATA'!$B$3:$B$107,0),1):INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$L$2,'RAW DATA'!$B$3:$B$107,1)-1,35)

becomes
=INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$K$2,'RAW DATA'!$B$3:$B$107,0),1):INDEX('RAW DATA'!$A$3:$AI$107,MATCH('06.03.12 Automated'!$L$2,'RAW DATA'!$B$3:$B$107,1),35)

Sorry for the delay.

Ive deleted the -1 from the code and nothing seems to be changing it is still stuck at grabbing the data 1 min before the actual end time. Any thoughts pal??

Thanks,

AV1D

[ChemistB]

See attached. I have 9:43 to 10:16 and it seems to be working fine.

[AV1D]

Hmm, bit of a mystery because i was going everything you said but nothing was happening.

However i noticed that you didnt delete the -1 from the function yet it still works correctly.

However i am currently using the new one that you have just said, to kill any more issues

And would like to thank you for you brilliant help :D

Thanks

AV1D

[AV1D]

Hi ChemistB

Sorry for the late reply but i have been busy in work and have been out of the office.

Regarding our PM Conversation, Here is the sheet that i changed to search more rows of information to find what it is looking for.

I customised it a bit, to where the sheet now has 3 extra sheets. One sheet where you enter the start and end times and it will calculate the avrage i think of what it relates to in the box. So when we create our reports, we could copy that data in sheet "Calculations" as well as copy the main data in the sheets name "Upwind" "Downwind" "Sensitive".

Even tho is workbook as been modified, excel hasnt found it a problem to show the results required in each of the sheets.

I'm just puzzled as to why excel wont change from searching upto row 107 to 500 so if there is at least 500 rows of data this wouldnt be a problem for excel to work.

#1 Weatherdata (Automated Sheet) Do Not Alter.xls

[ChemistB]

It looks like all your defined names are still set up to row 107. You also have some reference errors in there. The file you uploaded is password protected. Can you delete that one and upload one that does not have protection on any of the sheets?

[NBVC]

I guess rule # 1 is gone down the drain.....

[ChemistB]

Oops, didn't even notice that. As he started this thread in March, I don't believe he will be able to edit his title. Can a moderator change it to "Referencing dynamic ranges"?

[AV1D]

Hi ChemistB,

Sorry About that i thought i unprotected the sheets before i sent it. My Bad

NEW! Weatherdata (Automated Sheet) Do Not Alter.xls

[ChemistB]

Here's the corrected spreadsheet (I think). Your cell references within your named ranges were set at a maximum of 107. I changed them to 500 and your named range for SelectedData for spreadsheet "Sensitive" had a reference error. I fixed that.

[AV1D]

Hi Pal,

I will give that a whirl hopefully its fully solved.

Thank you once again mate you have been a big help :D

Thanks

AV1D