michaeltsmith93
Board Regular
- Joined
- Sep 29, 2016
- Messages
- 83
Hi all,
I am trying to combine sets of rows from two sheets. Here's a brief illustration of the desired product:
Sheet 3
Rows 1-5 of Sheet 1
Rows 1-5 of Sheet 2
Rows 6-10 of Sheet 1
Rows 6-10 of Sheet 2
...continuing in sets of five
I'm currently using the following formula:
=IF(INDIRECT(ADDRESS(1,COLUMN(A:A),,,$B$1))="","",INDIRECT(ADDRESS(1,COLUMN(A:A),,,$B$1))).
I've just put the sheet names in B1 and B2 so that I can use this sheet as a template.
I recognize that I could use EXACT, but I'm still toying with the idea of eliminating the cells that contain 0 (which has a meaning in my data set), and by setting the logic equal to 0, I can accomplish that quite simply. With that in mind though, my first question is this: is there a way that I could edit all of the cells in the sheet with this formula syntax and change the "" to 0 in one fell swoop?
Second question... this formula works great in terms of the COLUMN(A:A) function. However, if I use ROW(1:1), I obviously end up losing every other set of 5 rows from each sheet. I'd like to avoid having to manually input the row numbers for every row since there'll end up being +440, and I can't imagine that there isn't a way to do this. I'm a novice with Excel, so the Forum Searching Tips didn't help me too much--I'm sorry if this is a repeated topic! Thanks so much in advance!
Michael
I am trying to combine sets of rows from two sheets. Here's a brief illustration of the desired product:
Sheet 3
Rows 1-5 of Sheet 1
Rows 1-5 of Sheet 2
Rows 6-10 of Sheet 1
Rows 6-10 of Sheet 2
...continuing in sets of five
I'm currently using the following formula:
=IF(INDIRECT(ADDRESS(1,COLUMN(A:A),,,$B$1))="","",INDIRECT(ADDRESS(1,COLUMN(A:A),,,$B$1))).
I've just put the sheet names in B1 and B2 so that I can use this sheet as a template.
I recognize that I could use EXACT, but I'm still toying with the idea of eliminating the cells that contain 0 (which has a meaning in my data set), and by setting the logic equal to 0, I can accomplish that quite simply. With that in mind though, my first question is this: is there a way that I could edit all of the cells in the sheet with this formula syntax and change the "" to 0 in one fell swoop?
Second question... this formula works great in terms of the COLUMN(A:A) function. However, if I use ROW(1:1), I obviously end up losing every other set of 5 rows from each sheet. I'd like to avoid having to manually input the row numbers for every row since there'll end up being +440, and I can't imagine that there isn't a way to do this. I'm a novice with Excel, so the Forum Searching Tips didn't help me too much--I'm sorry if this is a repeated topic! Thanks so much in advance!
Michael