Hi all,
Not sure how to explain this, but by default if you want to copy a formula down a worksheet it moves one cell at a time. eg if you have in cell A1 the formula =SUM(B1:Z1), if you were to drag down to cell A2 you would get =SUM(B2:Z2) Is there any way I can drag the formula and it moves down a certain number of cells, so if A1 is =SUM(B1:Z1), when I drag it it changes in cell A2 to =SUM(B145:Z145)?
Hope this makes sense.
Thanks,
Luke
Last edited by lukela85; 02-02-2017 at 08:06 AM.
Try it like this:
=SUM(INDEX($A:$Z,(ROWS($1:1)-1)*144+1,0))
then copy down.
Hope this helps.
Pete
EDIT: Sorry, should be $B:$Z
Pete
Thanks Pete,
I was using a very simple example to try and explain what I meant.
This is my current formula in cell Y4:
=IF(X4<>"Error",INDEX(Data!$B147:$B290,MATCH(X4,Data!P147:P290,0)),"Error")
When I drag the formula down to cell Y5 what I'd like to see is this:
=IF(X5<>"Error",INDEX(Data!$B291:$B434,MATCH(X5,Data!P291:P434,0)),"Error")
So the cell references in cells B & P have effectively jumped 144 cells. Is it possible to do this?
Thanks,
Luke
It would help if you attached a sample Excel workbook, so that I can play about with different formulae.
To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
Note that the Paperclip icon does not work.
Pete
Thanks Pete.
You'll see that in each cell from B4 over to AK4 (minus a few blank cells) that each formula references the "Data" sheet. It's these cell references which I would like to "jump" 144 cells so I don't have to manually amend each cell (would be around 900!)
Thanks,
Luke
Okay, thanks for that Luke.
I'll be going out shortly, so I'll pick this up again when I get back (if no-one else has chipped in).
Pete
Thanks Pete.
Your file is very confusing (to an outsider). It would appear that in the data sheet your have a series of measurements taken each day at 10 minute intervals (which is where the 144 comes from, i.e. 6 readings/rows per hour * 24 hours), but you don't necessarily want to analyse them for a particular day as the entry in column A of the Detail sheet suggests - rather, your "day" may cover a period from 9:10am on one day to 9:00am on the next day. Consequently, your group of formulae are looking at different ranges, each spanning 144 rows. (That's a quick synopsis for my benefit).
I think it will be easier for you to adjust those ranges using the INDIRECT function, which allows you to "calculate" a range reference as a string of text and then get Excel to convert this into an appropriate reference that it understands. For example, you have this formula in cell E4:
=IF(AN4="Y",MIN(Data!E57:E200),"Error")
You would need to change this to (changes in red):
=IF(AN4="Y",MIN(INDIRECT("Data!E"&57+(ROWS($1:1)-1)*144&":E"&200+(ROWS($1:1)-1)*144)),"Error")
To understand what is happening, lets just focus in on the text that is formed within the INDIRECT function. The term ROWS($1:1) returns 1, and we subtract 1 from this giving zero, and this is multiplied by 144 and then added on to either 57 or 200. So the resulting text is:
"DATA!E" & 57 & ":E" & 200
which evaluates to what you have already. However, when the formula is copied down onto the next row the term controlling the row references becomes ROWS($1:2), which returns 2 and subtracting 1 from this will give 1 which is multiplied by 144. Thus, 144 is added on to 57 and 200. On the third row by the same logic, 288 will be added on to 57 and 200, and so on.
In F4 you have this formula:
=IF(E4<>"Error",INDEX(Data!$B57:$B200,MATCH(E4,Data!E57:E200,0)),"Error")
which is using the same row range (shown in red) in two different columns. For this formula, then, you will need two different INDIRECT functions along the lines just described, and similar for the formula in G4.
The formula in H4 uses a different range, i.e.:
=IF(AM4="Y",SUM(Data!Q201:Q344),"Error")
so this would have to be changed to:
=IF(AM4="Y",SUM(INDIRECT("Data!Q"&201+(ROWS($1:1)-1)*144&":Q"&344+(ROWS($1:1)-1)*144)),"Error")
In summary then, for each of the formulae in the cells up to AK4, you will need to amend the row references by adding on a term +(ROWS($1:1)-1)*144, and you will need to split the sheet name and column references out into fixed strings, and then wrap the whole string with INDIRECT( ... ). After you have made those changes, then the row of formulae can be copied down to give the effect of row references "jumping" by 144 on each new row.
Hope this helps.
Pete
P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.
Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).
Pete
Last edited by Pete_UK; 01-25-2017 at 09:20 PM.
Thanks for your help Pete.
Yes I can see why it would look confusing.
The purpose of the spreadsheet is to automatically list the extremes and times they occurred for each day of the month once I've downloaded the data from my weather station. For temperature and rainfall the recording period in the UK is from 0900 to 0900 each day so that's why the formulas are like that.
I've had a play with the formula in C4 and this is what I've got:
=IF(C4<>"Error",INDEX(INDIRECT("Data!B"&201+(ROWS($1:1)-1)*144&":B"&344+(ROWS($1:1)-1)*144)),MATCH(C4,(INDIRECT("Data!D"&201+(ROWS($1:1)-1)*144&":D"&344+(ROWS($1:1)-1)*144)),"Error")
However I'm getting the 'too few arguments' error. I can see I'm missing a closing bracket but not sure where to put it - not sure if this is the only issue!
Also, I have this formula in AM4:
=IF(AND(Data!$A201=$A4,Data!$B201=0.381944444444444,Data!$A344=$A5,Data!$B344=0.375),"Y","N")
This is to error check that the day starts at finishes at the expected time (0910 and 0900). Would you be able to show me where to put the INDIRECT functions with this formula?
Thanks,
Luke
why not use two criteria instead of one criteria. (i.e. "AO4" & "A4" which is date)
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
I think the long formula that you quote is in D4, not C4, and you currently have this:
=IF(C4<>"Error",INDEX(Data!$B201:$B344,MATCH(C4,Data!D201:D344,0)),"Error")
This should be changed to:
=IF(C4<>"Error",INDEX(INDIRECT("Data!B"&201+(ROWS($1:1)-1)*144&":B"&344+(ROWS($1:1)-1)*144),MATCH(C4,INDIRECT("Data!D"&201+(ROWS($1:1)-1)*144&":D"&344+(ROWS($1:1)-1)*144),0)),"Error")
You had an extra close bracket just before the MATCH in the middle, and then the end of the MATCH function had a few characters missing.
The formula in AM4 can be written like this:
=IF(AND(Data!$A201=$A4,Data!$B201=TIME(9,10,0),Data!$A344=$A5,Data!$B344=0.375),"Y","N")
which is a bit easier to understand, as it is not clear what the value 0.381944444444444 actually represents. This formula will get transformed into this:
=IF(AND(INDIRECT("Data!A"&201+(ROWS($1:1)-1)*144)=$A4,INDIRECT("Data!B"&201+(ROWS($1:1)-1)*144)=TIME(9,10,0),INDIRECT("Data!A"&344+(ROWS($1:1)-1)*144)=$A5,INDIRECT("Data!B"&344+(ROWS($1:1)-1)*144)=0.375),"Y","N")
Note that as the column references are part of a literal string they will not change if copied across, so the $ symbol in front of them is not strictly necessary, though it wouldn't matter if they were left in.
Hope this helps.
Pete
Thanks for all your help Pete, works perfectly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks