| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help IfBlank use data from next cell
I am using the data in one of my colums as the input for a subtitle on
another spreadsheet. So I have =IF(ISBLANK(qryGraphExportStep4!K2),qryGraphExport Step4!K3,qryGraphExportStep4!K2), which works fine unless K3 happens to contain no data. My problem is if K3 is blank I want it to move down to K4 and pull that data and if K4 is blank down to K5 and pull that data, etc until K12. Any assistance would be appreciated, I am not very experienced with excel |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help IfBlank use data from next cell
Array enter (enter using Ctrl-Shift-Enter)
=INDEX(qryGraphExportStep4!K1:K12,MIN(IF(qryGraphE xportStep4!K2:K12<"",ROW(qryGraphExportStep4!K2:K 12)))) HTH, Bernie MS Excel MVP "quzie" wrote in message ... I am using the data in one of my colums as the input for a subtitle on another spreadsheet. So I have =IF(ISBLANK(qryGraphExportStep4!K2),qryGraphExport Step4!K3,qryGraphExportStep4!K2), which works fine unless K3 happens to contain no data. My problem is if K3 is blank I want it to move down to K4 and pull that data and if K4 is blank down to K5 and pull that data, etc until K12. Any assistance would be appreciated, I am not very experienced with excel |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help IfBlank use data from next cell
Thanks for the quick reponse. Problem solved!
"Bernie Deitrick" wrote: Array enter (enter using Ctrl-Shift-Enter) =INDEX(qryGraphExportStep4!K1:K12,MIN(IF(qryGraphE xportStep4!K2:K12<"",ROW(qryGraphExportStep4!K2:K 12)))) HTH, Bernie MS Excel MVP "quzie" wrote in message ... I am using the data in one of my colums as the input for a subtitle on another spreadsheet. So I have =IF(ISBLANK(qryGraphExportStep4!K2),qryGraphExport Step4!K3,qryGraphExportStep4!K2), which works fine unless K3 happens to contain no data. My problem is if K3 is blank I want it to move down to K4 and pull that data and if K4 is blank down to K5 and pull that data, etc until K12. Any assistance would be appreciated, I am not very experienced with excel |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help IfBlank use data from next cell
=INDEX(K2:K12,MATCH(TRUE,INDEX(K2:K12<"",),))
"quzie" wrote: I am using the data in one of my colums as the input for a subtitle on another spreadsheet. So I have =IF(ISBLANK(qryGraphExportStep4!K2),qryGraphExport Step4!K3,qryGraphExportStep4!K2), which works fine unless K3 happens to contain no data. My problem is if K3 is blank I want it to move down to K4 and pull that data and if K4 is blank down to K5 and pull that data, etc until K12. Any assistance would be appreciated, I am not very experienced with excel |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help IfBlank use data from next cell
Thanks for the quick responsed. It worked perfectly. I just had to add in the
reference to the sheet. Thanks so much! =INDEX(qryGraphExportStep4!K2:qryGraphExportStep4! K12,MATCH(TRUE,INDEX(qryGraphExportStep4!K2:qryGra phExportStep4!K12<"",),)) "Teethless mama" wrote: =INDEX(K2:K12,MATCH(TRUE,INDEX(K2:K12<"",),)) "quzie" wrote: I am using the data in one of my colums as the input for a subtitle on another spreadsheet. So I have =IF(ISBLANK(qryGraphExportStep4!K2),qryGraphExport Step4!K3,qryGraphExportStep4!K2), which works fine unless K3 happens to contain no data. My problem is if K3 is blank I want it to move down to K4 and pull that data and if K4 is blank down to K5 and pull that data, etc until K12. Any assistance would be appreciated, I am not very experienced with excel |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help IfBlank use data from next cell
You're Welcome!
"quzie" wrote: Thanks for the quick responsed. It worked perfectly. I just had to add in the reference to the sheet. Thanks so much! =INDEX(qryGraphExportStep4!K2:qryGraphExportStep4! K12,MATCH(TRUE,INDEX(qryGraphExportStep4!K2:qryGra phExportStep4!K12<"",),)) "Teethless mama" wrote: =INDEX(K2:K12,MATCH(TRUE,INDEX(K2:K12<"",),)) "quzie" wrote: I am using the data in one of my colums as the input for a subtitle on another spreadsheet. So I have =IF(ISBLANK(qryGraphExportStep4!K2),qryGraphExport Step4!K3,qryGraphExportStep4!K2), which works fine unless K3 happens to contain no data. My problem is if K3 is blank I want it to move down to K4 and pull that data and if K4 is blank down to K5 and pull that data, etc until K12. Any assistance would be appreciated, I am not very experienced with excel |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help IfBlank use data from next cell
The below formula checks for any data in Graph export sheet and if all cells
in the range A2:A12 are blank returns a blank. If data is available will retrun the first value in the range. Please try and feedback. =IF(COUNT(qryGraphExportStep4!A2:A12)=0,"",INDEX(q ryGraphExportStep4!A2:A12,MATCH(FALSE,INDEX(ISBLAN K(qryGraphExportStep4!A2:A12),0,1),0))) If this post helps click Yes --------------- Jacob Skaria "quzie" wrote: I am using the data in one of my colums as the input for a subtitle on another spreadsheet. So I have =IF(ISBLANK(qryGraphExportStep4!K2),qryGraphExport Step4!K3,qryGraphExportStep4!K2), which works fine unless K3 happens to contain no data. My problem is if K3 is blank I want it to move down to K4 and pull that data and if K4 is blank down to K5 and pull that data, etc until K12. Any assistance would be appreciated, I am not very experienced with excel |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help IfBlank use data from next cell
Thanks for the quick response to all of you! I tried the below and it works
for Column a but my data is in Column K. I tried to modify the code but I must of done something wrong because it doesnt seem to work for column K. Can you please tell me what I am doing wrong. =IF(COUNT(qryGraphExportStep4!K2:K12)=0,"",INDEX(q ryGraphExportStep4!K2:K12,MATCH(FALSE,INDEX(ISBLAN K(qryGraphExportStep4!K2:K12),0,11),0))) "Jacob Skaria" wrote: The below formula checks for any data in Graph export sheet and if all cells in the range A2:A12 are blank returns a blank. If data is available will retrun the first value in the range. Please try and feedback. =IF(COUNT(qryGraphExportStep4!A2:A12)=0,"",INDEX(q ryGraphExportStep4!A2:A12,MATCH(FALSE,INDEX(ISBLAN K(qryGraphExportStep4!A2:A12),0,1),0))) If this post helps click Yes --------------- Jacob Skaria "quzie" wrote: I am using the data in one of my colums as the input for a subtitle on another spreadsheet. So I have =IF(ISBLANK(qryGraphExportStep4!K2),qryGraphExport Step4!K3,qryGraphExportStep4!K2), which works fine unless K3 happens to contain no data. My problem is if K3 is blank I want it to move down to K4 and pull that data and if K4 is blank down to K5 and pull that data, etc until K12. Any assistance would be appreciated, I am not very experienced with excel |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help IfBlank use data from next cell
Within index you have put 11 instead of 1.. Try the below
=IF(COUNT(Sheet2!K2:K12)=0,"",INDEX(Sheet2!K2:K12, MATCH(FALSE,INDEX(ISBLANK(Sheet2!K2:K12),0,1),0))) If this post helps click Yes --------------- Jacob Skaria "quzie" wrote: Thanks for the quick response to all of you! I tried the below and it works for Column a but my data is in Column K. I tried to modify the code but I must of done something wrong because it doesnt seem to work for column K. Can you please tell me what I am doing wrong. =IF(COUNT(qryGraphExportStep4!K2:K12)=0,"",INDEX(q ryGraphExportStep4!K2:K12,MATCH(FALSE,INDEX(ISBLAN K(qryGraphExportStep4!K2:K12),0,11),0))) "Jacob Skaria" wrote: The below formula checks for any data in Graph export sheet and if all cells in the range A2:A12 are blank returns a blank. If data is available will retrun the first value in the range. Please try and feedback. =IF(COUNT(qryGraphExportStep4!A2:A12)=0,"",INDEX(q ryGraphExportStep4!A2:A12,MATCH(FALSE,INDEX(ISBLAN K(qryGraphExportStep4!A2:A12),0,1),0))) If this post helps click Yes --------------- Jacob Skaria "quzie" wrote: I am using the data in one of my colums as the input for a subtitle on another spreadsheet. So I have =IF(ISBLANK(qryGraphExportStep4!K2),qryGraphExport Step4!K3,qryGraphExportStep4!K2), which works fine unless K3 happens to contain no data. My problem is if K3 is blank I want it to move down to K4 and pull that data and if K4 is blank down to K5 and pull that data, etc until K12. Any assistance would be appreciated, I am not very experienced with excel |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help IfBlank use data from next cell
I tried but that still didnt seem to work (I modified the sheet name from
Sheet2 to the qryGraphExportStep4,) still returned blank cell. I was able to take the below code from teethless mama and it seems to be working. Thanks again for your help. =INDEX(qryGraphExportStep4!K2:qryGraphExportStep4! K12,MATCH(TRUE,INDEX(qryGraphExportStep4!K2:qryGra phExportStep4!K12<"",),)) "Jacob Skaria" wrote: Within index you have put 11 instead of 1.. Try the below =IF(COUNT(Sheet2!K2:K12)=0,"",INDEX(Sheet2!K2:K12, MATCH(FALSE,INDEX(ISBLANK(Sheet2!K2:K12),0,1),0))) If this post helps click Yes --------------- Jacob Skaria "quzie" wrote: Thanks for the quick response to all of you! I tried the below and it works for Column a but my data is in Column K. I tried to modify the code but I must of done something wrong because it doesnt seem to work for column K. Can you please tell me what I am doing wrong. =IF(COUNT(qryGraphExportStep4!K2:K12)=0,"",INDEX(q ryGraphExportStep4!K2:K12,MATCH(FALSE,INDEX(ISBLAN K(qryGraphExportStep4!K2:K12),0,11),0))) "Jacob Skaria" wrote: The below formula checks for any data in Graph export sheet and if all cells in the range A2:A12 are blank returns a blank. If data is available will retrun the first value in the range. Please try and feedback. =IF(COUNT(qryGraphExportStep4!A2:A12)=0,"",INDEX(q ryGraphExportStep4!A2:A12,MATCH(FALSE,INDEX(ISBLAN K(qryGraphExportStep4!A2:A12),0,1),0))) If this post helps click Yes --------------- Jacob Skaria "quzie" wrote: I am using the data in one of my colums as the input for a subtitle on another spreadsheet. So I have =IF(ISBLANK(qryGraphExportStep4!K2),qryGraphExport Step4!K3,qryGraphExportStep4!K2), which works fine unless K3 happens to contain no data. My problem is if K3 is blank I want it to move down to K4 and pull that data and if K4 is blank down to K5 and pull that data, etc until K12. Any assistance would be appreciated, I am not very experienced with excel |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i find specific data in a cell and its colocated cell data | Excel Worksheet Functions | |||
| converting numerical data in one cell to word data in another cell | Excel Worksheet Functions | |||
| how to find number in a cell , a cell contains character data ornumeric data | Excel Worksheet Functions | |||
| if function and ifblank() function | Excel Worksheet Functions | |||
| Find MAX data in sheet (Cell) - Any cell that exceeds X amount of data | Excel Discussion (Misc queries) | |||
Hybrid Mode
